We are still on XI 3.1 We are transitioning all of our Deski Reports to WebI in anticipation of the 4.1 upgrade which we’ll be applying in about a year.
One thing we have noticed with our WebI reports is, everytime we modify our Universes (changing an object definition, or a derived table or even a join), the SQL of the WebI reports are not automatically updated, but the SQL for the Deski reports are.
The only way we were able to get the SQL to re-generate was to add and remove an object in the query panel to force a regeneration. We have hundreds of standard reports, so we really can’t afford to have to touch all of them every time we modify a universe.
I’m hoping someone has seen this issue and has a good solution. The more reports we convert to Webi, the more of an issue this will be.
Just a thought, but have you published your changes to the repository? Webi needs to see the new universe on the server.
Also, I’ve found that sometimes you need to log in again to see universe changes, and that object name chnages are not picke up in an exisiting Webi report unless you edit the query.
Indeed the SQL are not automatically updated when you are looking for it in the query panel. But if you try to refresh the report, the good SQL is supposed to be used.
I presume that you are using Webi Rich Client; if so, yes, it is a real pain to get the SQL updated to reflect Universe changes, especially if you have only made these locally and not exported to the server.
I haven’t had the time to go through carefully structured test routines but refreshing the Universes in WRC does not seem to work, and removing and adding Objects sometimes seems to work. In other cases you have to close and re-open WRC.
The bottom line is that it’s very frustrating, and, as usual, nowhere as slick as Deski.
Thanks for the replies. We are still facing this issue. Yes, we are using Webi Rich Client, since it is the closest to Deski, which is what we are familiar with. Has anyone found a workaround? Our workflows depend on universe changes propogating to the SQL in the webi reports, and we have close to 1,000 ad-hoc users who will have their own webi reports, so we can’t realistically reach out to all of them each and every time we touch a Universe.
SQL should be regenerated before you run, if the universe is detected as being changed. If you want to confirm this, just break an object (enter some invalid SQL that references a non-existing column) and try to run a query.
What I do to “force” the SQL to regenerate while I’m in the query panel is simply to click “Use Custom SQL” and then revert back. That will always regenerate the SQL based on new universe changes while I am watching. But you’re supposed to trust that it will happen without you looking at it as well.
I usually do the same thing Dave mentioned, of clicking the Use Custom SQL, and then reverting back.
I was told years ago that if you change the parameter AUTO_UPDATE_QUERY, in the universe, to Yes, then it will force the reports to regenerate their SQL on every use. I’ve never tested this because, quite frankly, I didn’t believe it. But you can feel free to test it.
I would think the AUTO_UPDATE thingie would be more of a burden, as it would force every report to update the SQL every time, which is not necessary. Is that how it’s supposed to work?
When making Universe changes, I often add a dummy Object so that I can clearly see that the latest version has been picked up. However, in WRC even though the Universe shows as having been changed, the SQL still often is not updated. I say ‘often’, because the behaviour does not appear consistent.
Sometimes, JVann’s suggested technique of removing and adding an Object forces the regeneration of the SQL, but I’ve found that this doesn’t always work. On some occasions, I have to create a new query on the Universe, and this then forces the update on the original query. This is a real pain!
What happens to Webi reports that are residing on the server, I don’t have the time to check the behaviour at present?
Are you seriously suggesting that if I have a 1,000 Webi reports out there, that they all have to be opened individually to force the SQL change?
We have the same problem. We switched our datawarehouse database from SybaseIQ tot SQL Server (sometimes the sql differs). But sometimes the sql is not correctly generated. The parameter AUTO… has no effect…
No, of course not. The SQL generating engine is supposed to check to see if the universe revision number is higher now than the last time it ran, and regenerate the SQL prior to running. What we’re talking about in this topic is seeing the “new” SQL in the query panel before running.
I have faced this issue several times.
What i usually do is delete the existing local copy of the univers and reimport.
That works perfectly fine for me.
Yes. Most of the time. The confusion comes from the designer side (report designer not universe designer) where it doesn’t seem to pick up the new changes. If you run the report and then go view the SQL it will but report developers like to see the “expected” SQL in the query panel before they run. As I mentioned, if I click “custom” and then go back to generated SQL it will force the regeneration to occur before I run the report.
We have had a very few cases - like maybe a dozen in thousands of reports over the years - where the SQL would not regenerate. In each case it was solved by manually regenerating (using the above process) or realizing that the report was not pointing to the universe we thought it was…
Back in the 6.5 days we used to have to manually delete the universe from the server to get server-based reports to update. But we haven’t had to do that in years.
But in Rich Client (in XI 3.5 it is there) you can UPDATE your LOCAL VERSION(s) of the universe(s) : menu Tools - Universes.
For the USERS of your report, the people who will view them through WEBI, there’s no problem: webi should detect the latest version f the universe ON THE SERVER, and use that.
In Rich Client, of course, you are supposed to work against a local copy of the universe, and you have to keep that copy “up to date”
But I’d be VERY surprised the learn that an OLD version of the universe (or of the query) could still be used, when the LOCAL UNIVERSE has been updated.
So my GUESS :
once you update your local universes, reports will automatically follow that change at the moment you open them.
If you merely VIEW them, not (re)running the quer, you don’t mind having old stuff on your screen. If you want “current data”, you refrsh, and the query gets updated because it will use the UPDATED LOCAL Universe.
We had the same problem in BO 6.5 , where reports would use the -outdated- previous version f the universe, because the LOCAL universe was used by deski (back then: Business Objects)
When we saw that happen, esp on a server that created and sent reports when triggered by an application. (app sent a trigger, server acts as a standard client asking for a report, using parameters included in the trigger)
We made sure to update that servers’ LOCAL universe immediately after promotion of the base universe in the 6.5 system.
( Today, this server uses “deski XI 3.5” … still working on a workaround for when deski will die. )
(BOB member since 2003-06-04)
(BOB member since 2013-07-25)
(BOB member since 2002-09-13)
(BOB member since 2002-08-08)
(BOB member since 2006-09-28)
(BOB member since 2002-06-06)
(BOB member since 2009-05-26)
(BOB member since 2003-05-26)
(BOB member since 2009-05-26)
The confusion comes from the designer side (report designer not universe designer) where it doesn’t seem to pick up the new changes. If you run the report and then go view the SQL it will but report developers like to see the “expected” SQL in the query panel before they run. As I mentioned, if I click “custom” and then go back to generated SQL it will force the regeneration to occur before I run the report.
(BOB member since 2007-06-18)