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.
Stored Procedure Questions
Moderator: Lisa Ennis
-
- Posts: 7
- Joined: Fri Jan 06, 2017 9:50 am
- Contact:
Re: Stored Procedure Questions
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.
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
- Example-UpdateInsert-Return.jpg (575.31 KiB) Viewed 2872 times
-
- Add new field to temp tables
- Example-AddToTempTable.jpg (254.04 KiB) Viewed 2872 times
Brandon Ritchie
Reports Technical Team Lead
SoftPro
Reports Technical Team Lead
SoftPro