none
Parameter filtering across multiple joined datasets? RRS feed

  • Question

  • Greetings!

    I have 4 datasets in a report.  One of them (not the main dataset) has a field I need to set as a searchable parameter.  In this case, it's an account code.

    How can I set up my parameters to only return account codes that match what the user inputs?

    Example:

    Dataset1 has an Order Number and some general info (like fiscal year)

    Dataset2 has an Order Number and some dollar amounts

    Dataset3 has as Order Number and Org codes (what I need to filter on)

    Dataset4 has an Order Number and some information about the vendors we're paying (location, etc.)

    I've inner joined all of them on the Order Number. 

    When I try this:

    SELECT DISTINCT 
    purch_order_num
    , org_code as Org
    , object_code as Obj
    FROM dataset3 
    WHERE org_code = (@orgCode)

    ...this returns ALL of the same data I had before (no filtering), but now every single Org code value is blank.  If I enter 999 as the @orgCode, it returns all the values from all the tables -- except it deletes the columns with Org codes.

    What can I do to create a parameter that filters across all of the tables and only returns the lines with the correct Org codes.

    Thanks!




    Tuesday, July 16, 2019 7:13 PM

All replies

  • Hi Alexander,

    Your query look clear and correct. 

    What you mean, "inner join" these four datasets in SSRS? Maybe this causes the issue.

    Would you mind share some sample data for better understanding? Or screenshots?

    Regards,

    Lukas


    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.

    Wednesday, July 17, 2019 2:03 AM
  • Create a Dataset specifically for orgCode.  For example, you can name it DSOrgCode and have it be defined as follow:

    SELECT DISTINCT org_code AS orgCode

    FROM ....

    Then for your @orgCode parameter:

    1. Make sure you set the Available Values to "Get values from a query"

    2. The Dataset set to DSOrgCode

    3. The Value field and the Label field both set to orgCode

    This usually works better as users can select the available Org codes to filter by via a drop-down menu.

    Hopefully this makes sense and helps you!

    Wednesday, July 17, 2019 7:39 AM