Saturday, December 22, 2012

Tracing Web Intelligence reports

Didn't you always want to be able to trace back from a SQL statement running in the database to the Web Intelligence report that generated it? Well now you can. 

With the introduction of the Information Design Tool (IDT) in BusinessObjects 4 a lot of new features are introduced, one of those are the System Variables that can be used in combination with the @variable function. 

When you are creating an object you can enter the SQL Expression Editor and here under the Functions you can find a new folder called System Variables.

You need 5 of the new System Variables available in IDT to do this:
  • UNVNAME: name of the universe
  • DOCNAME: name of the Web Intelligence document
  • DPNAME: name of the dataprovider
  • DBNAME: name of the database user running the query
  • and the good old BOUSER.

Here are the steps:
  1. Create trace objects with this @variables
  2. Create a business filter and add the trace objects
  3. Make the business filter mandatory and apply on the whole universe

Step 1 Create trace objects
For each System Variable you need to create 1 trace object. I created detailed objects, but this is not really necessary. The trace objects don't reference to a table. The 1 is added for the filtering functionality, I will come back to this in the next point.

2 Create a business filter and add the trace objects
When all trace objects are created you can create 
a business filter and add them to the filter. Because this is a filter there must be some equation, that is why I put the 1 in the trace objects. Now you can set the objects equal to 1, this way it always evaluates to true.

3 Make the business filter mandatory and apply on the whole universe 

The business filter will be hidden automatically. If you hide the trace objects, report developers will not be bothered with the tracing at all.

You have now created a filter that is added to each query created on this universe. All your Web Intelligence reports will now be traceable from the database.

If you apply the business filter to the LOV's those queries are traceable also. And as a bonus all Crystal reports and Dashboards build on this universe will also produce traceable queries.

No comments:

Post a Comment