Monday, December 31, 2012

Hierarchical LOV's

One of the new features in IDT are the hierarchical LOV's, which can replace the cascading prompts and are much easier to create.
A hierarchical LOV really lives up to his name, it shows the list of values in a hierarchy that you, as a universe designer, can create. Here is an example of a hierarchical LOV on a time dimension:

This hierarchy has 5 levels, so it is made out of 5 objects. But it can be used to select any of these 5 levels. The hierachy you see below has only 3 levels, but uses the same LOV.

Hierarchical LOV's are LOV's, not prompts. There is no prompt text we can use to communicate to the user. The text that is shown to the user with this hierarchy are the object names, so we must create objects with understandable names.

Now lets create this hierarchical LOV. First I will create all 5 objects and the LOV then I will change the object names for correct communication to the user.

Step 1 Create the 5 special LOV objects

I duplicated the original objects for two reasons. I am going to change the LOV and I want the objects with the default LOV to be available also. Secondly I have to change the name of the objects used in the hierarchical LOV.

Step 2 Create the hierarchy LOV

Go to the Parameters and List of values subview. Click the arrow in the List of values pane and create a new LOV based on business layer objects. 

Name it and be sure to check the option "List of values based on a custom hierarchy". Then add the 5 objects created in step 1 and put them in the correct order, the most dense on top and the most detailed at the bottom.

Step 3 Modify the 5 LOV objects

Select the "Lost Login Moment H-LOV" object and goto the Advanced tab. Check the Associate List of values box and press the ...-button.

Select the "Last Login Moment (Hierarchy)" LOV created in step 2. Then choose the column, that corresponds to this object, "Last Login Moment H-LOV".

You can do the same for the other 4 objects date, month, quarter and year (although the year object is not used).

Step 4 Change the names of the 5 LOV objects for communication

When you use the objects like this in WebI you will get the following LOV screen:

For me this is not a very clear communication to the user. You can see that two object names are used here to communicate. So I change these with more meaningful names: 

Then the LOV properties will look like this:

And when used in WebI you now communicate this:

When an incorrect level of a hierarchy is selected in WebI there is an error when the query is executed. But before that a small red triangle will be shown in the top left corner of the selection box.

When we create Parameters for these LOVs they also can be used in prompts. This is probably why you created these LOVs in the first place. Parameters can be created in the same subview as the LOVs were created. Parameters use the same mechanism to associate LOVs as normal objects.

When you want to use these in WebI you first create a normal prompt in the Query Panel - Query Filters pane, then you click the prompt button to edit the prompt.

And in the Parameters Properties you select the radio button Use Universe Parameters and then the parameters for the object can be selected.

Merge tables in IDT

Another new feature of IDT, merging 2 table together, creating a derived table. Of course this is something that can be done by manually. But when there are already joins, objects and filter defined it requires some work. Using the merge tables functionality is much easier, because it takes care of all these changes for you.

Lets say you have these to tables in your universe and want to merge them:

You select both of them, right click and select Merge...

Name the new table...

You will receive a message asking you if you want to delete the original table...

After the merge you only see this 1 table:

When you look at the sql, the tables are joined using the join that existed between the 2 original tables.

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.

Thursday, December 20, 2012

Calculated columns: Time Columns

One of the new features in IDT is the ability to create calculated columns on tables in the Data Foundation. If you select a table and right click you get the option to insert a calculated column:

But if you select a date field in the table and right click you see a slightly different option:

IDT can generate these calculated time columns for you out of the box.