SSRS linking two matrix tables

Question

I am using SSRS 2008. I have a report with 2 different matrix tables having two different datasets as their sources. The data comes fine in both the tables individually. BUT My issue starts where I have to use data from one table to calculate percentage in the second table. Here are the details:

Table 1: Contains columns: Date, Referal_Status ('1' for each valid row), Department

Table 2: Contains Columns: Date, Membership_Status ('1' for each valid row), Department

In table 1, I need to show referral counts (sum of valid Status) grouped by month in columns, and grouped by department in rows. Also an additional row and column for totals of the same. This is implemented with no issues.

In table 2, I need to show membership counts (sum of valid Status) AND referals to membership percentage grouped by each month in columns, and grouped by department in rows. Also an additional row and column for totals of the same. The issue starts when I try to implement the percentage calculation.

Let's say I have the membership counts number for April 2014 in the membership table. How do I take the referrals count number for April 2014 from the referral table and compute the April 2014 percentage as referal_num/Membership_Num * 100

The issue that I face is the scopes of both the matrix tables being different.

Please help me attain the above in the SSRS matrix tables.

Am I providing enough information to get through to you folks about my issue? Please let me know in case you need more information from me.

Answer 1

This is often a road to misery, but anyway ...

I would use the Lookup Function to retrieve the Referrals count. You will need to concatenate your two key columns (Date and Department) into one expression.

This sounds great and often works well. However when it doesn't work on odd rows or combinations of data, you are flying blind trying to debug it.

Good luck!

PS: actually for a reliable solution that is easy to debug, I would go back and combine the data upstream so it can be presented to SSRS in one Dataset. I would probably use SSIS for this.