Custom SQL in Webi reports reverting to gerenated SQL when exporting univ

[Former Member]

Hi all -

I've searched the forum and didn't find any posts of the sorts regarding my issue. I was wondering if anyone has experienced the following behavior in WebI XI R2.

We have a few reports that run on a scheduled daily basis. The report writer created these reports with custom SQL to use the current date in the select criteria. But when we make changes to the specifc universe that these reports use and export that universe from Designer or import it from QA to Production via Import Wizard, these reports all revert to the initial generated SQL. The report writer need to go into each one of these reports to set them back to use custom SQL. I should also add that the generated SQL of these reports are fairly complicated.

We have about 20 of these report and right now we are on a monthly deploy cycle. So it's becoming an issue to get these reports set back to use custom SQL every month.

Has anyone come across an issue like this? If so, was there a resolution?

Thanks in advance for any advice on this.

[Former Member]

Hi Carlo,

I am not familiar with the issue, however, is the option set in the report "do not generate SQL" off? This would impose the custom SQL and not regenerate the SQL at runtime.

Hope this helps

Jacques

[Former Member]

Thanks for the reply Jacques. But we are using WEBI for these reports. And as far as I know WEBI doesn't have that option like DESKI. WEBI only has 2 options "Use the SQL generated by your query" or "Use custom SQL". It's in these choices where the report gets reverted back to using the generated SQL.

Any other thoughts on this?

[Former Member]

Hi Carlo,

For custom SQL you need to select the custom SQL option and edit the query. I am sure that once this is set it is not supposed to revert back to the generated SQL.

[WMuellner]

Carlos,

first of all, regardless of your necessity to use custom SQL, I'd suggest to find a "universe" solution for your problem.

You mentioned you need the "actual date" somewhere in your sql, why not define a universe object which gives you the "actual date" and then use this object in your query?

just an idea... and only a workaround

Walter

[Former Member]

Walter- Thanks. Doing the universe solution work around is my last resort which will probably be the case.

It does seem like this is some sort of bug in WEBI because it only happens in certain cases - complex custom sql. We have a other reports with custom sql that have simple select SQL that don't show this behavior when the universe is exported. So I was looking to see if anyone else has encountered this and knew if there was a patch or something that would address this issue.