Tejana

Microsoft Dynamics GP Payroll Report, SQL Procedure, One to Many in two columns

Posted in Microsoft, Software, SQL by tejana on April 24, 2007

Stored Procedure Reporting One to Many in Two Colmuns

Database designers like one-to-many relationships, report writers don’t !

The report has to meet a deadline and I would prefer to just whip up a custom report meeting boss man’s request than struggle with the existing report. The main record is simple enough representing the totals of a single payroll check. It is the details, now that’s a challenge. There are many detail records for each check in a separate table. I am only interested in three detail variables but I cannot set a limit to the number of detail records per check.

Being new to all this SQL and Reporting Services my first attempt used a join of the check summary with the detail showing one set of three detail data points with the 10 data points I wanted from the check. The resulting report was hundreds of pages long with one small font check summary followed by an extra small font line for each detail record. The sample data shown below was not too bad but the actual data had 12 or more detials for each check making for a very long report. I wanted to display two colmuns of detail records under each summary record so I could cut the length of the report.

 Not knowing how I could get the Reporting Services to efficiently show the many details under each check I set about to create two sets of the details joined to the check summary as the driving data stream for the report:

 CheckNo, CheckDate, EmpNo, Gross…Code1, Amt1, Code2, Amt2.

 By creating two temporary tables with the detail data, one with an (1,1) identity and a second with a (0,1) identity, a join on the identities could output the two sets of data points. Restricting the join to detail elements of the same check summary records by check number ensures that the output has null for the last detail data point on checks with an odd number of details:

— table1’s detail loads second column from table2

— join on check and index offset puts next detail or null in column2

UPDATE #TEMP1

              SET #TEMP1.PYRLRTYP2 = #TEMP2.PYRLRTYP,

                  #TEMP1.PAYROLCD2 = #TEMP2.PAYROLCD,

                  #TEMP1.UPRTRXAM2 = #TEMP2.UPRTRXAM

FROM   #TEMP1 INNER JOIN #TEMP2 ON

         #TEMP1.MY_ROW_ID = #TEMP2.MY_ROW_ID  AND — identities

         #TEMP1.AUCTRLCD = #TEMP2.AUCTRLCD AND    — audit ctl

         #TEMP1.CHEKNMBR = #TEMP2.CHEKNMBR        — check no

But alas we have every detail record twice. We want to eliminate every other output record for each check summary check number. By setting a flag for the first record of each check:

— set int flag to one for each first detail row per check

— (where the previous row is a different payment number)

UPDATE #TEMP1 SET MY_FLG  =      1

FROM    #TEMP1 INNER JOIN #TEMP2 ON

            #TEMP1.MY_ROW_ID = #TEMP2.MY_ROW_ID + 2 AND

            #TEMP1.PYADNMBR <> #TEMP2.PYADNMBR

— set int flag to one for each first detail row per check

— (where the previous row is a different payment number)

We can then start an count tracking the number of times the unique check number occurs in our many detail records:

— new index starting from 1 at each flagged row

–(counts the many details for each check)

DECLARE @track int

SET @track = 0

UPDATE #TEMP1

    SET @track = CASE WHEN MY_FLG = 0

                        THEN @track + 1 ELSE 1 END,

    MY_ROW_ID2 = @track

 The even count records are deleted:

DELETE #TEMP1

WHERE

MY_ROW_ID2 % 2 = 0

and the temporary table temp1 can be joined to the check summary table to prepare for presentation to the Reporting Services Report.

  A sample page: A sample page       Full Report .pdf

The resulting stored procedure:

Stored Procedure .txt  Stored Procedure pdf    Stored Procedure .doc

Surely if this interests you then you must know a better way to do this, please comment. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: