Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2005 Display Columns in a matrix report even if no data is present in the sql table


SSRS 2005 Display Columns in a matrix report even if no data is present in the sql table

Author
Message
chuck-803874
chuck-803874
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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, ColumnTongueaymentDate

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 Smile

Thank you,

Chuck
dj1202
dj1202
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 231
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!
FunkyDexter
FunkyDexter
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 1066
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.
Vonnie-917791
Vonnie-917791
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Vonnie-917791
Vonnie-917791
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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 Sad

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search