topic 2 datasets in Paginated reports using parameters in Report Server

2 datasets in Paginated reports using parameters

Karolina411 — Fri, 19 Jul 2024 20:38:35 GMT

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 which is what I want as I wish to COMPARE the OVERALL score to the score which will be filtered by both YEARMONTH and PHYSICIANORG. When I add a new table and reference dataset2 along with dataset1 in one table it does work. I tested this expression from Dataset2:

=Lookup(Fields!YearMonth.Value, Fields!YearMonth.Value, Fields!ID_AllowedPMPMYTD_.Value, "DataSet2") (the numbers are not matching the excel formula which is =IFERROR(CUBEVALUE("Cube",GM,CubeMeasures!$D$25,CurrentMonth),0). (the current month is hardcoded and referenced

I tried this expression inside the same table and referenced Dataset2 but that did not work. I am trying to match an excel formula report which uses HFPN overall is just AllowedPMPMYTD =CUBEMEMBER("Cube","[Measures].[AllowedPMPMYTD]") (it filters by month using How is this matched in Report Builder??? Do I need to use a DAX variation of CURRENT MONTH?

I use the formula below sliced by yearmonth (SQL via Tabular Model) in Report Builder:

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.

EVALUATE SUMMARIZECOLUMNS('Providers'[PCPLocation], 'Providers'[PhysicianOrganization],'DateDim'[Year],'DateDim'[MonthNameAbbreviation],'DateDim'[YearMonth], FILTER(VALUES('Providers'[PhysicianOrganization]), 'Providers'[PhysicianOrganization] = @PhysicianOrganization),FILTER(VALUES('DateDim'[YearMonth]), ('DateDim'[YearMonth] =@YearMonth)), FILTER(VALUES('Patients'[Populations]), ('Patients'[Populations] = "GM")), "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "ERVisitsYTD", [ERVisitsYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD],"AntidepressantMedicationAcutePhaseNumerator",[AntidepressantMedicationAcutePhaseNumerator],"AntidepressantMedicationAcutePhaseDenominator",[AntidepressantMedicationAcutePhaseDenominator],"AntidepressantMedicationAcutePhaseRate",[AntidepressantMedicationAcutePhaseRate],"AnnualWellnessExamNumerator" ,[AnnualWellnessExamNumerator],"AnnualWellnessExamDenominator",[AnnualWellnessExamDenominator], "AnnualWellnessExamRate",[AnnualWellnessExamRate],"ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate],"ReadmitsYTD",[ReadmitsYTD],"ReadmitsYTDRate",[ReadmitsYTD%],"AdmitsYTD",[AdmitsYTD],"AllowedCost",[AllowedCost],"AllowedCostYTD",[AllowedCostYTD],"AllowedPMPMYTD",[AllowedPMPMYTD],"BreastCancerScreeningNumerator",[BreastCancerScreeningNumerator],"BreastCancerScreeningDenominator",[BreastCancerScreeningDenominator],"BreastCancerScreeningRate",[BreastCancerScreeningRate],"ColorectalCancerScreeningDenominator",[ColorectalCancerScreeningDenominator],"ColorectalCancerScreeningNumerator",[ColorectalCancerScreeningNumerator],"ColorectalCancerScreeningRate",[ColorectalCancerScreeningRate],"DAWNumerator",[DAWNumerator],"DAWDenominator",[DAWDenominator], "DAWFillRate",[DAWFillRate],"ERVisitsRate",[ERVisitsYTDMoreThan5Visits1000],"ERVisitsNumerator",[ERVisitsYTDMoreThan5Visits],"LowBackPainImaging18to64Numerator",[LowBackPainImaging18to64Numerator],"LowBackPainImaging18to64Denominator",[LowBackPainImaging18to64Denominator],"LowBackPainImaging18to64InverseRate",[LowBackPainImaging18to64InverseRate],"LowBackPainImaging65to75Numerator",[LowBackPainImaging65to75Numerator],"LowBackPainImaging65to75Denominator",[LowBackPainImaging65to75Denominator],"LowBackPainImaging65to75InverseRate",[LowBackPainImaging65to75InverseRate],"VirtualNumerator",[OutpatientUtilYTD],"VirtualRate",[OutpatientUtil1000YTD],"DepressionScreeningNumerator",[DepressionScreeningNumerator],"DepressionScreeningDenominator",[DepressionScreeningDenominator],"DepressionScreeningRate",[DepressionScreeningRate],"DepressionPositiveScreenNumerator",[DepressionPositiveScreenNumerator],"DepressionPositiveScreenDenominator",[DepressionPositiveScreenDenominator],"DepressionPositiveScreenRate",[DepressionPositiveScreenRate],"CervicalCancerNumerator",[CervicalCancerNumerator],"CervicalCancerDenominator",[CervicalCancerDenominator],"CervicalCancerRate",[CervicalCancerRate],"GenericNumeratorYTD",[GenericNumeratorYTD],"GenericDenominatorYTD",[GenericDenominatorYTD],"GenericFillRateYTD",[GenericFillRateYTD],"April YTD","April 2024 YTD","Zero",0)

Re: 2 datasets in Paginated reports using parameters

hackcrr — Sun, 21 Jul 2024 00:48:04 GMT

Hi, Karolina411

DataSet1 gets the detailed data filtered by YearMonth and PhysicianOrganization.

SELECT [PhysicianOrganization], [YearMonth], [MeasureDescription], [AllowedPMPMYTD] FROM [YourTable] WHERE [YearMonth] = @YearMonth AND [PhysicianOrganization] = @PhysicianOrganization

DataSet2 gets the overall HFPN data filtered by YearMonth.

SELECT [YearMonth], [HFPN_Overall_AllowedPMPMYTD] FROM [YourOverallTable] WHERE [YearMonth] = @YearMonth

Create a parameter @YearMonth and link it to the @PhysicianOrganization dataset.

Create a table in the report and bind it to DataSet1.
Use the LOOKUP function in the table to get the HFPN overall score from DataSet2. Here is an example of how to use the LOOKUP function in a table in Report Builder:

=Lookup(Fields!YearMonth.Value, Fields!YearMonth.Value, Fields!HFPN_Overall_AllowedPMPMYTD.Value, "DataSet2")

Add a table to your report and bind it to DataSet1.
Add columns for the measures Description, Rate, and HFPN Overall.
In the HFPN Overall column, use the LOOKUP function that I mentioned earlier.

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

Re: 2 datasets in Paginated reports using parameters

Karolina411 — Mon, 22 Jul 2024 15:19:13 GMT

Thank you but I do not quite understand this:

Create a parameter @YearMonth and link it to the @PhysicianOrganization dataset.

Create a table in the report and bind it to DataSet1.

Currently I have 2 parameters-- @YearMonth @PhysicianOrganization both are linked in dataset 1 and only YearMonth is linked in dataset 2. What should I do differently? Are you saying combine both data and physician organization datasets? I am lost---this is also a Tabular model so dataset2 looks like this: EVALUATE SUMMARIZECOLUMNS('Providers'[PCPLocation], 'Providers'[PhysicianOrganization],'DateDim'[Year],'DateDim'[MonthNameAbbreviation],'DateDim'[YearMonth],FILTER(VALUES('DateDim'[YearMonth]), ('DateDim'[YearMonth] =@YearMonth)), FILTER(VALUES('Patients'[Populations]), ('Patients'[Populations] = "GM")), "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "ERVisitsYTD", [ERVisitsYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD],"AntidepressantMedicationAcutePhaseNumerator",[AntidepressantMedicationAcutePhaseNumerator],"AntidepressantMedicationAcutePhaseDenominator",[AntidepressantMedicationAcutePhaseDenominator],"AntidepressantMedicationAcutePhaseRate",[AntidepressantMedicationAcutePhaseRate],"AnnualWellnessExamNumerator" ,[AnnualWellnessExamNumerator],"AnnualWellnessExamDenominator",[AnnualWellnessExamDenominator], "AnnualWellnessExamRate",[AnnualWellnessExamRate],"ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate],"ReadmitsYTD",[ReadmitsYTD],"ReadmitsYTDRate",[ReadmitsYTD%],"AdmitsYTD",[AdmitsYTD],"AllowedCost",[AllowedCost],"AllowedCostYTD",[AllowedCostYTD],"AllowedPMPMYTD",[AllowedPMPMYTD],"BreastCancerScreeningNumerator",[BreastCancerScreeningNumerator],"BreastCancerScreeningDenominator",[BreastCancerScreeningDenominator],"BreastCancerScreeningRate",[BreastCancerScreeningRate],"ColorectalCancerScreeningDenominator",[ColorectalCancerScreeningDenominator],"ColorectalCancerScreeningNumerator",[ColorectalCancerScreeningNumerator],"ColorectalCancerScreeningRate",[ColorectalCancerScreeningRate],"DAWNumerator",[DAWNumerator],"DAWDenominator",[DAWDenominator], "DAWFillRate",[DAWFillRate],"ERVisitsRate",[ERVisitsYTDMoreThan5Visits1000],"ERVisitsNumerator",[ERVisitsYTDMoreThan5Visits],"LowBackPainImaging18to64Numerator",[LowBackPainImaging18to64Numerator],"LowBackPainImaging18to64Denominator",[LowBackPainImaging18to64Denominator],"LowBackPainImaging18to64InverseRate",[LowBackPainImaging18to64InverseRate],"LowBackPainImaging65to75Numerator",[LowBackPainImaging65to75Numerator],"LowBackPainImaging65to75Denominator",[LowBackPainImaging65to75Denominator],"LowBackPainImaging65to75InverseRate",[LowBackPainImaging65to75InverseRate],"VirtualNumerator",[OutpatientUtilYTD],"VirtualRate",[OutpatientUtil1000YTD],"DepressionScreeningNumerator",[DepressionScreeningNumerator],"DepressionScreeningDenominator",[DepressionScreeningDenominator],"DepressionScreeningRate",[DepressionScreeningRate],"DepressionPositiveScreenNumerator",[DepressionPositiveScreenNumerator],"DepressionPositiveScreenDenominator",[DepressionPositiveScreenDenominator],"DepressionPositiveScreenRate",[DepressionPositiveScreenRate],"CervicalCancerNumerator",[CervicalCancerNumerator],"CervicalCancerDenominator",[CervicalCancerDenominator],"CervicalCancerRate",[CervicalCancerRate],"GenericNumeratorYTD",[GenericNumeratorYTD],"GenericDenominatorYTD",[GenericDenominatorYTD],"GenericFillRateYTD",[GenericFillRateYTD],"April YTD","April 2024 YTD","Zero",0)

Re: 2 datasets in Paginated reports using parameters

Karolina411 — Mon, 22 Jul 2024 20:50:39 GMT

hackcrr - Also when I join both the Physician Organization and DateDim together I get this when I view the report:

Do you think I need to somehow create two tables rather than referencing DataSet2?