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

Adding Columns to report if no data in a given period Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:13 PM
Points: 20, Visits: 57
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
Post #1486024
Posted Monday, August 19, 2013 10:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:00 AM
Points: 2,993, Visits: 2,590
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.



Post #1486101
Posted Monday, August 19, 2013 11:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1486108
Posted Tuesday, August 20, 2013 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:13 PM
Points: 20, Visits: 57
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))
Post #1486435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse