Report Builder filtering table based off another table parameters

[Spikenaylor]

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

[Anonymous · Accepted solution]

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) | Mi...

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.