Forum Discussion

Spikenaylor's avatar
Spikenaylor
New Member
1 year ago
Solved

Report Builder filtering table based off another table parameters

Hi all

I am building my report and my have my parameters working on the main table i am using, report works fine.

My main table has two columns in that I am interested in. Prep_Month and Completion_Month

The report uses the a parameter 'MonthCompleted' which uses the Completion_Month

 

I have another table, called Delivery and this has a Delivery_Month which i need to filter or parameter based off the Main Table.

 

I would like the Delivery table to display only the records where the Delivery_Month matches the Prep_Month from the Main Table

 

I do have this working but for an example Completion_ Month of "March 2024", the Prep_Months will be "February 2024" and "March 2024"

 

but the report Delivery table only displays Delivery_Month" of "February 2024" looking like to me it is only picking up the 1st results from the Main Table.

 

My internal Parameter (Month_Prep) looks like this

 

General:

Nothing selected,

Internal set

 

Available Fields

DataSet: "Main Table"

Value Field: "Delivery_Month"

Label Field: "Delivery_Month"

 

Default Fields

DataSet: "Deilivery_Month"

Value Field: "Delivery_Month"

 

My dataset for the "Delivery Table"

uses filters

Prep_Month IN [@Month_Prep]

 

any ideas why I cannot get the Delivery table to display data fror February and March instead of Just March

 

Many thanks for looking

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,Spikenaylor . I am glad to help you.
    If you use the Filters function to filter the dataset, the system selects the parameter with a (0) suffix by default.


    If this is the case for you, you need to remove “(0)” and make the whole parameter a list of values, so that the corresponding data can be filtered properly (I think there is an error in your parameter configuration settings)


    this is my test data:

    If you want to implement a similar join query (or more complex query logic for datasets, I'd prefer that you write the query statement artificially)
    Using query statements + query parameters

    Tutorial: Add parameters to your report (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Learn

    Like this:
    Set up a separate parameter value dataset for the report parameters to get the corresponding parameter values.
     

    The dataset DataSetMainTablePar used by the parameter [MonthCompleted]

    SELECT
DISTINCT
MainTable.Completion_Month
FROM
  MainTable

     

    This is then used in the final dataset to set the correct query parameters

    I'm using a SQL Server datasource

    You need to ensure that:

    1. the parameters are set up with the correct type, whether they contain multiple values, (use “=” or “IN” in the query)
2. the parameter mapping is set correctly
3. Query parameters are used correctly in the query statement



    This is my query code:

    SELECT d.*
FROM DeliveryTable d
JOIN MainTable m ON d.Delivery_Month = m.Prep_Month
WHERE m.Completion_Month in ( @MonthCompleted );

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,Spikenaylor . I am glad to help you.
    If you use the Filters function to filter the dataset, the system selects the parameter with a (0) suffix by default.


    If this is the case for you, you need to remove “(0)” and make the whole parameter a list of values, so that the corresponding data can be filtered properly (I think there is an error in your parameter configuration settings)


    this is my test data:

    If you want to implement a similar join query (or more complex query logic for datasets, I'd prefer that you write the query statement artificially)
    Using query statements + query parameters

    Tutorial: Add parameters to your report (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Learn

    Like this:
    Set up a separate parameter value dataset for the report parameters to get the corresponding parameter values.
     

    The dataset DataSetMainTablePar used by the parameter [MonthCompleted]

    SELECT
DISTINCT
MainTable.Completion_Month
FROM
  MainTable

     

    This is then used in the final dataset to set the correct query parameters

    I'm using a SQL Server datasource

    You need to ensure that:

    1. the parameters are set up with the correct type, whether they contain multiple values, (use “=” or “IN” in the query)
2. the parameter mapping is set correctly
3. Query parameters are used correctly in the query statement



    This is my query code:

    SELECT d.*
FROM DeliveryTable d
JOIN MainTable m ON d.Delivery_Month = m.Prep_Month
WHERE m.Completion_Month in ( @MonthCompleted );

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.