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.

Tuesday, August 21, 2012

Localized dimensions

The translation management tool is very useful in a multilingual environment. You can translate folder names, category names, report names and report content like headers and labels. But sometimes you need to go a step further, creating localized dimension objects in a universe can be a solution to your problem.

With the introduction of the Information Design Tool (IDT) there is a lot new functionality. In the SQL Expression Editor there is a new function in the System Variables folder called PREFERRED_VIEWING_LOCALE. The BI Launchpad language preference is assigned to this variable at runtime.

You can use this variable to switch between different fields in your database for the different supported languages. Now you can create localized objects and change the database field depending on the users preferences using the PREFERRED_VIEWING_LOCALE variable.

The result when used in a WebI report with viewing locale en_GB will be:

and when the viewing locale is switched to fr_BE:

Monday, August 13, 2012

Show a representative for each group

When creating an Explorer Information Space you can group facets together in folder. If you combine this with the facet display: Show a representative for each group, these folders will act like a placeholder.

Each folder gets his own placeholder in the facet panel, showing the first facet in the group, in this case Opened at year, Closed at year, Category and Status group.

When a year is selected and moves to the filter panel, the placeholder shows the next facet from the folder: Opened at quarter.

When a quarter is selected the same thing happens, the placeholder shows the next object from the folder: Opened at month.

If a selection is made on the last facet in a folder, the placeholder disappears and the space is occupied by the next folder: Closed at.

Saturday, August 11, 2012

Mouse over WebI graphs

When you mouse over WebI graphs you see the corresponding dimension and value. If you want more information in your mouse over you can add more dimensions to your graph and hide them. You will not see them in the graph axes but you will see them in the mouse over.

2 or 3 tier WebI RC?

WebI Rich Client can be running 2 tier or 3 tier mode. 2 Tier means using the database middleware of your computer and 3 tier means using the database middleware on the WebI server.

How can you see if you are using WebI Rich Client in 2 or 3 tier mode? Just hover over the Connected label in the bottom right of your screen.

                    2 tier mode: 

                    3 tier mode:

When you start WebI and click the down arrow in the System box on the logon screen, you see lines starting with a computer icon and a globe icon. A globe icon means you start in 3 tier mode. A computer icon results in 2 tier mode.

You will only see the globe icon if you started WebI RC at least once from BI Launch pad!

Thursday, August 9, 2012

Analysis for Excel add-in not working

When you get the message: "The launcher was not able to connect Analysis Add-In" it means the add-in is disabled.

You can enable the add-in the following way:
- press the Office button
- press the Excel Options button

- Select the Add-Ins options on the left
- Check if Analysis is in the Disabled Application Add-ins section
- Select Disabled Items from the Manage dropdown listbox at the bottom and press Go
- Select Analysis in the Disabled Items popup and press Enable

Thursday, August 2, 2012

Measures with delegated projection

In IDT you can create measures with delegated projection, which means recalculation of the measure in subtotal or totals is done in the database. This setting can be used with averages and count distinct.

Recalculation of the measure also occurs if you create tables or graphs in your report with a different granularity then the granularity used your query.

BusinessObjects WebI handles this by changing your query and add a union for each granularity used in a table or graph.

If you create a simple sample query like this one:

The table will look like this:

 and the query script looks like this:

When you add two new tables to your report you see #TOREFRESH which means the tables have a different granularity then your query.

When you refresh the query, a query with 3 unions will be executed. A column named GID is added to make the link to the correct table in the report.