x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 10 replies
  • Subscribers 142 subscribers
  • Views 6794 views
  • Users 0 members are here
Browse Forums
Announcements
237
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
416
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. 

  • 0
    I'm trying to do the same thing. Are you getting the same error?
    Job 7038 - ERR 504 en provenance du serveur d'édition
    : Erreur provoquée par le moteur d'impression Crystal .NET
    . <Report Name>.rpt - - Message
    1/3 : Erreur à l'exécution du rapport: D:\Sage\SafeX3\EDTV2\EDT210\srvedit\Data\PROD_<servername>_1807\ENG\Report\<Report Name>.rpt - Message
    2/3 : Erreur de connexion: Table:<SP_Name>;1 - Emplacement x3.PROD.<SP_Name>;1 - Message
    3/3 : Failed to open the connection. Details: [Database Vendor Code: 17 ]
    Failed to open the connection. <Report name> 83508_80284_{04154FA1-7129-4D10-A0CB-89B0329049AD}.rpt Details: [Database Vendor Code: 17 ]
  • 0 in reply to Toby McDonald
    hi
    did you try SQL command? you can create one in the Database expert.
    if you can write what you need without SQL syntax like IF and loops, only pure SQL select with params then you can do it, you just need to set the param from the report to the SQL command. maybe you can call your SP inside the SQL command in crystal?
  • 0 in reply to Barak
    Barak: If you view the SQL Command for a report that has a Stored Procedure as a datasource the code is already there; {CALL "<DatabaseName>"."<SchemaName"."<StoredProcedureName"}.

    I resolved my issue my moving the stored procedure to the same schema as my X3 folder. X3 was then able to view and execute SP when running my report.

    The answer to the question 'Can stored procedure be used in X3 to run Crystal reports' is therefore 'Yes'. Regarding the subsequent question of whether it's possible to pass parameters from X3 to Crystal. Yes, I'm successfully doing that. Without knowing the specific issues/errors you're dealing with, I would suggest firstly checking you have consistent number of parameters, datatype of parameters, and name of parameters in Crystal and X3.
  • 0 in reply to Toby McDonald
    My procedure is definitely within the same schema.

    This is the error message, I think X3 is expecting a table and does not like the proq

    ERR 504 from the edit server
    : Error Caused by the Crystal .NET Print Engine
    . ZMYSPTEST.rpt - - Message
    1/3: Report Error: E: \ Sage \ SafeX3 \ EDTV2 \ X3PU9PRINT \ srvedit \ Data \ SEED_win-mvogauf4i0e_50009 \ ALL \ Report \ ZMYSPTEST.rpt - Message
    2/3: Connection error: Table: ZTBReportingProcedure; 1 - Location x3pu9.SEED.ZTBReportingProcedure; 1 - Message
    3/3: The table 'ZTBReportingProcedure; 1' could not be found.
    Error in File ZMYSPTEST 5352_2076_ {E6716D80-E421-4188-A801-2FBEF8E20460} .rpt: The table could not be found.
  • 0 in reply to Ranko
    I'm almost certain this is a permission issue then. You'll get 'table cannot be found errors' if you've got an access problem. I would suggest comparing the permissions for the SP with the permissions of an existing View or table that you can successfully query. (In SSMS, RClick your Stored Procedure->Properties, click Permissions in LeftList)
  • 0 in reply to Toby McDonald
    If that is the case then I am laughing...When creating a stored proq, I normally grant permissions to relevant users, have to admit have not done it this time....let's see how that works now...
  • 0 in reply to Toby McDonald

    Hi! what version of X3 are you using? My parameters are failing to pass to the report.

    @ddate is the name in the report parameters

    @ddate is the parameter code in Crystal Reports

    @ddate is the parameter code in SQL Stored procedure

    But it still fails and says this parameter is missing.

    Appreciate any feedback you may be able to give.

    Regards

  • 0

    The answer to this is yes.  I am running several reports using a stored procedure.  You need to put the stored procedure in the schema you are running it.  Make sure your parameters in the report setup have an @ symbol in front of them.  They need to match the SQL parameter list.

  • 0

    Even though possible, in my opinion, it is not recommended. 

    The reason is that, with every upgrade or schema (folder) change, your report will stop functioning for that SP. Unless you can pass the schema name through a parameter. But you will still need to script that SP to the new schema manually.

    I usually do my calculations using a view created within X3 (leave out schema name in your query), and then slice and formulate your data from the view in the Crystal report.

    This ensures that with every folder copy / upgrade, your report will still work, as X3 will automatically run it with the relevant schema with which you are logged in with.

    I hope you find this helpful.

    Regards

  • 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 

*Community Hub is the new name for Sage City