**SLSO is a self-hosted organization**
I am creating a custom report for my coworkers and I need the report to be hidden on screen, but data to be shown on export option. I know this is possible because my predecessor had this set up on some existing custom reports, however I am struggling to make it happen on my new custom report. We are using SSRS 2014. In the report that my predecessor created, he has the Tablix body visibility property set to hidden:
And in that report for some reason the body is pink, but no fill color is set. I wonder if that coloring indicates some setting somewhere that I cannot find:
For his report, when you run it in Tess, the body is hidden but the user can export the date to CSV:
When I mimic these setting my my new report, nothing exports except a single cell with symbols in it. So I tried unhiding the Tablix body in my new report and the data exported just fine. What am I missing here? Is there a setting in the report my predecessor create that I am unaware of and therefore missing my new report?
I checked all the column visibility settings and they all say "show":
Does anyone know how to make this work?
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
**SOLVED**
I figured it out almost immediately after asking for help. I hate when that happens.
What I did was select my Tablix:
Pressed F4 to get the properties pane on the right, then changed DataElementOutput from "Auto" to "Output" and Hidden from "False" to "True"
And now the data exports properly! I think the "DataElementOutput" setting was the key.
Ashley
So I learned today that this ^^ works when you want to hide the entire Tablix, but not when you just want to hide some columns on screen and still have them export.
Anyone know how to do that?
You have do do it on the column.
18 column result:
Same report 24 column
On the report:
the expression
I would also advise putting logic in the query. While SSRS can filter down rows, it is slow and you want to send as little data as possible to it. I usually pass detail level to the query and do something like the following because grouping the results:
I misread your initial issue, but the logic is basically the same as column hiding although I'm not certain why you would want to hide a report on screen and then have it available during export.
Also if you are using sub reports it has been my experience that most of them time they are not needed by making some changes to the source query. Out of the couple of hundred SSRS reports I've written there have only been about 5 instances where I needed to utilize a sub report.
Okay so this is helpful, for detail vs summary reports.
More Fields Available on Export
I cannot figure out how to hide SOME fields on screen, yet have them export to CSV. Every time I try the solution I found above, the whole tablix becomes hidden.
Can anyone help?
Yes, it is more work up front, but it saves having to create multiple detail variation reports of the same data.
Hi Ashley. Here you go let me know if this works for you but it should.
satishmsbiworld.blogspot.com/.../how-to-hide-column-in-export-data-from.html
You can use the method I referred to and export to Excel instead and the columns will remain hidden. Unfortunately it will include the page header and footer.
Here is an example of an attendance report that is Excel friendly:
By using the hide totals option is makes it Excel friendly:
Here is what it looks like exported to Excel:
Unfortunately there is no way to programmatically suppress the header and footer. Some people fake the header and footer by making them boxes in the body of the report and then hiding them as needed. The downside of doing that is built in fields like page number are not available.
If this way doesn't work for you have basically two options that I can think of right now:
Use custom code in the report to fire a stored procedure on the server that will rerun the same query and save the results to a text file on a shared drive when the user clicks on some reference in the report
Create a second report that is the raw data where u can pass the parameters to which is formatted specifically for export when the user clicks on some reference in the report
This expression worked:
=IIF(Globals!RenderFormat.Name = "EXCEL", False, True)
Thank you! I had been replacing the "EXCEL" with "CSV" and that doesn't work, although it is supposed to work.
I worked on a detail/summary report today! Granted it was an SSRS rebuild of an Infomaker report that my predecessor built, but I am getting a better understanding of it.
Thank you, Ronald!
Good deal, only way to learn is to do.
Nice option, never used that functionality, but good to keep in the toolbox.