I have created the report below in SSRS to show the total by month for the calendar year.
As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.
| 01 | 02 | 03 | 03 | 4 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | Total |
| AA | | | | | | 17.5 | | | 22 | 17.5 | 75 | 30 | | 162 |
| BB | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 195 |
| CC | 15 | 15 | 15 | 15 | 15 | 337 | 15 | 233.3 | 15 | 325.5 | 117 | 15 | 15 | 1147.8 |
| DD | 315 | 290 | 1231 | 1231 | 1231 | 269 | 953 | 384.8 | 706.8 | 1798.8 | 290 | 602 | 419 | 9720.25 |
| Total | 345 | 320 | 1261 | 1261 | 1261 | 638 | 983 | 633.1 | 758.8 | 2156.8 | 497 | 662 | 449 | 11225.1 |
As I go to project this report for January 2007 I am having trouble.
I want the report to appear as below with the 12 requisite columns.
| | 01 | 02 | 03 | 03 | 4 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | Total |
| AA | | | | | | | | | | | | | | 0 |
| BB | 15 | | | | | | | | | | | | | 15 |
| CC | 15 | | | | | | | | | | | | | 15 |
| DD | 315 | | | | | | | | | | | | | 315 |
| Total | 345 | | | | | | | | | | | | | 345 |
But as only data for the first column is returned from my query I only get one column to appear.
| | 01 | Total |
| AA | | 0 |
| BB | 15 | 15 |
| CC | 15 | 15 |
| DD | 315 | 315 |
| Total | 345 | 345 |
How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?
Hi,
One option I can think of is adding some dummy records to the query, using the UNION statement. One for each month of the year, with a value 0.
Regards, Jeroen
|||
At the risk of sounding like a total noob. Can you modify the query below or give an example?
SELECT
bo.AS400Billingid,
OrgName,
ih.date,
ih.rev
FROM billingorg bo
INNER JOIN
(SELECT
AS400BillingID,
substring(InvoiceDate,4,2) date,
sum(SalesAmount) rev
FROM
InvoiceHeader
WHERE
left(InvoiceDate,3)='106'
GROUP BY
AS400BillingID,substring(InvoiceDate,4,2)) ih
ON bo.AS400BillingID=ih.AS400BillingID
|||
I found a solution.
SELECT
id,
OrgName,
AddedDate,
SalesTerritory,
IndustryCode,
AccountManager,
[01] AS 'JAN',
[02] AS 'FEB',
[03] AS 'MAR',
[04] AS 'APR',
[05] AS 'MAY',
[06] AS 'JUN',
[07] AS 'JUL',
[08] AS 'AUG',
[09] AS 'SEP',
[10] AS 'OCT',
[11] AS 'NOV',
[12] AS 'DEC'
FROM
(SELECT
bo.AS400Billingid id,
bo.OrgName,
bo.AddedDate,
bo.SalesTerritory,
bo.IndustryCode,
bo.AccountManager,
substring(ih.InvoiceDate,4,2) date,
ih.SalesAmount rev
FROM
billingorg bo INNER JOIN InvoiceHeader ih
ON bo.AS400BillingID=ih.AS400BillingID
WHERE
left(InvoiceDate,3)='106') as data
PIVOT
(
sum(rev)
FOR date IN([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) XTab
