I see this question on the forums all the time and thought it was worth a repost here. Just this week, at least two people on the MSDN forum for Reporting Services have asked how to add radio buttons or check boxes to a report.
Can this be done?
No, …I mean Yes, …well, sort of.
Many new report designers who have an application development background tend to treat Reporting Services as an app development tool and it’s not. Although you can embed code into a report, you cannot add controls in the same way that you would in a web form or windows form project. Report “controls” are known as “report items” and are generally for displaying data, not gathering input. The report parameters don’t give you explicit control over the way users are prompted for input values and typically display values in a drop-down list. If you really need to create a custom interface to prompt for parameter values, you can create a custom web form and then use the ReportViewer control or generate a URL string to launch the report, but this is an exception to the standard reporting experience. You can do a lot of creative things with SSRS but the design approach is much different that application development. For newcomers I recommend that you pick up a good book on Reporting Services and learn the how the product was designed to function, rather than spending a lot of extra effort customizing it.
When I’m asked if it’s possible to do something in Reporting Services, I try to frame the need I one of three categories:
- A product feature, something that SSRS was designed to do easily, using the standard design interface and properties.
- An advanced feature that the product does well but requires advanced knowledge, scripting or a little programming.
- A very creative extension of product features and custom programming, perhaps stretching the limits of the product design.
Here’s a way to add checkboxes to a report that can be used for interactive selection. The same technique could be used to add radio buttons. These techniques fit into category 3 in the above list.
Using checkboxes to select/deselect items & choose parameters
Any idea how to add a button to HTML ReportViewer?
Hi Paul, I am trying to create a master detail SSRS, when clicked on order id in orders table, I want to filter records in orderDetails table, but without refreshing the report, do you think it’s possible ? I think it might be if we can update report variable without refreshing page, thanks
There are two different ways that you can create a master/detail report that shows detailed/related records within in same report when the user selects a master record. First is to create a two level hierarchy in a table of other grouped data region, hide the details group and set the visibility to a toggle item. This approach has minimal interaction with the report server but all the detail data must be initially loaded. The second approach is to use a report action to pass a parameter to the same report, using a second filtered dataset to return the detail records. This requires the report to reload and re-render.
The only way to run a Reporting Services report without interacting with report server and “refreshing the page” is to generate an RDLC file and use the forms control to run the report in local model.
Completely agree with main statement above about SSRS. As a Senior SQL Server DBA I have used SSRS a lot for user interfaces and project pilots or proof of concepts, so the project starts and ends. I have been successfully focus on its concept. Love .Net though have no free time for anything else than SQL Server, of course I love SQL.
SSRS is:
_ SSRS was designed to do easily, using the standard design interface and properties.
_ A product with advanced features that does not require advanced knowledge of scripting/programming.
_ An amazing and very creative extension of product features and custom programming.
Regards
Noravia
hi Paul, I am not able to run the rdl’s as it is saying unknown error while opening with visual studio 2010
i can’t make the code to work, it always give me the error of conversion trying to convert varchar to numeric, i try using the convert(varchar function to convert my column from numeric to varchar but when i do that it doesn’t retrieve any records, only if i select just one value then it retrive one record with the checkbox checked
This is a good solution but if the report (list of products) extends over two pages and you select a product on page 2 then the report defaults back to page one.
Is there any way of maintaining the page that you are on when making a product selection?
There is not a way to drill-through to the report and go to a specific page and that’s where this technique has its limits… referring back to my point #3 above.
Thinking out loud… one way that this could be remedied would be to use a multicolumn matrix to list more than one column of checkbox images & items. Let’s say 20 rows deep and 3 columns wide. Now, how would be go about grouping the columns so that they would “wrap”? I can imagine that an expression of some kind based on some subset of the total rows and the rownumber() could be used to group the columns. Interesting challenge and perhaps a good topic for a later post.
I’ll offer a free book to the first person to post a solution to this puzzle.
I’ve now been able to produce a report that drills through and maintains the page or goes to a specific page.
I used an action that goes to a reportserver url using “&rc:Section=” to go to a page number. The parameters I pass are included in the url such as;
‘http://srs-01/ReportServer_SRS/Pages/ReportViewer.aspx?%2fReportFolder%2fReportt&rs:Command=Render&rc%3aFormat=HTML4.0&rc%3aDocMap=False&rc:Section= “Page Number Goes here”
I’m able to determine the page number by having my records ordered and then restricting the numbers of records per page. In this case 25.
So page Number is derived something like;
CAST((((ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY A.CurrentRisk desc, A.patient_id)-1)/25)+1) AS varchar(3))
This is then appended to the above report server url above.
All of this is done in stored procedure but does come with a limitation. As the url is directing to the report server and not the report manager, a user is able to alter the parameters passed in the url which could have obvious security implications!!!
Please, let me know if I’ve not been clear enough in my explanation.
Very nice, Kevin! Goes to show that with some persistence and creative, out-of-the-box thinking; almost anything is possible.
I believe I owe you a book. Please contact me with your mailing address and let me know if you’d like a copy of the 2012 Professional or the Recipes book.
http://sqlserverbiblog.wordpress.com/contact/
Hi Jonathan
Thanks for providing this resource. Would it be possible to add an input text box that updates a hidden parameter?
By default, adding a parameter to a report will prompt the user to enter a value in a textbox within the parameter toolbar. Keep in mind that Reporting Services is not an application development tool and you cannot add form controls like textboxes to the body of a report. For this, you should design an application interface, like a web form.
Hi
I read this post 2 times. It is very useful.
Pls try to keep posting.
Let me show other source that may be good for community.
Source: Warehouse kpi
Best regards
Jonathan
Hi Jonathan
Thanks for providing this resource. There appears to be some useful information on this blog about different types of KPIs and various line-of-business requirements.
There is no information on this site about the poster or the source of the information. Is it yours?