question

RaviTheja-3153 avatar image
0 Votes"
RaviTheja-3153 asked RaviTheja-3153 commented

Limit no of columns in SSRS matrix report

I have designed SSRS Matrix report following the bellow post. my requirement is as below,


123871-image.png


and expected Output :

123827-image.png


I am facing below issue where my week sub total are getting multiplied by the number of store displayed per page, below is the output that i am getting with this issue. is it possible to overcome this issue?

123828-image.png


sql-server-reporting-services
image.png (11.3 KiB)
image.png (8.5 KiB)
image.png (8.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @RaviTheja-3153 ,
The reason you are present is a matrix is a summary of aggregated data grouped by rows and columns, similar to a PivotTable or a Crosstab.
At runtime, when combining report data and data areas, as you add columns to column groups and rows to row groups, the matrix will grow horizontally and vertically on the page. The display of a matrix cell is limited to the aggregate value of the intersection of the row group and column group to which the cell belongs. You can understand that the scope of the cells in the matrix is the intersection of two.

When you "Add Total", it is the sum between the row group and the column group.

In your case, you should add "Product" to the parent group and group by "Week". When you "Add Total", the value obtained is the column group "[Store]" and the row group "[Week]" sum. That is what you see "20", "80", "20".
Usually using Matrix, we need to know what you see is not what you get, which is different from Table.
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered RaviTheja-3153 commented

Is it possible that you mix the figures from column "Total" and "Store total"?
For "1W" the sum of "Total" is 20 as in your report; the sum for "Store total" is only 10.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Since i am showing Two Store names (x & y) in one page , that number of stores is getting multiplied with the Total(10*2)

if i try to populate 3 stores - X, Y and Z in same page then the Total is shown as "30" (10*3) instead of 10

0 Votes 0 ·