question

robertosborn40 avatar image
robertosborn40 asked

Using Cross-Tab or Pivot in an SP for crystal reports

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.
pivotstored procedurescross-tabcrystal-report
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's hard to tell from this how you want your data presented and what Crystal is doing wrong. Can you mock it up in excel and post a screen shot?
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.