Displaying 2 datsets on one page using Monthyear Parameter

[Karolina411]

Greetings! I have a Paginated Reports (I am using a tabular model from our analysis server) question as I have 2 datasets joined by a common Parameter which is the DateColumn MonthYear- I have these referenced inside both datasets like so:

Dataset1 will be filtered by both Physician Organization and Date (monthly reporting) and DataSet2 will only be filtered by yearMonth.

I created 2 new tables for both Parameters (YearMonth and PhysicianOrg).  Dataset2 only references the yearMonth parameter.  When I add a new table and reference dataset2 along with dataset1 in one table it does work.  I tested this expression from Dataset2: =IIF(IsNothing(Sum(Fields!ID_AllowedPMPMYTD_.Value)),0,Sum(Fields!ID_AllowedPMPMYTD_.Value))

I tried this expression inside the same table and referenced Dataset2 but that did not work.

=IIF(IsNothing(Sum(Fields!ID_AllowedPMPMYTD_.Value, "Dataset2")), 0, Sum(Fields!ID_AllowedPMPMYTD_.Value, "Dataset2"))

The question is how do I show both results from 2 datasets in one paginated report?  I just need one column with the same expression to filter differently :  The Rate Column Filters by both parameters and the HFPN column filters by yearmonth column.  Any suggestions?  I created dataset2 just for HPFPN overall which I am currently hard coding until I can work this relatively simple problem out.

[Anonymous]

Hi  @Karolina411 ,

You can try the following steps:

SSRS is case sensitive, so make sure you spell the dataset name correctly when referencing it in your expression.

If you are using the + operator to splice characters, you can try using & instead.

reporting services - SSRS Expression: The value expression for textbox has scope parameter that is i...

Here is the solution to a similar error, hope it helps:

Multiple DataSet error SSSRS - Microsoft Q&A

Error in SSRS "Aggregate expression without scope – SQLServerCentral Forums

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  @Karolina411 ,

In the table, you can try to use the lookup function to extract data from the second dataset based on the key fields in the first dataset. The syntax is shown below:

=Lookup(Fields!CommonField.Value, Fields!CommonField.Value, Fields!DesiredField.Value, "Dataset2")

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Karolina411]

Thank you. I tried that but get this error= The 'Value' expression for the text box 'Textbox10' has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.