Webi Reports using BEx Queries – Issues and Workarounds
During the WebIntelligence report development, we faced the below challenges.
- Unable to hide the zero/Null rows/columns in a Cross Table
- Standard combined stacked and Line chart with dual axis template is not available
- Prompts in WebIntelligence are not working
- Not able to add more than 50 objects at a time in the Result objects Panel
In order to overcome these challenges, there are workarounds developed for each case and the same is described in the forth coming slides.
Note: This is applicable for the Business Objects Version 4.0 SP6
1. Hiding the Rows/Columns
If the Rows/Columns in a crosstab which has the measure values as NULL, Zero and Sum as Zero then the particular Row / Column must be hidden automatically.
Creating a Cross Tab with the normal Measure
Drag the Cross Tab template to the Reporting Panel and assign the corresponding dimensions and measures. The resulting Cross Tab is shown below.
Right click on the cross tab, then click the Format Table and uncheck the highlighted icons as shown below.
The error #Unavailable will be present in the Cross Tab.
As a work around a new Detail object can be created and assign it to the same measure.
Drag the newly created detail object to the Cross tab. Now the NULL, Zero valued columns are hidden automatically as shown below.
2. Combined stacked and Line chart with dual axis
Existing WebIntelligence version 4.0 does not have the template for the Combined stacked and Line chart with dual axis.
This type of chart can be achieved by following the procedure below.
1.Select the Combined Column and Line chart template from the existing Chart Type.
2.Assign the X axis values (Dimension) to the Category Axis and Measures to the Y axis and click ok.
(Note: Measures which need to be stacked should be assigned to Value Axis 1 and Measures which should be shown in the Lines has be assigned to Value Axis 2)
4. Under Global -> Region Type , change the Value Axis 1 Region Type to Bars from the drop down and change the Value Axis 2 Region Type to Lines from the drop down.
5. Navigate to Value Axis tab in the design, change the Unstacked to Globally Stacked Chart as highlighted below
6. Click Apply and Ok. Now the desired Combined stacked and Line chart with dual axis will be available as below
3. Prompts in WebIntelligence
When we create a WebIntelligence report on top of BEx queries unlike universe reporting, though the objects are added to the Query Filters, the list of values will not be available and also only one value can be filtered with manual input.
As a work around, open the tree of the dimension which should be
made as a prompt.
Select the Type:Member of the dimension as highlighted below and drag to the Result Objects Panel
Click the down arrow from the object and the Member
Selector will be prompted.
Check the Enable Parameter option in the Prompt Tab
and click ok.
Refresh the query now and the prompt window will be seen as below along with the values for the dimension.
Multiple selections with the values list are available in this workaround whereas single selection and manual input is possible when the object is used under query filters.
4. Objects Addition in Result object Panel
When we add more than 50 objects to the Result objects Panel from a BEx Query at a time, an error(WIS00000) will be encountered when we refresh the report.
As a workaround we can add less than 50 objects at an instance and refresh the report and again edit the query and add the remaining objects.
For e.g. if there are 125 objects needs to be added to Result Objects Panel then follow the below table.
No of objects to be added