Adding Columns heading to report if no data in a given period

Question

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.

Try creating a table of months to JOIN against. You could also try [this](http://stackoverflow.com/questions/9381915/running-total-over-date-range-fill-in-the-missing-dates) — MrCleanX, Aug 19 '13 at 21:34
@MrCleanX, thanks for your reply but I am a bit confused on how to implement it from the link you have posted. I will try to look it over tonight again. If you can provide another example, that would be great. thanks. — Charles Bernardes, Aug 19 '13 at 21:56

score 2 · Accepted Answer · answered Aug 19 '13 at 22:26

Database level

Since you're only returning a year at a time, you can create a calendar table and add this to your result set:

Keeping it as simple as possible, with the date table coming from a CTE:

with months as -- get required year/months
(
  select RevenueYear = 2013
    , RevenueMonth = 1
  union all
  select RevenueYear = 2013
    , RevenueMonth = RevenueMonth + 1
  from months
  where RevenueMonth < 12
)
select CompanyID = coalesce(r.CompanyID, c.companyID)
  , RevenueMonth = coalesce(r.RevenueMonth, m.RevenueMonth)
  , RevenueYear = coalesce(r.RevenueYear, m.RevenueYear)
  , Revenue = isnull(r.Revenue, 0.0)
from months m
  cross join (select distinct CompanyID from records) c -- make sure all companies included
  left join records r on m.RevenueYear = r.RevenueYear
    and m.RevenueMonth = r.RevenueMonth

SQL Fiddle with demo.

This will return a year/month for each company in the result set.

In the long run it would be better to move from a CTE to a permanent calendar table in the database.

You can then implement this in the report using a matrix style tablix.

Report level

If you'd prefer to do this at the report level, you can set up a table-style tablix with 12 permanent columns, one for each month, then populate the month revenue cells with expressions like:

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

For the February column.

This would work with your existing dataset without any database code changes.

Thanks so much. I used the Report Level Option. This worked out perfectly. I will keep this tucked away when I need this at the Database level too. I appreciate you providing both ways. Well done. — Charles Bernardes, Aug 20 '13 at 15:47

Adding Columns heading to report if no data in a given period

1 Answers1