Stored Procedure Questions

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
elimalone19
Posts: 23
Joined: Mon Jan 31, 2022 2:26 pm

Stored Procedure Questions

Post by elimalone19 »

Hi,

I'm trying to create a custom Crystal report based on one of the canned reports in Select (Revenue by SA/AM - New). The report uses a stored procedure (spc.csp_700180_OrdersByMarketingRepSAAM;1) but doesn’t have a field that I need (isCommercial). I tried joining to the OrderInfoView table, but this seems to slow things down a lot. Crystal also does not recommend joining to a stored procedure. What is the best way to accomplish what I need? Do I need to create a brand new stored procedure with the join built in?

I'm new to Crystal Reports and any help is appreciated.
brandon.ritchie
Posts: 7
Joined: Fri Jan 06, 2017 9:50 am
Contact:

Re: Stored Procedure Questions

Post by brandon.ritchie »

The best option is to create a new version of this stored procedure to add the new column and point your report to the new version. Due to installing any bundle update for custom reports would overwrite/revert any changes you make.

To add the IsCommercial column, the easiest way would be to add an "IsCommercial" field to the #Orders & #Results temp table. Then capture that on the INSERT statements. The IsCommercial field is in the pfm.[Order] table which is already in the inserts. Then add the field to the final results so you can use it in your new report. See attached images for examples.
Attachments
Capture field in temp table inserts and return in final results
Capture field in temp table inserts and return in final results
Example-UpdateInsert-Return.jpg (575.31 KiB) Viewed 1710 times
Add new field to temp tables
Add new field to temp tables
Example-AddToTempTable.jpg (254.04 KiB) Viewed 1710 times
Brandon Ritchie
Reports Technical Team Lead
SoftPro
Post Reply