Exporting formula from WebI to Excel

Hi All,

In this article, I will beproviding details on how to export excel formulas from Web Intelligence report  WebI) to excel export. 

Requirement: In a WebI report, we have some blank columns. After exporting WebI report to excel, user will manually enter numeric values to the blank columns. Calculation should take place automatically for the manually entered values in blank columns.

Example:  Report has Column A , B, C where B is a Blank column at report level.

Values in WebI report:

After exporting this report to excel, when user enter’ s values for column B, Total should be calculated automatically as  A+B+C.

Result Should be:

User need  not to manually recalculated total to include sum for manually entered values in column B using excel formula.

Steps to Perform:

Step 1: Create a WebI report with few Blank Columns

In this Example, Margin is a blank Column. Blank column will be filled once report is export to excel. User will enter values in this column after exporting to excel.

In report, Total = Sales revenue+ Quantity sold

However after exporting to excel, total column should automatically include values from Blank column when user enters.

In excel, formula for total should be,  [Total] = Sales revenue + Quantity sold + Margin

Step 2: We can achieve this only if column is dynamically populating formula that is accepted by the Excel. We need excel formula for dynamic calculation.

To achieve this, Exported report to excel and get exact column name in excel for blank column.

Example:Blank Column is against column G in excel and starting from 3rd row. In terms of excel Blank column=  G3 (G is column name and 3 is row number. )

Step 3:  Formula that needs to be used in excel to dynamically calculate sum in Total column. This formula is as per the excel syntax.

Important: Row number will change for each row of data; however column number will be constant.

Step 4: Created a formula using running count for Total in Web Intelligence and formula is same as we have in excel. Syntax should be as displayed in previous screenshot for Total in Excel.

A. Create a formula for each line of record using= RunningCount()

Note: 2 is added to running count because  in excel 1^st row has value starting from G3 and we are creating formulas as per the excel.

In WebI, create below mentioned formula for total:

[Total]=”=”+[Total]+”+”+”G”+[VarRUnningCount]

WebI output:

Note: Total Column has the formula we need for excel, however it doesn’t seems good at report level because complete formula is displayed in Total column.

Step 5:  Write a HTML code for total column as follows, so that Total column only displays numeric values

= “<font color=white>” +”=”+”</font>” + [Total]+”<font color=white>”+”+”+”G”+[VarRUnningCount]+”</font>”

Right click on Total column and select Format cell. Select “Read value as HTML”.

Output will as below without HTML code:

Important: Report must be viewed in HTML view mode .

Step 6: Export Report to excel  and you will see complete HTML code

Step 7:  In order to make excel formula to work after Webi report is exported to excel, use find and replace twice as mentioned below in sequence:

A: Find “</font>” and replace with empty

  B: Find  “<font color=white>” and replace with empty

Step 8 : Now, data is automatically, added in Total Column. Even you add any value to blank column it will be automatically added.

Note: This approach solves our purpose and we get what we need.  However below are two points where we need manual work:

1: We need to use find and replace 2 times after exporting  the report to excel and in below mentioned sequence:

1. Find and Replace </font> with null (empty)
2. Find and Replace <font color=white> with null (empty)  

2: Export of WebI report to excel is fine, however export to PDF will not give us expected results. Columns where HTML coding is used will be blank or will display complete code in PDF that cannot be replaced.

Conclusion: In this way exported excel will automatically calculate total column with values manually entered by user in Blank column.

Regards,

Manveer Nakoti

BO Consultant

Accenture