locked
How to run the same SSRS report multiple times, each time with a different parameter value? RRS feed

  • Question

  • Hi,

    I have an SSRS report that produces a list of invoices for a single supplier (so supplier is a parameter specified at runtime). The report is based on a stored procedure in SQL. I would like to make things easier and instead of going through all suppliers one by one, I want to have a single action, which produces separate reports, per supplier, for all suppliers that have an outstanding invoice (so not all suppliers on the system as a whole but all suppliers that are within the result set of the stored procedure).

    I assume this would mean first executing the stored procedure to find all suppliers that have invoices at the time. And then running the report multiple times, once for each supplier, where the supplier value is used as the parameter every time. ideally this would open separate webpages with a single report per page. (I am creating a button in another software, which is simply launching iexplore.exe and passing a fixed SSRS report URL).

    What would be required? I have SQL 2012 and I am building this in Report Builder 3.0.

    Thank you!

    Dimitar

    P.S. What about still running ONE report, but every supplier starting on a new page? Is that more realistic? So supplier will not be a parameter any more, but just something that creates a new page on the report?



    • Edited by goldfrapp01 Thursday, March 5, 2015 4:24 PM new idea
    Thursday, March 5, 2015 4:13 PM

Answers

  • Hi Dimitar,

    Based on my understanding, you want to make each user run the same report, but each person can only see the personal data. In a word, you want to know how to filter the report data based on who is running the report, right?

    In your scenario, you can grant a particular domain user account as the db_datareader role in the reporting database. Then specify the credential which connecting to data source as this account. Besides, you should create a table to specify content that a user is allowed to access. And we include this table in the report query to limit the user to see the data which they are allowed to see. For more information, please refer to this article:Dynamically Control Data Filtering in SQL Server Reporting Services Reports.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Saturday, March 7, 2015 10:50 AM
  • What about still running ONE report, but every supplier starting on a new page? Is that more realistic? So supplier will not be a parameter any more, but just something that creates a new page on the report?

    Yes thats a much better option. Its very easy to generate such a report in SSRS. You dont need a parameter in that case but just needs to add Supplier as a grouping field in the tablix. Then add other columns and groups if required. Select option set page break after each instance of the group for first grouping (ie based on supplier field). Then report will render with one page per supplier. On exporting to excel also each supplier data goes to a different tab

    Here's a similar requirement I did for one of my projects

    http://visakhm.blogspot.in/2013/10/using-ssrs-to-export-sqlserver-data-to.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, March 7, 2015 12:35 PM

All replies

  • Hi Dimitar,

    Based on my understanding, you want to make each user run the same report, but each person can only see the personal data. In a word, you want to know how to filter the report data based on who is running the report, right?

    In your scenario, you can grant a particular domain user account as the db_datareader role in the reporting database. Then specify the credential which connecting to data source as this account. Besides, you should create a table to specify content that a user is allowed to access. And we include this table in the report query to limit the user to see the data which they are allowed to see. For more information, please refer to this article:Dynamically Control Data Filtering in SQL Server Reporting Services Reports.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Saturday, March 7, 2015 10:50 AM
  • What about still running ONE report, but every supplier starting on a new page? Is that more realistic? So supplier will not be a parameter any more, but just something that creates a new page on the report?

    Yes thats a much better option. Its very easy to generate such a report in SSRS. You dont need a parameter in that case but just needs to add Supplier as a grouping field in the tablix. Then add other columns and groups if required. Select option set page break after each instance of the group for first grouping (ie based on supplier field). Then report will render with one page per supplier. On exporting to excel also each supplier data goes to a different tab

    Here's a similar requirement I did for one of my projects

    http://visakhm.blogspot.in/2013/10/using-ssrs-to-export-sqlserver-data-to.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, March 7, 2015 12:35 PM