StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Database level</strong></p> <p>Since you're only returning a year at a time, you can create a calendar table and add this to your result set:</p> <p>Keeping it as simple as possible, with the date table coming from a CTE:</p> <pre><code>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 </code></pre> <p><a href="http://sqlfiddle.com/#!3/979db/1" rel="nofollow">SQL Fiddle with demo</a>.</p> <p>This will return a year/month for each company in the result set.</p> <p>In the long run it would be better to move from a CTE to a permanent calendar table in the database.</p> <p>You can then implement this in the report using a matrix style tablix.</p> <p><strong>Report level</strong></p> <p>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:</p> <pre><code>=Sum(IIf(Fields!RevenueMonth.Value = 2, Fields!Revenue.Value, Nothing) </code></pre> <p>For the February column.</p> <p>This would work with your existing dataset without any database code changes.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload