question

ckmst11 avatar image
ckmst11 asked

SSRS How do I divide column group totals of two matrices that use different datasets?

I have an SSRS Report that has two matrices with a different dataset in each matrix. I have a total column in each matrix. I want to perform a calculation on the values in the totals columns. Here is what I want to do: MatrixA.TotalColumn.Value divided by MatrixB.TotalColumn.Value So I want to see a column of values showing the calculation between the two. MatrixA uses a different dataset than MatrixB. Both MatrixA and MatrixB have the same number of rows and columns. There is a total column on the column group for each matrices. I want to see a column of values of the division of the total columns.
ssrscalculationsmatrix
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
David Wimbush avatar image
David Wimbush answered
How about changing the query so the results are all in the same dataset?
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ckmst11 avatar image ckmst11 commented ·
Yes. I could do that. However, I already have the report set up the way I want it with two matrices and two datasets. I'm just missing the calculation column. I guess I'll have to change the query if I can't find a way to do the calculation. Thanks for your response.
0 Likes 0 ·
David Wimbush avatar image David Wimbush commented ·
Fair enough. That just seemed like the clearest place for a join. In that case, as long as you have a field you can 'join' the datasets on, you should be able to use the Lookup function in an expression to get the relevant field value from the other dataset.
0 Likes 0 ·
ckmst11 avatar image
ckmst11 answered
I think I'll change the query since I'm having problems getting the lookup function to return the correct results. Thanks for your help.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.