Monday, March 19, 2012

Force columns to appear in matrix.

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

No comments:

Post a Comment