Toggle the Display of Text and Key in Webi

Toggle the Display of Text and Key in Webi

If you’re running Webi on BW, you may well have users that are used to being able to see either text or a key for an object. In this post, I’ll show you how to add a drop-down to your Webi reports that will offer your users this familiar functionality.
I’m using:

• BusinessObjects BI Platform 4.0 SP4 (the steps are mostly the same in XI3.1 SP3+)
• BW 7.1 (although the logic is the same for any data source)
• BICS connector (again, the logic is similar for any data connection method)

I’m assuming:

• You have a Webi report with a data source that has text and key values for some dimension.
• You can use Input Controls. If you’ve got a report with 10 tabs, this may not be a great solution.

An Example
Imagine the scenario where sometimes showing a profit center as ‘Corporate Administration’ might be the thing to do, at other times, the corresponding key of 10288791 might be more appropriate. And sometimes both.


BICS vs. Universe
The two big differences between using a BICS connector and connecting to a universe are hierarchies and delegated measures. I’ll start by explaining the trick as though you’re using a universe, then finish up with what special considerations these two differences need.

You’ll notice that in these screenshots I happen to be using a universe.


The Logic
As with the posts on dynamic measures and adding a search field in Webi, I’ll be using some Input Control tricks to allow the user to control what’s shown in a particular column. The ingredients you’ll need are:

• A dimension variable that holds the view state (Text or Key).
• A dimension variable that shows the appropriate dimension (the text or key for a dimension).

The View State

1. In your Webi report, create a new variable dimension.
   
2. Name it Text or Key (Select) and in the formula type =”Text”.
   
3. Click OK.
4. Create a new Input Control.
   
5. Base it on your new variable and click Next >>
   
6. Click the ellipsis next to List of Values
   
7. Type Key in in the Type a value: box and click the right arrow to add it to the list. You can hit enter if you’re in a hurry.
   
8. Add Text [Key] and Key [Text] to the list in the same way.
   
9. Click OK.
10. Click Finish.
11. Your input control should look like this:
    

The Dynamic Dimension
Now you’ll add a dimension to use in your report. This will change what it displays based on what’s selected in the input control.

1. Add the variable and name it Product (Text or Key).
   
   Obviously the dimension I’m using here is going to be Product. If you’re using cost center or account number or whatever switch out the names appropriately.
2. This step right here is only needed if you’re using database delegated measures. You may have noticed that as soon as you add some dimension variable to a block, your measures will all show #UNAVAILABLE. This is because Webi can no longer work out the calculation context. In these cases, change the Qualification to Detail and associate the variable with the original dimension. In my case, that’s Product.
   
   That makes Webi say “I don’t recognize this object, but David says it’s the same as Product, so I’ll go with that when aggregating my measures.”
3. The formula is pretty simple. If the user selects ‘Key’, show them the key (Product ID). If they select ‘Text’, show them the text (Product), and so on. The other two options are just concatenation of the fields.
   
   I’m using square brackets only because that’s what’s generally used in BW, you can use whatever you like.

The Result
In the below table I’m including columns for product and product ID just to demonstrate what’s going on. The third column is the ‘dynamic’ column. With ‘Text’ selected, I see the text in the third column:


If I select ‘Key’ in the input control, that column shows me the product ID.


Selecting ‘Text [Key]’ shows me just that.


So with that done, I can now delete the first two columns.

Well, not just yet. If you’ve got database delegated measures (if you’re using BICS, you probably do) then as soon as you remove ‘Product’ you’re going to get #UNAVAILABLE errors.

You can go ahead and remove the ID column. Then right click in the Product column and select Hide > Hide Dimension. The user won’t see it, but Webi will still know how to do it’s sums (remember Webi will ignore the variable dimension because we’ve told it that it’s the same as Product).

This is new in BI4.0 so if you’re using XI3.1 keep reading.



What about hierarchies?
If your product dimension is actually a hierarchy, then you’ll be blessed with a beautiful expandable tree in your Webi column, and you might not want to lose that. And if you’re using XI3.1 with database delegated measures, then you need to have the original product dimension in the block.

This is where conditional formatting comes in (or in XI3.1 the less-sensibly named ‘Alerters’). As I wrote about in Sorting Months in Webi, you can have one dimension in a block, but show something else in the cells. In this instance the logic changes slightly. You’ll tell Webi to: show the product dimension, unless someone selects something else from the input control.

1. I’ll remove the extra column so I just have the base product dimension and a measure.
   
2. With the product column selected, click the Analysis tab, then the Conditional tab, then New Rule…
   
3. Name it something great and change the condition type to Formula Editor
    
4. Click the tiny formula button down in the bottom right to launch the formula editor.
   
5. Remember that this formula must result in a true or false. I only want this rule to trigger if someone has selected something other than ‘Text’ in the input control, so my formula is fairly simple:
   
   Remember that “Text or Key (Select)” is the name of the variable dimension that the input control is based on.
6. Click the Format… button.
   
7. Click the Formula Editor button.
   
8. Select the dynamic dimension variable you created and click OK.
   
   Remember that this is the variable that shows either the product Text, Key, Text [Key] or Key [Text] depending on what the user selects in the input control.
9. Click the Text tab and change the font color back to Default, unless you want it red.
   
10. Click OK.

So now even though that column is technically the product dimension, I’m overriding what’s shown depending on what the user selects in the input control.


Note how it still says ‘=[Product]’ up in the formula bar. What’s more, because the conditional format does nothing if the user has selected ‘Text’ or ‘All Values’, you will still see that lovely tree structure if your dimension is a hierarchy.

Super happy bonus tip: if you’re showing say, Text [Key] and your dimension is a hierarchy, you will lose your tree structure, but you can still represent the hierarchy by indenting each record accordingly. Instead of showing product name, try something like this:
=FILL(“  “;[Product].Depth) + [Product]
.Depth will give you the depth of the node in a hierarchy as an integer. Obviously this only works if the dimension is a hierarchy.

That’s it!

- See more at: http://blog.davidg.com.au/2012/07/toggle-display-of-text-and-key-in-webi.html#sthash.n0USXgUm.dpuf