October 23, 2008 at 8:24 am
Hello,
I have been tasked with developing a report where users need to chose which fields are included in the report from a parameter. There can be 2 fields, 10 fields, 20 fields, etc. Has anyone ever had the chance to do this?
Marty :w00t:
October 23, 2008 at 9:45 am
What is the reason for this requirement? It sounds to me like you really are producing multiple reports. Here are some questions that may help define the best way to accomplish this:
Are the fields static based on the parameter? For example, when parameter value 1 is chosen I always show the same 2 fields.
Are there any groups on the report?
What are the controls you are using on the report?
I really think you are defining N number of reports and should code that way. I'd probably go with individual reports and use a subreport control where I define the report that is tied to the subreport control based on the parameter.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 10:22 pm
Marty,
Yes, we have reports such as you're describing. Typically, these are a single table using a single dataset, and at run time, the user selects which fields from the dataset they wish to display.
One approach:
1) Define a single dataset with all possible data columns.
2) Lay all fields out in a single table. This method does not permit reordering the table columns, but a more sophisticated approach could accomplish this need.
3) Create a multi-select parameter listing all possible columns for the end-user to select. I just hardcode the possible values in the parameter itself, but you could also drive this via a table if appropriate.
4) In each table column. set the visibility based on whether or not the value is selected in the parameter. Typically, I do this with a custom IF statement that checks if the column "name" (whatever I used in the parameter) is contained in the array of values selected by the user. If it is, show the column. If not hide it.
Now there are some caveats to this method:
* The dataset returns all columns but you only show some of them. This can cause a performance degradation, and in fact, we had to follow the method suggested by Jack in the previous reply and build two datasets -- one with common columns and the other with the less used.
* In SSRS 2005, the page width is greater than the original table. If you had columns, the table shrinks in width, but the page doesn't. This leaves lots of white space to the right of the table, which can be a problem with printing or export to PDF. I understand that SSRS 2008 has resolved this, but haven't had a change to verify yet.
* As I said above, the column order is fixed.
Hope this helps.
Scott Thornburg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply