Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSRS 2005 Display Columns in a matrix report even if no data is present in the sql table Expand / Collapse
Posted Thursday, June 2, 2011 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 6, 2012 9:23 AM
Points: 9, Visits: 9

I have created a report in SSRS 2005 which contains a Matrix and I want to dislplay the following information:

Row:UserID, PaymentAmt, Column:PaymentDate

I want to display 12 months (Jan, Feb, Mar...etc) whether there is a PaymentDate or not in the db, with the text "N/P" in the PaymentAmt field

I have tblMonthQtr in the db with a Data Type of nchar(10), and tblPayment which contains a PaymentDate field which is Data Type smalldatetime

I have tried the following:

=iif(Sum(Fields!PaymentAmt.Value is nothing, "N/P", Sum(Fields!PaymentAmt.Value))

I believe I need to tie in the PaymentDate to this somehow but am not sure how to do that.

The logic seems to be - If the PaymentDate <> Month 1-12, then "N/P" with the Name of the Month displayed in the PaymentDate textbox.

Any assistance would be appreciated :)

Thank you,

Post #1119042
Posted Monday, August 6, 2012 7:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
I am having a very similar issue, except in SSRS 2008. I have a matrix table, with "Accounts" as rows and "Locations" as columns. The data is a money sum value. Basically, I would like to have all Locations (columns) display even when there is no data. Right now, only the columns with data show. Any advice would be greatly appreciated!
Post #1340598
Posted Thursday, August 9, 2012 7:48 AM


Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 122, Visits: 633
You will only get a column in the matrix if there's a row in your dataset that contains a value for that column. So if your dataset contains e.g.:-
Month Sales
Jan 12
Feb 32
Mar 16
... and you set month as the grouping for your columns in the matrix you can't be surprised that you don't get a column for April. The matrix doesn't even know that April exists.

So you need to "force" rows into the dataset for all the months. You do this in your dataset's query. A good technique for doing this is to use a Cross Join to build the dimensions of your dataset and then left join from that to a table that contains that actual data. Something like:-
Select Month, Region, count(S.SalesID)
From Months M
Cross Join Regions R
Left Join Sales S
on M.MonthID = S.MonthID
and R.RegionID = S.RegionID

That ensures you always have the full set or row and column values ready for your matrix to pivot out. If there's no data for a particular combo (i.e. you made no sales In Region 1 in November) you'll hust have a null in that cell which the matrix will display as a space, but you can use an isnull to substitute any value you like into that cell.
Post #1342648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse