none
Parameter driven query and SSRS Dataset fields

    Question

  • I have a Parameter in my SSRS Report, @PatientSelection. I use this Parameter within my SQL Stored Procedure to execute one version of the report and get specific columns, or another version of the report with different columns.

    --
    -- @PatientSelection is set to 1 if the Business Report Requestor chooses "New Patients"
    --
    IF @PatientSelection = 1...

    --
    -- @PatientSelection is set to 2 if the Business Report Requestor chooses "Established Patients"
    --
    IF @PatientSelection = 2

    When I do this, none of my fields are showing up within my SSRS Dataset.

    Am I fundamentally doing this the wrong way concerning SSRS and Report Creation???

    Any feedback is greatly appreciated. Thanks for your review and am hopeful for a response.

    PSULionRP


    Friday, March 28, 2014 7:24 PM

Answers

  • So are you telling that you want same report to work based on dynamic metadata? ie in one mode work on one set of columns and in another mode work on other set of columns? I dont think that would work as for dataset metadata has to be fixed otherwise it will complain on missing columns.

    What might be better would be to use two separate datasets with each calling procedure using a different parameter value and fixing metadata based on it, Then you can use separate containers within your report to show corresponding data. You can also add a hidden property expression to them to make them visible and show required data based on your passed report parameter conditionally.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 29, 2014 8:56 AM

All replies

  • You can add manually  the missing fields, drag and drop all needed fields to the tablix and see how was it  going. Doo you use one or two tables(based on the parameters) to display the report?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, March 29, 2014 7:19 AM
  • So are you telling that you want same report to work based on dynamic metadata? ie in one mode work on one set of columns and in another mode work on other set of columns? I dont think that would work as for dataset metadata has to be fixed otherwise it will complain on missing columns.

    What might be better would be to use two separate datasets with each calling procedure using a different parameter value and fixing metadata based on it, Then you can use separate containers within your report to show corresponding data. You can also add a hidden property expression to them to make them visible and show required data based on your passed report parameter conditionally.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 29, 2014 8:56 AM