SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding Columns to report if no data in a given period


Adding Columns to report if no data in a given period

Author
Message
cbernardes
cbernardes
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 90
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
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4727 Visits: 3215
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cbernardes
cbernardes
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 90
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))
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