A Customized Crosstab Report

• Download source - 1.31 KB

Introduction

This topic gives you a new approach on how to organize your data into tables that will help you generate large "Crosstab" Reports using SQL Server 2000/2005. This is a custom solution and does not use and PIVOT/UNPIVOT options which are available in SQL Server 2005.

Background

Recently, I came cross a new problem where I needed to generate a crosstab report that had to pull data for 48 months back in time from the current month, where the report had to calculate and give various amounts like the "Year To Date," "Advances," "Paid Amount," "Recovered Amount," "Adjustment Amount" and "Balance" for each month. This information had to be provided for each "Service Provider." In short, I needed to create a report with data spanning across 250 columns as a crosstab report. Please do not look at the numbers and sum the totals. They will not match. The data tables are given to support the concept and approach. Given below is a sample report format for a period of three months:

This basic requirement makes the report a bit complicated to generate, as we are still using SQL 2000 and the PIVOT/UNPIVOT option is not available out of the box. The other things that make this report complex are:

1. Unavailability of XML data types and XML data manipulation
2. Unavailability of Object data types as Column types, as available in Oracle 8 and above

The Solution

If not the XML data types, the Object data types kind of violate the First Normal Form (1NF) where a relation must contain only atomic values in each row and column. This relationship of an Object data type column with the primary key is a one to many relationship. We can also see this as a nested table scenario where the primary key maps to an in-memory object which is a table by itself.

Keeping the basics in mind, I kind of looked for several options to implement a solution without writing 1000 of lines of T-SQL Code. Finally I arrived at this solution below.

Let us see this approach through an example before we start to look at the actual SQL Tables, Stored Procedures and Functions that I created to generate this report.

The most important concept that helped solve this issue is "Pointers." So, what is a Pointer? A Pointer is an address of a memory location where the "Data" is present. In a "C/C++ Language" perspective this data is an Instance of a "Structure," a "Class" or any other basic data type.

Other basic concepts of RDBMS that helped solve this issue are the "Unique Index" and the "Primary Keys."

By tying these two programming constructs and database concepts together, I have implemented this approach in getting the "Crosstab" report.

Source Data for the Report

Given below is a summary of the structured data that needs to be presented as a Crosstab report as mentioned above. In reality the different amount/values come from three different sources/tables and not from a single table. To keep it simple, I have listed all different amount/ $value columns along with their provider and process period (2007/12 to 2007/07) in one single table. This table is a single table representation of tblClaims table that you will see in the example.

Implementation Details to this Approach

As mentioned in the problem definition, this report needs to present 6 different amount/$ values for a period of 48 months. It is not too hard to build a table with so many columns (250+) and pretty much most of the RDBMS like SQL Server support this and bring the same back into reports. But the basic idea is to PIVOT/Transform the data of "Rows of Process Period" into Data of "Process Period Columns." The way I have implemented this solution is by using "Concepts and Language Constructs," as I have mentioned above in the "The Solution" section of this article.

As part of the solution I had to create two tables. The First table is called CrosstabXRef. The structure of this table is given below:

Table Name: CrosstabXRef

Note the data type of the columns (Period48, Period47, Period46, ..., Period02, Period01). All these columns are of type uniqueidentifier that will hold a unique value which maps to a record in the table CrosstabLookup given below.

Table Name: CrosstabLookup

So, how are these two tables related? The answer is on the data type of the Period## columns that are there in these two tables. If you have guessed it correctly, yes the GUID value that is returned as part of the =NEWID () method is stored as data values of these uniqueidentifier data typed columns.

When a record is present as part of the "Claims" payment and adjustment selection of a given "Period" and "Service Provider" an entry will be made into this CrosstabLookup table. And this process will be continued for the selected "48" months window. If there is no activity for a particular "Svc Provider" for a specific "Period" then no record will be inserted into this lookup table. But that situation is unlikely to happen as Advance is paid to the "Svc Provider" every month.

So in a nutshell, the data in the database will look as depicted in the diagram below:

For achieving this, I have to create two DB Objects, one Stored procedure to construct the above mentioned two tables and one function to return the data stored in the CrosstabLookup based on the uniqueidentifier and an Index.

The SQL code block is the Stored Procedure that I have created which will load the data into CrosstabLookup and CrosstabXRef. The name of this stored procedure is spProcessClaimsCrosstabMaping.

CREATE PROCEDURE dbo.spProcessClaimsCrosstabMaping
AS

    SET CONCAT_NULL_YIELDS_NULL OFF

    Declare @SvcProv         varchar(10)
    Declare @Advance        numeric
    Declare @PaidAmt        numeric
    Declare @RecAmt        numeric
    Declare @AdjAmt        numeric
    Declare @GUID            uniqueidentifier
    Declare @DynamicSQL        varchar(3000)
    Declare @ProcessPeriod        numeric
    Declare @YearPart        Varchar(4)
    Declare @MonthPart        Varchar(2)
    Declare @CharYear        Varchar(6)
    Declare    @StartDate        datetime
    Declare @EndDate        datetime

    DECLARE SvcProvCursor CURSOR READ_ONLY
    FOR
    Select distinct ServiceProvider from tblClaims

    OPEN SvcProvCursor

    FETCH NEXT FROM SvcProvCursor INTO @SvcProv
    WHILE @@FETCH_STATUS = 0
    BEGIN

        --SET @ProcessPeriod = 200706
        SET @EndDate = GETDATE()
        -- THE STATIC NUMBER SHOULD BE 1 MONTH LESS TO THE ACTUAL MONTHS REQUIRED
        SET @StartDate = DATEADD(mm,-12, @EndDate)

        SET @DynamicSQL = ' INSERT INTO dbo.CrosstabXRef ' +  ' values (''' + _
            @SvcProv + ''',''' + CAST(@StartDate AS Varchar(20)) + ''','

        WHILE (@StartDate <= @EndDate)
        BEGIN

            SET @YearPart = CAST(DATEPART(yyyy, @StartDate) AS VARCHAR(4))
            SET @MonthPart = CAST(DATEPART(mm, @StartDate) AS VARCHAR(2))
            IF LEN(@MonthPart) = 1
                SET @MonthPart = '0' + @MonthPart

            SET @CharYear = @YearPart + @MonthPart

            SET @ProcessPeriod  = CAST(@CharYear AS numeric)
            --PRINT @ProcessPeriod

            SET @GUID = NULL

            Select @Advance = Advance ,
            @PaidAmt =  Paid , @RecAmt = Received,
            @AdjAmt = Adjustment
            from tblClaims tc where tc.ProcessDate =  @ProcessPeriod and
            ServiceProvider=@SvcProv

            if @@ROWCOUNT = 1
            BEGIN
                SET @GUID = NEWID()
                --PRINT  @GUID

                INSERT INTO  dbo.CrosstabLookup
                VALUES
    --                ("PeriodIdentifier", "Advance", "Paid", "Received", "Adjustment")
                    (@GUID, @Advance, @PaidAmt, @RecAmt, @AdjAmt)

                SET @DynamicSQL =  @DynamicSQL  + '''' +  _
                    CAST(@GUID AS varchar(64)) + ''''
            END
            ELSE    --END OF IF BLOCK
            BEGIN

                -- NO GUID VALUE WILL BE SET TO MAP THIS COLUMN WITH A RECORD
                -- IN THE MAPPING TABLE
                SET @DynamicSQL =  @DynamicSQL  + 'NULL'

            END    --END OF ELSE BLOCK

            if (@StartDate <> @EndDate)
            BEGIN
                IF (@GUID <> NULL)
                    SET @DynamicSQL =  @DynamicSQL + ''','
                ELSE
                    SET @DynamicSQL =  @DynamicSQL + ','
            END

            SET @StartDate = DATEADD(mm,1, @StartDate)

        END -- END OF THE WHILE LOOP

            IF (@GUID <> NULL)
                SET @DynamicSQL =  @DynamicSQL + ''')'
            ELSE
                SET @DynamicSQL =  @DynamicSQL + ')'

        --PRINT @DynamicSQL
        PRINT 'WORKING....!!'
        EXEC (@DynamicSQL)

        FETCH NEXT FROM SvcProvCursor INTO @SvcProv
    END
    CLOSE SvcProvCursor
    PRINT 'FINISHED PROCESSING...!!'
GO

The second function that I have created to present the data is called fnClaimsCrosstabHpr. This will help us get the $ Values based on the PeriodIdentifier, which is indirectly mapped to a Service Provider.

CREATE  FUNCTION dbo.fnClaimsCrosstabHpr(@PeriodId uniqueidentifier, @ColumnIndes int)
RETURNS float
AS
BEGIN
    Declare @Amount float

    if (@ColumnIndes = 1) Select  @Amount=_
            Advance from CrosstabLookup where PeriodIdentifier = @PeriodId
    if (@ColumnIndes = 2) Select  @Amount=_
            Paid from CrosstabLookup where PeriodIdentifier = @PeriodId
    if (@ColumnIndes = 3) Select  @Amount=_
            Received from CrosstabLookup where PeriodIdentifier = @PeriodId
    if (@ColumnIndes = 4) Select  @Amount=_
            Adjustment from CrosstabLookup where PeriodIdentifier = @PeriodId

    RETURN(@Amount)
END

The next step is to execute the Stored Procedure to populate the data into CrosstabLookup and CrosstabXRef tables. Once this is complete, the next step is to generate the report itself. Given below is a sample query that will help generate a table of data for the Crosstab report. This data will be directly exported to Excel for further analysis.

select SvcProvider, RecGeneratedDate,
dbo.fnClaimsCrosstabHpr(Period48, 1) AS Advance_48,
dbo.fnClaimsCrosstabHpr(Period48, 2) AS Paid_48,
dbo.fnClaimsCrosstabHpr(Period48, 3) AS Received_48,
dbo.fnClaimsCrosstabHpr(Period48, 4) AS Adjustment_48,
dbo.fnClaimsCrosstabHpr(Period47, 1) AS Advance_47,
dbo.fnClaimsCrosstabHpr(Period47, 2) AS Paid_47,
dbo.fnClaimsCrosstabHpr(Period47, 3) AS Received_47,
dbo.fnClaimsCrosstabHpr(Period47, 4) AS Adjustment_47,
dbo.fnClaimsCrosstabHpr(Period01, 1) AS Advance_01,
dbo.fnClaimsCrosstabHpr(Period01, 2) AS Paid_01,
dbo.fnClaimsCrosstabHpr(Period01, 3) AS Received_01,
dbo.fnClaimsCrosstabHpr(Period01, 4) AS Adjustment_01
from CrosstabXRef

Attachments

Attached is a copy of the SQL Script and SQL Objects that are listed as part of this article for download.

Conclusion

For writing this article, I had to make few changes to the actual tables and SQL DB objects. I hope the changes I have made have not caused differences making this document unclear to read. If you have any questions please write to me. I will try to address them.

Enjoy learning by reading.