x Want to participate? Join the group to interact
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 13 replies
  • Subscribers 140 subscribers
  • Views 12674 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
bbarnes

Crystal Reports with SQL Commands

SOLVED
Posted By

Hi all,

I'm a Crystal Reports developer working for a company that uses X3 V6.5 patch 26 and I'm having troubles with a report that uses a SQL command. It doesn't seem that X3 sets the connection details properly on the command object when it runs the report, therefore causing the report to halt asking for connection details.

Does anybody have any clues as to how to fix this?

Cheers,

Ben.

Parents
  • 0

    Hi Ben:

    Could you please provide some more details on what do you mean by "SQL command"

    Standard reports in X3 have parameters in them that allow the report to connect properly when launched within Sage ERP X3.

    For example if you open Tabcountry report, which is a simple report to present the list of countries you would notice that it has certain parameters like below.

     

    Regards,

Reply
  • 0

    Hi Ben:

    Could you please provide some more details on what do you mean by "SQL command"

    Standard reports in X3 have parameters in them that allow the report to connect properly when launched within Sage ERP X3.

    For example if you open Tabcountry report, which is a simple report to present the list of countries you would notice that it has certain parameters like below.

     

    Regards,

Children
  • 0 in reply to Esfahani

    Hi Ali,

    Thanks for the reply. I'm new to X3 having only worked with it for the past 6 months. It's good to be part of an active X3 community.

    Within Crystal Reports, an SQL command is an SQL query sent to the database server when the report is run. The result returned from this query can be used like any other table in the report.

    The problem with these SQL commands is that X3 doesn't seem to treat them *exactly* the same as normal database tables. Without an SQL command, my reports run perfectly, connecting to the correct X3 database at run time. With an SQL command, however, Crystal Reports displays a message box prompting for connection details. Also - entering the correct details here still doesn't actually work.

    I have used these SQL commands successfully in another third-party application, which makes me think that X3 just doesn't set the connection details properly in the code that calls the Crystal Reports SDK. I was hoping there was some way of overcoming this via changing something in X3, or that I am completely wrong and that there is something else really simple that I'm missing.

    Cheers,

    Ben.

  • 0 in reply to bbarnes
    verified answer

    Hi Ben:

    One thing that I would think, maybe a suitable workaround for you, would be to use Views in Sage ERP X3.

    In other words, it's possible to create a View, using a SQL statement, which then it would be available in Crystal report to be used like a table.

    For creating a view please follow below steps.

    1. Open Development, Data and Parameters, Views.
    2. Click New and create a new View , you can look at existing views in the system and how they have been created.
    3. Click Create.
    4. Click Validation.

    Now you should be able to see that view to be added to your crystal reports.

    Lastly I like to mention that there are some Crystal report training related to Sage ERP X3 that are available as Real time learning as part of offering through Sage University here.

    Regards,

     

  • 0 in reply to Esfahani

    Thanks Ali. I was hoping to get an answer that avoided defining extra views but this is a good workaround. The facility to define views in X3 is very handy and I'll use it in future.

    I have checked out a few of the courses on Sage University but I have had technical difficulties that I am trying to sort through with Sage at the moment.

    Thanks for all your help.

    Cheers,

    Ben.

  • 0 in reply to bbarnes

    I've had the same problem embedding SQL commands in Crystal Reports. Am now using the View workaround described here. I would love a better understanding of how X3 processes/handles crystal reports.

  • 0 in reply to TJunction
    I've successfully created a View in Sage X3 that provides a simple listing of Fixed Assets. I've built a crystal report that draws data from the view only. The report runs OK from outside X3 (Preview within Crystal Reports). When I run the report from the Report menu in X3 I get error;

    Job 38 - ERR 504 en provenance du serveur d'édition : Erreur provoquée par le moteur d'impression Crystal .NET . YFASLIST.rpt - - Message 1/3 : Erreur à l'exécution du rapport: D:\Sage\SafeX3\EDTV2\EDT210\srvedit\Data\X3_scg-1442-x3-1_1807\ENG\Report\YFASLIST.rpt - Message 2/3 : Erreur de connexion: Table:YFASLIST - Emplacement x3.PILOT.YFASLIST - Message 3/3 : Failed to open the connection. Failed to open the connection. Details: [Database Vendor Code: 53 ] Failed to open the connection. Details: [Database Vendor Code: 53 ] Failed to open the connection. YFASLIST 7948_5088_{C0CA2DEA-9BC9-4197-807A-D493E37FDC18}.rpt

    Any advice would be appreciated.
  • 0 in reply to Toby McDonald
    Hi Toby,

    This may be caused by a shortcoming in Crystal Reports where the saved connection settings for an individual table or view are different to the saved connection settings for the overall report. When X3 runs the report, it overwrites the report's connection settings with those appropriate to the current X3 folder, but if the table or view's settings are even slightly different, it won't touch these. This may lead to an inability to connect to that table or view, especially if it is in a different database. Crystal Reports Designer is smart enough to allow this, but the Crystal SDK that X3 accesses is not.

    For X3 to run a report properly, each table or view needs to have exactly the same connection settings as the report. To make sure of this, with the report open in Crystal Reports, go to the Database menu and select Set Datasource Location. In here you will see database connections for the main report and underneath, connections for any subreports. Go through each of these connections and make sure they are all pointing to the same database. If not, use the bottom window to select the correct database and click 'Update'.

    Then - and this is the part that makes this problem obscure - go through each table or view and check its connection settings. If anything is different - even the owner (because this will mean a different folder) - select the correct table in the bottom window and click 'Update'.

    Once you've been through all connections and tables/views, save the report and run it again. TJunction - hopefully this gives you something new about how X3 handles Crystal reports.

    Cheers and good luck,
    Ben.
  • 0 in reply to bbarnes

    Really appreciate your response Ben,

    The 'Set Datasource Location' screen is relatively simply in this case because my report is based solely on only one database view (the one I created via X3 UI, 'YFASLIST').

    Everything looks OK to me.

    Screenshots as follows

  • 0 in reply to Toby McDonald
    The Crystal Report connection is using OLEDB to SQL database view and authenticating with SQL server with 'sa' account.

    Ben:
    With regard to your note above, "When X3 runs the report, it overwrites the report's connection settings with those appropriate to the current X3 folder";

    Can you tell me how I determine the connection settings that are appropriate to the current X3 folder. Where do I look for these?

    Thanks
  • 0 in reply to Toby McDonald
    Hi Toby,

    Sorry it's been a while. I've been busy.

    I didn't notice at first that the connection was OLE DB. I just set up a report with an OLE DB connection and had the same issue. It would run fine through Crystal Reports but I got the same connection error when running it through X3.

    Can you configure the report to use an ODBC connection instead? That's what I use exclusively when developing Crystal reports. X3 V6.5 already defines an ODBC connection so that's the logical choice.

    And I'm not sure where you can see those connection settings. I just know that they are set for the current folder when you run a report from that folder.

    Cheers,
    Ben.
  • 0 in reply to bbarnes
    OK thanks. Will give that a go.
  • 0 in reply to bbarnes
    Can you tell me where I find the ODBC connection that X3 already defines? (referred to above) I've just looked on the server hosting SQL- not there.
    I'm looking for a 'File DSN' right?
  • 0 in reply to Toby McDonald
    It should be a User DSN - at least it is on my installation.

    Where it is defined may depend on your version. In V6.5, it should be defined on any workstation that runs the X3 client. These workstations need the ODBC connection defined to be able to run Crystal reports. V7 and onwards I have no experience with yet - we're going through an upgrade to PU9 as we type. It may be the same as V6.5, where the Crystal report is run on the workstation, or it may be run within the browser. In the latter case, you would have to look around one of the servers - probably the application server.

*Community Hub is the new name for Sage City