BusinessObjects 4.1.6
Data on Oracle 8i
Repo (Security/Universe Domain) on Oracle 8.0.4
According to a recent post, by disabling the command “Do not always regenerate SQL” in Supervisor for a user (or group), you can ensure that SQL is always regenerated no matter how the query is run. I did some testing today that would seem to refute this, but maybe there’s more to the story and I just don’t have the whole picture.
Here’s the scenario. The “Do not always regenerate SQL” command has been disabled for my user account. I opened a report that runs successfully in a given universe. The report has two data providers. I changed the universe to a new universe for both data providers. I clicked the Refresh icon. I got an error message “ORA-00904 Invalid column name”, then a message that one of the data providers was not successfully refreshed. I opened that data provider (Query Panel) and clicked Run. The query ran and returned data.
So, the question is, if SQL is always being regenerated for my user account, why would the query not run when I click the Refresh icon, but then run successfully when I click Run from the Query Panel. Am I missing something?
The option to “Do not always regenerate SQL” pertains to the ability to modify the SQL of a report in the Edit Data Provider window. You can free hand the SQL and check the box and it will return results based on your modified SQL, not necessarily the objects chosen in the GUI. The option is Supervisor allows this to be active. As you have found out it does not impact the refresh action.
I don’t think this is correct. There is a command in Supervisor for “Edit Query SQL”. I haven’t tested this, but it seems like this should control whether or not a user can modify the SQL. However, according to an earlier post by Simon Miller, he disabled the Edit Query SQL command and the “Do not generate SQL before running” checkbox still appears in the SQL Viewer and could still be checked. Anyway, that’s another discussion. I’m not concerned at the moment with editing the SQL, I’m trying to figure out what causes BusinessObjects to regenerate the SQL. There are other things that can change in a query or a universe (besides editing the SQL) that might require the SQL to be regenerated to make the changes take effect.
On the same post from Simon, he indicated he was having inconsistent results with the “Do not always regenerate SQL” command also. My tests confirms what he found – kind of. If you have multiple data providers, only the first data provider gets the SQL regenerated the first time you refresh. In my case I changed the universe on all three data providers. If I display the universe name for each data provider in the report and click the Refresh icon, only the first data provider has been switched to the new universe. I can refresh again and the second one gets changed, and if I refresh a third time, finally all three have been switched. Of course, you can open the Query Panel for each data provider and click Run and the SQL will be regenerated, but the point was that if this option is disabled, the refresh icon is supposed to work the same way (or so I thought) and it doesn’t! Dave Rathbun posted about this same subject and said “If you reset the setting …, then the SQL is regenerated every time. It doesn’t matter if you go to the query panel or simply use the Refresh button”. This might be the case if you have only one data provider, but isn’t the case when there’s more than one.
I’ve logged this with Tech Support – if they provide an explanation I’ll post it.