How to edit SQL of existing report?

[Former Member]

Hey all, I am very new to Crystal reports. We already have a report that's made and I created a copy of it to try and make some changes. I am able to view the SQL but I am not able to edit it. Also, I am not able to click on the 'edit report object' selection from the edit menu. See screenshot below.

Any help is appreciated.

https://ibb.co/fRRoTc

[abhilash_kumar]

Hi Sunny,

Go to the "Field Explorer" and expand "Parameters". You should see the company parameter (or whatever it was named by the developer); right-click the parameter and select Edit.

Scroll down the list of options and make sure "Allow multiple values" is set to "True".

You may also have to insert a group on the Company Field if it isn't already there. You can then go to the Group Footer's paging options and set it to "New Page After" so that each Company's data prints on its own page.

-Abhilash

[Former Member]

Thank you both for the responses. I am not quite sure how the report was created since it was created by someone that is no longer working for us. The ERP that we use is called Vista by Viewpoint. What I am trying to accomplish is not adding/removing fields. Here is what my goal is--

When we run a report, it makes us select what 'Company (data)' to use. We have to run separate reports for Company 1, Company 2, and so on. I want to edit the report to be able to show company 1 and 2 all on 1 report. Does that help?

[abhilash_kumar]

Hi Sunny,

What is the datasource of the report? If it was created off of tables/views, you're not able to "edit" the SQL.

If you wish to use a Custom SQL as the report's datasource, go to the Database Menu > Set Datasource Location > Create a new source in the pane below > Highlight "Add Command" option in the new connection > Highlight the table in the existing connection on the top > click update. You should see a Command Window that let's you write custom SQL.

This works well when you have a single table. If the report contains multiple tables, it gets difficult and I would suggest creating the report from scratch using the custom SQL.

-Abhilash

[DellSC]

A lot of this is going to depend on how the report was originally created.

If you don't see the "Field Explorer" on your screen that has a tree containing Database Fields, Formula Fields, etc., go to the View menu and turn on Field Explorer. Open the list of Database Fields and see if what you need is there.

If the fields you need are not there, go the the Database menu and open the "Database Expert". You will see one of a couple of things here:

1. A list of tables and/or views that the report uses.

2. A stored procedure that the report uses.

3. A "Command" that the report uses.

You can determine which it is by right-clicking on the name on the right. If "Edit Command" appears, you have a command. If it doesn't, you have either a stored procedure or a table or view.

If the report is using tables or views, you can add the table that includes the data you want and then "link" to it on the Linking tab of the Database Expert.

If the report is using a stored procedure or a command, DO NOT add an additional table and link it! That will significantly slow down your report. Instead, if it's a stored procedure, you'll need to update that to include the data you want. If it's a command, edit the command and update it to include the data you want - I suggest testing the result in something like SSMS, Toad, etc. - whatever you have that appropriate for your database - to make sure it runs correctly because Crystal won't let you save it until the SQL is correct.

-Dell