none
How to alter parameters to use in multiple datasets RRS feed

  • Question

  • Trying to figure out flow of Report Builder 3.0 report and how to execute properly and efficiently. 

    I have @Event parameter, which is text base and can allow blank value
    I have @SupplyID, which is text base and can allow blank value

    Based on the two conditions of if @Event is empty or has value, I need to create a NEW VALUE that I'll then use in multiple datasets with Select queries

    So, here are the scenarios.

    If len(@Event) < 1 then I want to use @SupplyID to create and call a new value and call it @ConvertedValueOfTwoParameters to call in all datasets.  BTW, I say @ConvertedValueOfTwoParameters, but this is not a parameter and I just don't know how to create and call this new proposed field.

    If len(@Event) > 0 then I want to create a Select Statement that will produce a number that I'll want to define as @ConvertedValueOfTwoParameters.  Then, the @ConvertedValueOfTwoParameters will be used to Select an ID field in multiple datasets.  BTW, if it helps, this new @ConvertedValueOfTwoParameters field in this 2nd scenario will be populated by this Select statement like:

    Select ID, Event
    From TableA
    Where Event = @Event

    From this Select statement, I want the ID field result to be the @ConvertedValueOfTwoParameters

    Any direction is appreciated.


    Saturday, September 21, 2019 6:33 PM

All replies

  • Sounds like this to me

    DECLARE @ConvertedValueOfTwoParameters int
    
    If Len(@Event) > 0
    
    Select @ConvertedValueOfTwoParameters = ID
    From TableA
    Where Event = @Event
    
    Else
    If Len(@Event) < 1
    
    Select @ConvertedValueOfTwoParameters = ID
    From TableB
    Where SupplyID = @SupplyID
    
    
    ... your other queries using the variable @ConvertedValueOfTwoParameters


    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

    Sunday, September 22, 2019 4:59 AM
  • Visakh16, Thanks for helping.  I've never used Declare before, so I just need a nudge on the rules using it.  I created a new dataset to test without conditions to see how Declare would work, but that didn't work.  To keep things simple in the new dataset, this does not error out.  I can show the ID value, but no @ConvertedParameters value to use.

    DECLARE @ConvertedParameters int

    Select ID
    From system.dbo.Response
    Where Master_Number = @Event

    I slightly changed it to what I thought you were suggesting, but maybe I didn't understand it.  The dataset errors out and won't load

    DECLARE @ConvertedParameters int

    Select @ConvertParamters = ID
    From system.dbo.Response
    Where Master_Number = @Event


    Sunday, September 22, 2019 6:07 AM
  • Hi

    Please go through the below blog demonstrates the similar kind of exercise. Please let me know how it goes

    https://www.mssqltips.com/sqlservertip/3453/sql-server-reporting-services-reports-with-optional-query-parameters/

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 6:23 AM
  • Visakh16, Thanks for helping.  I've never used Declare before, so I just need a nudge on the rules using it.  I created a new dataset to test without conditions to see how Declare would work, but that didn't work.  To keep things simple in the new dataset, this does not error out.  I can show the ID value, but no @ConvertedParameters value to use.

    DECLARE @ConvertedParameters int

    Select ID
    From system.dbo.Response
    Where Master_Number = @Event

    I slightly changed it to what I thought you were suggesting, but maybe I didn't understand it.  The dataset errors out and won't load

    DECLARE @ConvertedParameters int

    Select @ConvertParamters = ID
    From system.dbo.Response
    Where Master_Number = @Event


    Easiest way is to make it into a procedure and call it from SSRS dataset

    like

    CREATE PROC ProcName
    @Event varchar(100) = NULL,
    @Supply varchar(100) = NULL
    AS
    
    DECLARE @ConvertedParameters int
    
    IF LEN(@Event) > 0
    Select @ConvertParamters = ID
    From system.dbo.Response
    Where Master_Number = @Event ELSE IF LEN(@Event) < 1
    Select @ConvertParamters = ID
    From system.dbo.Response
    Where Master_Number = @Supply
    ...

    And call it from dataset like

    EXEC ProcName

    or just use option as SToredProcedure and select stored procedure name in the next selection box


    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


    Sunday, September 22, 2019 6:34 AM
  • I think I can manage that, but there may or may not be an issue.  The SSRS that I'm building still needs to have both parameters in place called @Event and @SupplyID.  When used as a stand-alone report, the user will input the @Event, which is how I coded report originally.  In addition, I have many other SSRS reports that field this one and supplies the @Event set of characters (text) about 95% time and I'm coding for the 5% that can't feed a @Event.  So, the other reports will supply the @SupplyID, which is an integer.  So, that's why I need the if then statement.  

    Now, will using @Event and @SupplyID in the procedure be an issue?

    Sunday, September 22, 2019 7:02 AM
  • Hi 

    From your post, I am not very clear about what exact your concern is, why would this be an issue? Could you elaborate a little for better understanding?


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 24, 2019 8:30 AM