SQL Dynamic Pivot Tables, BIRT Cross Tab Report

The day was spent working on getting a cross tab report in BIRT. It was a series of two steps forward and one step back, but at the end of the day it was done! Here's how I did it.

Here is a sample of the data in the table:

The report needs to do a monthly sum of the amounts for each Region/Office where the documentation is not complete. I started out by doing a regular pivot table in SQL. This worked fine but then I realized that my months and years would be changing so what I needed was a dynamic pivot table. Using this example from Kodyaz, I came up with this dynamic pivot table:

DECLARE @PivotColumnHeaders VARCHAR(Max)
--Create a list for the column names
--This list will be used in our PIVOT down below
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + [Month] + ']',
    '[' + [Month] + ']'
  )
FROM (select distinct top 50  datename(month, [dtAccountingPeriod]) + ' ' + cast(YEAR([dtAccountingPeriod]) AS varchar) as [Month]
, YEAR([dtAccountingPeriod]) AS IDXY, Month([dtAccountingPeriod]) AS IDXM
From[INformV2].[dbo].[FoAccountingDocumentAudit]
where [DocumentationComplete] = 0
order by IDXY, IDXM) TTD

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from(
SELECT 
luFoOfficeRegion.Value as Region
,FoOffice.OfficeIdentifier as Office
,datename(month, [dtAccountingPeriod]) + '' '' + cast(YEAR([dtAccountingPeriod]) AS varchar)  as [Month]
,[amtUSD] 
FROM [INformV2].[dbo].[FoAccountingDocumentAudit]
join FoOffice on fkOfficeID = FoOffice.ID
join luFoOfficeRegion on [INformV2].[dbo].[FoAccountingDocumentAudit].fkRegionID = luFoOfficeRegion.ID
where [DocumentationComplete] = 0
) Datatable
PIVOT(sum([amtUSD]) for [Month] in (' + @PivotColumnHeaders + ' )
)as pvtMonth
order by Region
'
EXECUTE(@PivotTableSQL)

The result was this:

Very nice!
Now on to BIRT. I started to create the report in BIRT and realized there was a problem, BIRT likes to work with static columns. Back to SQL.
I used this in a view to return my data:

SELECT top 100    dbo.luFoOfficeRegion.Value AS Region, dbo.FoOffice.OfficeIdentifier AS Office
,datename(month, dbo.FoAccountingDocumentAudit.dtAccountingPeriod) + ' ' + cast(YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) AS varchar)  as [MonthYear]
,SUM(dbo.FoAccountingDocumentAudit.amtUSD) AS Total
,YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Year
,Month(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Month
FROM dbo.FoAccountingDocumentAudit INNER JOIN
dbo.FoOffice ON dbo.FoAccountingDocumentAudit.fkOfficeID = dbo.FoOffice.ID INNER JOIN
dbo.luFoOfficeRegion ON dbo.FoAccountingDocumentAudit.fkRegionID = dbo.luFoOfficeRegion.ID
WHERE dbo.FoAccountingDocumentAudit.DocumentationComplete = 0
GROUP BY dbo.luFoOfficeRegion.Value, dbo.FoOffice.OfficeIdentifier, dbo.FoAccountingDocumentAudit.dtAccountingPeriod

The year and month are used in BIRT to sort the columns.

The returned data:

Back to BIRT. I used the view in my report and created a data cube:

Then using the data in a BIRT "Cross Tab", applying a little formating, and here's my final result: