question

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) I need the void reasons as my columns and employee as my rows.