x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 10 replies
  • Subscribers 142 subscribers
  • Views 6789 views
  • Users 0 members are here
Browse Forums
Announcements
235
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
411
replies
Sage Intelligence Reporting
121
topics
322
replies
Ranko

Can stored procedure be used in X3 to run Crystal reports

Posted By

Hi,

I am new to X3, but not to ERP systems.

Have lots of experience with SQL and Crystal reports.

This is the issue I am having, I have created a custom report for a client, and have created a stored procedure to run the report.

The report is calculating running balances for any nominated period of time.

The standard report would take absolutely forever to return such a result set, as it retrieves complete data set and then does the filtering, where the stored proc gets only data based upon parameters then does all the calculations and it is instant.

None of my old customers would tolerate waiting for a ridiculous amount of time to have the report just executed. 

While this all works perfectly fine in Crystal and SQL Management studio, I am struggling to pass parameter values from within the X3 to the Crystal report linked in X3. 

Are there any tricks here, has anyone achieve it and how? 

Thanks for your help. 

Parents
  • 0

    There are two ways to add the stored procedure to Crystal reports. One is to add the script and the script could call the SQL stored procedure. Another is to add the stored procedure to the Crystal reports directly. The only issue for adding the SQL stored procedure is to create the same one for each schema (folder name), such as PROD.sp_xxxx(para1, para2), or PILOT.sp_xxxx(para1, para2). When adding the SQL stored procedure to the Crystal report directly, the parameter name could be changed after adding. But try to determine the date type or datetime type. At the end of stored procedure, you may use "Select * from [table]/[view]" to use the return data. Regards 

Reply
  • 0

    There are two ways to add the stored procedure to Crystal reports. One is to add the script and the script could call the SQL stored procedure. Another is to add the stored procedure to the Crystal reports directly. The only issue for adding the SQL stored procedure is to create the same one for each schema (folder name), such as PROD.sp_xxxx(para1, para2), or PILOT.sp_xxxx(para1, para2). When adding the SQL stored procedure to the Crystal report directly, the parameter name could be changed after adding. But try to determine the date type or datetime type. At the end of stored procedure, you may use "Select * from [table]/[view]" to use the return data. Regards 

Children
No Data

*Community Hub is the new name for Sage City