CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012
Paul
Paul

Reputation: 147

ssrs matrix not sorting and not getting other months

I have a dataset like
enter image description here

I am creating a matrix to represent the data like this-
enter image description here

The problem is since I don't have other months (Mar-Dec) in the Dataset, I'm getting for first two months only (Also not in order). I'm new to SSRS and your help is much appreciated. Thanks!

Upvotes: 0

Views: 50

Answers (2)

alejandro zuleta
alejandro zuleta

Reputation: 14108

In the Month Group Properties / Sorting tab add this expression:

Switch(
Fields!Month.Value="Jan",1,
Fields!Month.Value="Feb",2,
Fields!Month.Value="Mar",3,
Fields!Month.Value="Apr",4,
Fields!Month.Value="May",5,
Fields!Month.Value="Jun",6,
Fields!Month.Value="Jul",7,
Fields!Month.Value="Aug",8,
Fields!Month.Value="Sep",9,
Fields!Month.Value="Oct",10,
Fields!Month.Value="Nov",11,
Fields!Month.Value="Dec",12
)

Also set A-Z in Order setting.

Upvotes: 0

William Xu
William Xu

Reputation: 261

I had a similar situation. My solution was to include every month in my dataset, by using UNION and CROSS JOIN. Use CROSS JOIN to include all the possible combination of Department and Month, regardless whether you have data or not, but set their Amount to be 0. Like this:

You_Original_SELECT_Statement
UNION
SELECT D.Department, M.Month, 0 AS Amount
FROM Month M CROSS JOIN Department D

Upvotes: 1

Related Questions