Cross-tab or pivot in an sp for crystal reports

Transact-SQL
1

I have a stored procedure for a report that feeds a Crystal Report. We are moving from binary file based reporting to using an sp then displaying in Crystal. The Cross-tab function in Crystal is not giving me the desired results or formatting. I want to use either the Cross-tab or Pivot function. The issue I am having is that the voids have the possibility of being different for each store location. They can not be hard coded.

Here is the stored procedure and its results.

The @reporttemp table is used to get total sales. then selected in the query the data is being pulled from through Crystal.

DECLARE @ReportTemp Table (Sales smallmoney, iEmployeeID int)
 Insert Into @ReportTemp
 select Sum(js.iAmount) 'Sales', j.iEmployeeID
 from journalsales js
     inner join Journal j on js.iJournalID = j.iJournalID
 where js.iMerchandiseID > 0
 and js.iAmount>0 
 and j.iStatus_PullBack = 0
 AND j.bClosed = 1
 group by j.iEmployeeID
 
 Select v.sName, 
     Sum(js.iAmount) 'Voids',
     Count(v.Sname) 'VoidCount', 
     rt.Sales, 
     b.iCostCenter,
     e.sLastName,
     e.sFirstName,
     e.iEmployeeID
 From Void v
     inner join JournalSales js on v.iVoidID = js.iVoidID
     inner join Journal j on js.iJournalID = j.iJournalID
     inner join Employee e on j.iEmployeeID = e.iEmployeeID
     inner join @ReportTemp rt on e.iEmployeeID = rt.iEmployeeID, 
     BusinessInfo b
 Where j.iStatus_PullBack = 0
 and j.bClosed = 1
 and v.bActive = 1
 and js.iVoidID > 0
 and js.iAmount > 0
 GROUP BY v.sName, e.sLastName, e.sFirstName, rt.Sales, b.iCostCenter, e.iEmployeeID

--result set--

 (11 row(s) affected)
 sName      Voids                 VoidCount   Sales                 iCostCenter sLastName            sFirstName           iEmployeeID
 ---------- --------------------- ----------- --------------------- ----------- -------------------- -------------------- -----------
 CUST CHG   12.99                 1           98.19                 5108        MULLENAX             BRYCE                1149
 CUST CHG   7.00                  1           353.60                5108        STEWARD              MAX                  7189
 CUST CHG   14.86                 4           529.54                5108        NYBERG               JOSHUA               1020
 CUST CHG   7.00                  1           566.35                5108        CARTER               TIFFANY              1032
 CUST CHG   14.68                 3           595.89                5108        HALL                 HUSTON               1150
 CUST CHG   5.99                  1           835.04                5108        WILLIAMS             JESSICA              1012
 CUST CHG   7.00                  1           1195.16               5108        FIELDS               TOMMY                1057
 NOT RECVD  22.97                 3           213.63                5108        SANTINO              AMAN                 1016
 TOO MANY   2.99                  1           290.85                5108        MAROSTE              KRYSTAL              1151
 WRONG ITEM 11.00                 1           116.15                5108        SINGLETERRY          LETA                 1043
 WRONG ITEM 2.39                  1           290.85                5108        MAROSTE              KRYSTAL              1151
 WRONG ITEM 8.99                  1           443.07                5108        TOMBAUGH             GEORGE               1042
 WRONG ITEM 6.99                  1           595.89                5108        HALL                 HUSTON               1150
 
 (13 row(s) affected)
2

It is not clear to me what the output you are looking for is. Assuming you want to do a PIVOT, the PIVOT operator in T-SQL can be used. However, that requires a'priori knowledge of the columns you want to get in the pivoted output. If you don't know those, you will need to use dynamic pivot using dynamic SQL. See here for examples.

3

Employee becomes the first column and the void reasons become my column headers. Then fill in the dollar and count values associated with each employee and the void reasons. The 'iCostCenter' column will no longer be needed as that is the only way I could get it to group right in Crystal Report. I was using a cross-tab in crystal but couldn't get my totals right at the bottom of the cross-tabs. That is why I was looking for an alternative solution and Crosstab or Pivot in the SQL seemed to be the only other options.