Webi tricks to save hour(s) of work (in present & in future)

[mhmohammed]

Hello everyone,

Here are couple of Webi tricks that are easy to implement and much easier to forget about, when developing a report, but which makes us spend hours of work later fixing/redoing/testing them over and over. I've learnt this the hard way, by spending a few additional hours whenever there was an update request for such reports.

 

Multiple variables to address custom sort reset issues:
Let's say, we've a variable v_XYZ that we created in the report, used it to display data in a table/crosstab, and custom sorted on. Everything is perfect in this report until, there is a request to update the logic in v_XYZ by adding/removing conditions which may result in increase/decrease of the LOVs. That update will mess up custom sort in the report and you'll have to go back and custom sort again, it's a pain if we have more than 15 or 20 LOVs in that variable. If that's not enough, what if that variable’s logic changes frequently! We wouldn’t want to imagine that. The solution is to create 2 variables, 1st is "v_XYX_logic" which has the actual logic with If Then Else statements, and 2nd is "v_XYZ" that is just referring to the logic variable we created earlier as "=[v_XYZ_logic]". Now, we use the variable v_XYZ to display data in table/crosstab etc, and also to custom sort on. Voila, the custom sort won't reset now, regardless of how many times you update the logic in the variable v_XYZ_logic.

Dummy (label) objects in the universe to use in combined queries:
I've worked on quite a number of reports which were created using multiple data providers (say 3) and those data providers had (say 4) combined queries each. In my case, each combined query in a data provider had measures that would call different context (subject area), even though most of the dimension objects were same and there was no flag objects that helped me to differentiate between the data, if its from combined query 1/2/3/4. This was great, until a user said that the numbers were not correct and I had to trouble shoot and find which combined query is the culprit. To trouble shoot the issue I had to duplicate this report 12 (3*4) times and create as many Test reports. Each Test report would have only one combined query from one data provider (as shown below) and then, I would try to find the root cause.

Abbreviations used:

DP = Data Provider

CQ = Combined Query

TR = Test Report

1. DP 1
   
   
   
   1. CQ 1
   
   
   2. CQ 2
   
   
   3. CQ 3
   
   
   4. CQ 4
   
   
   
   


2. DP 2
   
   
   
   1. CQ 1
   
   
   2. CQ 2
   
   
   3. CQ 3
   
   
   4. CQ 4
   
   
   
   


3. DP 3
   
   
   
   1. CQ 1
   
   
   2. CQ 2
   
   
   3. CQ 3
   
   
   4. CQ 4
   
   
   
   

Test Reports creation:

1. TR 1 was created using the combination CQ 1 from DP 1


2. TR 2 was created using the combination CQ 2 from DP 1


3. TR 3 was created using the combination CQ 3 from DP 1


4. TR 4 was created using the combination CQ 4 from DP 1


5. TR 5 was created using the combination CQ 1 from DP 2

so on till TR 12.

Relax, the way to get rid of this issue is create dummy (label) objects in the universe and add one each in the combined queries. I created 4 dummy objects as below:

No	Object Name	Definition	Add to
1	Combined Query	'Combined Query 1'	CQ 1 (in all the data providers)
2	Combined Query 2	'Combined Query 2'	CQ 2 (in all the data providers)
3	Combined Query 3	'Combined Query 3'	CQ 3 (in all the data providers)
4	Combined Query 4	'Combined Query 4'	CQ 4 (in all the data providers)

Make sure that the data type of all the objects is the same (string), they won't parse, but will work fine. Notice the name of first object is "Combined Query" and not "Combined Query 1," I did that on purpose so that name of the object resulting from this query will be "Combined Query." Now, add one object each in the combined queries as I said in the "Add to" column.
Voila, now can filter the data using Combined Query object, so, testing is a whole lot easier. You can also use this object in the logic if it simplifies things (I did, it helped me a lot in some crazy logic.
Hope this helps people save many hours of work! Any feedback to improve this post is appreciated.

Thanks,
Mahboob Mohammed