Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MsLady
Starting Member
5 Posts |
 Posted - 2009-12-30 : 14:21:44
|
| I need to create a report using Crystal Reports with a stored procedure as the data source. So far, not problem; however this report requires a column total based on the row's data-problem, for me as I do not fully understand how to use Pivot and Cross-Tab. In order to create the report the creation of two stored procedures, stprocAB and stprocCD, were necessary. One stored procedure has 31 rows and the other has 38 rows; they both are accurate and both sets of data needs to be presented in the report in the format:Desired OutputDescription Locale Item CostAB CostCD TotalBuilding Local 123L 400 400Building Regional 123R 100 100 200 Computer Local 456L 2000 2000Computer Regional 456R 2500 2500 5000Transportation Local 789L 11250 750 12000Transportation Regional 789R 750 750Furniture Local 951L 350 250 600Furniture Regional 951R 350 250 600Truck Local 357L Truck Regional 357R 5000 5000 10000To make this scenario more interesting for me, the data for column Locale is not in the database, but is based on the last value of them Item column I will need to write out Local or Regional depending on "L" or "R". Is this doable, cleanly, using SQL? If not, how would I export the two stored procedures into Excel so that I could then create a Pivot table to generate a report.Thank you. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-01 : 17:00:44
|
selectDescription, case when right(rtrim(Item),1) = 'L' then 'Local' when right(rtrim(Item),1) = 'R' then 'Regional' else 'undefined'end as Locale, Item, CostAB, CostCD, Totalfrom ...where ...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
|
|
Direction and Clarification