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)