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
Author
Message
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
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
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:47 AM
Points: 130, Visits: 710
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
Posted Thursday, August 28, 2014 8:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:31 PM
Points: 3, Visits: 13
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 Year 2011 2012 2013 2014 SSRS gives Q1 Year 2012 2013 2014
Headcount 1000 1000 1000 Headcount 1000 1000 1000
or or
Q2 Year 2011 2012 2013 2014 Q2 Year 2011 2012
Headcount 1000 1000 Headcount 1000 1000
or or
Q3 Year 2011 2012 2013 2014 Q3 Year 2012 2013 2014
Headcount 1000 1000 1000 Headcount 1000 1000 1000


Post #1608476
Posted Thursday, August 28, 2014 8:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:31 PM
Points: 3, Visits: 13
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 2011 2012 2013 2014 SSRS gives Q1 Year 2012 2013 2014
Headcount 1000 1000 1000 Headcount 1000 1000 1000
or or
Q2 Year 2011 2012 2013 2014 Q2 Year 2011 2012
Headcount 1000 1000 Headcount 1000 1000
or or
Q3 Year 2011 2012 2013 2014 Q3 Year 2012 2013 2014
Headcount 1000 1000 1000 Headcount 1000 1000 1000


Cheers Vonnie
Post #1608478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse