How to tell which subreport an SQL Expression is in 1

[CrystalLion]

I am trying to edit a report that has nine subreports. I am also upgrading the report from version 8.5 to version XI. The SQL expression editor is coming up as the result of an error when trying to run the report, the error being "column not found/specified". The expression is basically four substrings which divide a 400 character text field into 100 character substrings. The editor does not indicate where the expression is used. We do not see any SQL expressions in either the main report or any other subreports, so we cannot edit the expression nor replace it without poking through all 10 reports looking for where it's used and not finding (yet?).

Is there a "where is formula used" or where is "SQL expression used" or a way to serach everything for a string of characters.

Any help would be fabulous.

 

[lbass]

Did you add a command to the report? Doing this makes the SQL expression editor disappear from the report. Basically, you can't use both SQL expressions and commands in the same report (altho sometimes old reports will work). Try removing the command and see if you can then see the SQL expressions.

-LB

 

[CrystalLion]

Thanks LBass. To the best of our knowledge, we have not added any commands. However, this report is so full of formulas, subreports, etc., it's getting really ugly for us to modify as a thrid-party. Is it possible that the client did so prior to us trying to convert from 8.5, and if so, could that be the source of the problem.

Also, do you know of any way to search for formulas or strings in a report/subreport.

Thanks for you speedy reply.

LL

 

[lbass]

Are you using more than one datasource then? That would have the same impact. Commands were not available before 9.0.

When the error pops up, you have your chance to edit the SQL expression. You could simply erase the content--it sounds like the expression might be referencing a field that is not in a table in the report.

You might also want to change the page orientation to landscape/legal to see if there are fields in the margins.

You can also export to "Report Definition" to see where fields are located--in case the expression is merely displayed, not used in a formula.

-LB

 

[CrystalLion]

Not more than one datasource. Did use the export Report Definition. thanks. that was quite helpful. We actually found the location of the culprit sql statment and subsequent formula and fixed it.

Thanks for your help.