June 2, 2011 at 1:38 pm
Hello,
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,
Chuck
August 6, 2012 at 7:36 am
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!
August 9, 2012 at 7:48 am
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.
August 28, 2014 at 8:31 pm
SSRS matrix and displaying columns when there's missing data: I have tried the cross join example as this is not working out for my requirements as so much of the data is an aggregate within a year by..... so wondering if you could help
Take Qualification enrolment headcounts by year. The selection parameter is by Qualification and the headcount data is to populate a 'template' report that is exported into excel (that's really the problem!!). The report holds much aggregated demographic data creating a template that is copied into an EXCEL worksheet providing links for a graphical summarised front page
Trouble is not all Qualifications are taught for all the years of the front page linking worksheet causing serious issues with maintaining accuracy with the template - so somehow I need all qualifications to have all years/columns 2011-2014 regardless if there is any data.
In the past I have forced this by creating case statements for all permutations and inserted those in each relevant year column but this report just has too many row variable to make it viable
Q1 Year2011201220132014SSRS givesQ1 Year201220132014
Headcount100010001000Headcount100010001000
oror
Q2 Year2011201220132014Q2 Year20112012
Headcount10001000Headcount10001000
oror
Q3 Year2011201220132014Q3 Year201220132014
Headcount100010001000Headcount100010001000
August 28, 2014 at 8:36 pm
SSRS matrix and displaying columns when there's missing data: I have tried the cross join example as this is not working out for my requirements as so much of the data is an aggregate within a year by..... so wondering if you could help. in the example below it sums the headcount by each year 🙁
Take Qualification enrolment headcounts by year. The selection parameter is by Qualification and the headcount data is to populate a 'template' report that is exported into excel (that's really the problem!!). The report holds much aggregated demographic data creating a template that is copied into an EXCEL worksheet providing links for a graphical summarised front page
Trouble is not all Qualifications are taught for all the years of the front page linking worksheet causing serious issues with maintaining accuracy with the template - so somehow I need all qualifications to have all years/columns 2011-2014 regardless if there is any data.
In the past I have forced this by creating case statements for all permutations and inserted those in each relevant year column but this report just has too many row variables to make it viable
Q1 Year 2011201220132014 SSRS givesQ1 Year 201220132014
Headcount100010001000Headcount100010001000
oror
Q2 Year 2011201220132014 Q2 Year 20112012
Headcount 10001000Headcount10001000
oror
Q3 Year 2011201220132014Q3 Year 201220132014
Headcount100010001000Headcount100010001000
Cheers Vonnie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply