13 thoughts on “Can I Add Controls to a Reporting Services Report?

  1. 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

    Like

    • 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.

      Like

  2. 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

    Like

  3. 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

    Like

  4. 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?

    Like

    • 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.

      Like

      • 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.

        Like

    • 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.

      Like

    • 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?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s