cancel
Showing results for 
Search instead for 
Did you mean: 

Convert CR8.5 with Union Query to CR2011

Former Member
0 Kudos

I created a report years ago in CR 8.5 on a MySQL database.  I had to modify the SQL query to create a UNION ALL query in the original report and it has been working fine for years.  When now converting to CR 2011, the report is ignoring the second part of the UNION.  It doesn't create an error, just ignores it.

When I open the report in CR 2011 myself, I can see the SQL query window from Database, Show SQL Query has the Reset button active and when I hit it, the query deletes the UNION.

I am not sure how to handle this properly so I am looking for some advice on what to do to get the report working properly again in the new version.

My thoughts so far are that I should convert the report to using a Command instead of table linking.  I am not sure if I can convert it in place or do I need to create a new one with the Command and recreate the whole layout?  I'd like to keep the layout and fields as is but tell the report which fields in the command are the appropriate ones for the old fields.

If my Command idea is the correct path, how do I best add the parameters?  I have played a little and added two that appear to work.  For the one that normally in Crystal is a

{Table.Field} startswith  {?Client}

Should the resulting SQL look like this?

`table`.`field` LIKE '{?Client}%'

Any help is appreciated. TIA rasinc

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Rasinc,

CR 8.5 let you edit the SQL it generated however, CR 2011 doesn't. Anything that you've manually added to the query disappears when you hit the 'Reset' button.

You're headed in the right direction though. Instead of linking tables, here's what you need to do:

1) Copy the SQL from the old report

2) Open the report in CR 2011 > Show SQL > Reset

3) Database Option > Set Datasource Location > From the panel at the bottom go ahead and create a connection to the database > Expand the connection name once it's made > Highlight Add Command > Highlight the Table Name on the Panel on the top > Click Update

4) Paste the SQL query you copied in step 1 > Hit OK

5) The Command Window also has an option on the right to create parameters.

Let me know how this goes.

-Abhilash

Former Member
0 Kudos

Thanks Abhilash.

It looks like I was on the right track.  I had done what you suggested but it only allowed me to map the one table.  I have four tables associated with the current report and I was hoping I would not have to reposition and program each field and formula.

Is there a way to get all tables to map the fields to the command?  Or do I need to just place the appropriate fields and copy any programming over before I remove the original tables from the report?

abhilash_kumar
Active Contributor
0 Kudos

You would need to find the table that has the most fields used on the report and set the command to replace this table.

Once that is done, you would need to remove any references to fields being used from the other tables - either on the report or in formulas and replace them with the fields from the Command. There's no easy way out with multiple tables, unfortunately.

-Abhilash

Former Member
0 Kudos

OK great, thank you for the confirmation.  I was on the right track, I just thought there might have been an easier way to convert the other tables over.  Manually is the answer but I'll use the formatting/sizing shortcuts to help speed it up.

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

And you must create the parameters in the Command Editor in  order for them to be "visible" to the command.  The Command Editor provides access to a very limited set of properties on the parameter.  However, once it's been created in the Command Editor, you can edit it in the Field Explorer in the report to tweak the properties if, for example, you want to make it a dynamic prompt.

-Dell

Former Member
0 Kudos

Thank you Dell,

I believe we have had this part of the discussion before with parameters.  The reasoning is that when the parameter is part of the command, it will feed direct to the database and retrieve records based on the restriction.  If I use a report-level parameter only, the report will return all possible records and filter at the client computer instead of the server.  I have not yet played with the report level tweaking that you are referring to but would like to try to make one a dynamic prompt in this report.