Adding Columns to report if no data in a given period

Question

Adding Columns to report if no data in a given period

cbernardes

SSC Eights!

Points: 938
More actions
August 19, 2013 at 3:18 pm

#297121

I have a dataset for each record it has a CompanyID, RevenueMonth, RevenueYear, Revenue
When I create the report, I am grouping each CompanyID and showing their monthly revenue for a given year.
But in a given year, not all companies have any revenues for a particular month.
Example:
A sample record would look like:
CompanyID, RevenueMonth, RevenueYear, Revenue
1,05,2013,5.00
1,08,2013,6.00
1,03,2013,3.00
End Result, I would like my report to look like this with CompanyID 1.
Company ID|01|02|03|04|05|06|07|08|09|10|11|12
1 0.00|0.00|3.00|0.00|5.00|0.00|0.00|6.00|0.00|0.00|0.00|0.00
In my current Report, it will only fill column headings with March (03), May (05) and August (08).
Company ID|03|05|08
1 3.00|5.00|6.00
How do I get my Report to add the missing months for the year?
I hope my questions is clear. This is my first post.
Thanks,
Chuck

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

A matrix report will only show the months that exist in the dataset used for the report. So, as you are finding, only 3 are currently appearing.

You will need to include all of the months you need on your report for at least one of the companies in the report. I guess one of the issues you will encounter is that at the start of the year, there are no sales for most of the months of the year. Your SQL code will need to include the extra months in the dataset - probably with nulls for the values. If all 12 months appears in the dataset your report will look OK. The "missing" months only need to exist for a single company for the report to show the headings for all 12 months. So, if you only have values for month 01 and you have 20 companies on your report, you dataset would currently have 20 records in it and there would only be one month heading. If you changed you dataset so the at least one of the companies had a record for all 12 months, you would get the result you are after. You don't need to include all 12 months for all companies (although this may make it easier to code)

...hope that makes sense.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

The missing months don't need to appear for any of the companies if you use a calendar table or a smaller table with just the months of interest in it and do an outer join to that. I'm not sure how to do that in an SSRS matrix but it's a fairly simple thing to do with a query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cbernardes SSC Eights! Points: 938 More actions · Answer 3

happycat59 (8/19/2013)
A matrix report will only show the months that exist in the dataset used for the report. So, as you are finding, only 3 are currently appearing.
You will need to include all of the months you need on your report for at least one of the companies in the report. I guess one of the issues you will encounter is that at the start of the year, there are no sales for most of the months of the year. Your SQL code will need to include the extra months in the dataset - probably with nulls for the values. If all 12 months appears in the dataset your report will look OK. The "missing" months only need to exist for a single company for the report to show the headings for all 12 months. So, if you only have values for month 01 and you have 20 companies on your report, you dataset would currently have 20 records in it and there would only be one month heading. If you changed you dataset so the at least one of the companies had a record for all 12 months, you would get the result you are after. You don't need to include all 12 months for all companies (although this may make it easier to code)
...hope that makes sense.

Jeff Moden (8/19/2013)
The missing months don't need to appear for any of the companies if you use a calendar table or a smaller table with just the months of interest in it and do an outer join to that. I'm not sure how to do that in an SSRS matrix but it's a fairly simple thing to do with a query.

Thank you both for your replies. I do understand what you are getting it. What I ended up doing based on another suggestion I found similar to my problem was to make static columns in my Report.

I created 12 columns for each month of the year and I adjusted this formula respectively as noted below displaying 3 of the 12 months.

For January

=Sum(IIf(Fields!RevenueMonth.Value = 1, Fields!Revenue.Value, Nothing))

..

For June

=Sum(IIf(Fields!RevenueMonth.Value = 6, Fields!Revenue.Value, Nothing))

..

For December

=Sum(IIf(Fields!RevenueMonth.Value = 12, Fields!Revenue.Value, Nothing))