Report Design: Re-open query to edit

Re-open query to edit

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10347
Printed Date: 03 May 2024 at 11:41pm

Topic: Re-open query to edit

Posted By: rmbennett
Subject: Re-open query to edit
Date Posted: 24 Jun 2010 at 4:40pm

Am new to CR V10 and can't figure out how to re-open my report SQL query to modify something (add a parameter). Tried to find in books, on-line, etc. Would appreciate help with this simple question. Thanks.

Replies:

Posted By: rahulwalawalkar
Date Posted: 26 Jun 2010 at 7:46am

You can't modify the report SQL Query in Crystal unless you are using command object, the Sql Query is created on the number of fields selected and if any record selection forumla used in the report ...

*Open the report and do the following

*You can veiw the sql query, Go to Database in Main Menu the click Show Sql Query.

*To Add Parameters Go to View in Main Menu and click Field Explorer once you do that on the right you can view the Field Explorer Window with all the different option such as Database Fields,Formula Fields... etc

*Select Parameter Fields right click and select New to create New parameter.

Cheers

Rahul

Posted By: Emir_W
Date Posted: 26 Jun 2010 at 9:17pm

additional info:

if your report using a Stored Procedure (SP) or View with parameter to collect some data then you need to modify the SP/View it self.

After you finish then you need to verify your report

here is the steps:

*To Check if your report using SP or View

- click on Database menu

- click on 'Set Datasource Location...'

here you'll see your ODBC name and the SP (or View)

- expand your SP/View, to see the real SP/View name

till here you can open and add parameters on your SP

*To verify your report

- click on menu 'Database'

- click on 'Verify Database'

in this stage CR will sync with the SP/View (if there are some changes on fields / params, etc.)

hope it help.

-------------
Emir W

Posted By: rmbennett
Date Posted: 27 Jun 2010 at 4:20pm

Thank you Emir and Rahul,

When my consultant and I created this report, we took a complete MS Access SQL passthrough query that I had written for an Access Report and dropped it into a dialog. Then set a couple of parameters. We used an ODBC connection to the SQL server. This defined the data set for the report. I'm really sorry I don't know this better, but now I want to get back to that SQL query that I used, and add another parameter.

Emir's advice seemed to take me to stored procedures in the SQL database, but I'm looking for the SQL query that I wrote and put into Crystal. Rahul: are you assuming my report design created the SQL script. In my case I used a script I had previously designed. Now I want to further edit it. I hope this makes sense. Thank you.

Posted By: Emir_W
Date Posted: 27 Jun 2010 at 9:33pm

for editing the SQL from CR all you can do only:

1. add/remove table(s)

2. change the link between table(s)

these can be done from 'Database Expert'

and, change the data restrictions (where clause) from Report-Selection formula.

you can also add a parameter from CR by:

1. right click 'Parameter Fields' in Field Explorer

2. click 'New' and put the Parameter name, Type of the parameter, and other options on it.

in case you using a stored procedure then you can add parameter(s) from your stored procedure as my previous reply.

hope it help.

-------------
Emir W