How to hide a column in WebI
In this post I would like share some information on how to hide columns using alternate and manual methods. The illustrative example report is created from Efashion sample universe in BOXI 3.1.
Let us take the following example,
Here we have a column named “promotion (y/n)” which might not give more information regarding the promotion, so we intend to hide this temporarily until the client/customer asks for it.
Now we are going to hide this column from the block using alerter option from Web Intelligence, following screenshot shows exact report layout before hiding any columns
Steps to implement the alerter:
1. Click the alerter option in the reporting toolbar, a new window will open as shown in the sample screenshot below,
2. In Sub – Alerter formula, provide some formula that is always true like the one shown below,
= [Promotion Cost USD] = [Promotion Cost USD]
= 1 = 1
Similarly you can come up with many formulas that returns true.
3. Now go to format option and format everything to match you Web Intelligence background like cell content, border and background colors as shown below in the following screenshot.
4. Now select the column that you wanted to hide and activate the alerter,
5. Go to alerters at reporting toolbar and enable it, then the selected column will be hided from the layout.
Did we notice the column header in above screenshot, why we missed to hide the column header?
In WebI, both are considered as separated layout since column headers are differently formatted from column values fields.
6. Now select the column header and again apply the alerter to hide it.
7. The same hiding process can be done by simply formatting the column to match the background color of WebI and resizing the column width.
8. This manual process and alerter based hiding will work only at the display level meaning when the report is exported as excel, we will be seeing the hidden fields. However PDF does the job, it won’t display the column that is hidden in WebI.
Frequently used Functions and Formula
To remove the commas and currency symbols in the measure values,
To get the measure values in decimal notations,
To get the beginning and ending [year] values from a list of year values inside the reports
=Min([Year])+" to " +Max([Year]) In Report
=First([Year])+" to " +Last([Year]) In Report
To get the prompt names from the query,
To show "ALL" values in label , when selecting no values in Simple Filter. For example take year as an Simple report filter and there are values like 2002,2004,2006 … and "All Year". To bring these values in label , we can use two functions,
Both the functions works the same way when selecting ALL Year from List of Values shown in Simple Filter
IT will show like 2002;2004;2006 in the label, which becomes complex when more and more year values added in the tables. So we can show just "All Year" as label.
To show "All Year" as label, we can modify the functions as shown below,
=If DrillFilters([Year])<>"" Then DrillFilters([Year]) Else "All Year"
To get the report refreshed time and date in a cell, use the formula below.
="Last Refresh Date: "+FormatDate(LastExecutionDate();"dd/MM/yyyy") +" , "+LastExecutionTime()
Rolling Text in Report Title
Using HTML Scripts inside the reports for label and other format makes the report more dynamic from its interactiveness
For Example we can show a rolling text as an report title, We must ensure the following steps should be done so that html scripts can work in infoview ,
For a particular user account in BO, there are several preferences in using the infoview.
1. Logon to the Infoview,
2. There will be an option named "Preferences" at the top right of the window,click that option
3. you can find different sections, like general, desktop intelligence, Web intelligence etc..
4. Under the Web Intelligence Section,
Select a default view format: choose "Interactive"
Select a default creation/editing tool: "Interactive"
5. Click "OK" and save the preferences.
Then go to the report where you want to create a rolling text report title,
Create a Blank cell and create the HTML script,
Marquee tag is used for rolling the text form end to end in HTML,
for example, the sample script shown below is used for rolling the report title,
<Marquee> Report Title </Marquee>
After completing the script, select the cell and hit the properties tab in Web Intelligence Rich Client,
under Display options , read the content as "HTML" and finally export the report to the CMS, Now the rolling text can be viewed in infoview.
The same can be done from Infoview directly with corresponding Edit permissions,
select the cell, right click and select format and then cell
read the content as "HTML",
Click OK and rolling text report title is done, similarly we can do as many HTML scripts in the report and make it interactive.
In webi we can calculate the rolling sum by using two functions as per my knowledge,
In the following example, i have used efashion sample universe from Business Objects
They are RunningSum() and Previous(self) functions
while using RunningSum() function we have three arguments in this, which is RunningSum([Measure];Row|Col;([Reset Dimension]))
here we can control the flow of calculation by row using second argument (Row|Col)
the alternate way of doing the same is by using Previous(self) function
there also should be some other way to achieve this one using formula.
if you find any, share it here >….