Exporting formula from WebI to Excel
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 1st row has value starting from G3 and we are creating formulas as per the excel.
In WebI, create below mentioned formula for total:
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:
- Find and Replace </font> with null (empty)
- 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.
Thank you for the instructions and these did help me start for a similar requirement.
However, when i export the file to excel, the formulas are in a text format. And then i have to go and change it to general format and enter into each cell and hit the "enter" button for the formula to work.
i don't mind if the formula or "522729.4 + G6" (in your sheet) shows up in excel.
is there any way for the formulas to automatically work immediately after exporting to excel?
This is true that formula will be exported in to text format and you need to update it in excel, so that it can be understand by excel and used as formula.
However, you need not to go and change each formula. The can be achieve in one go for all the formulas exported to excel.
As I have mentioned find and replace 2 times in my blog for formulas to work in excel.
If you are not using <font> tags in your report, then you need to modify your formula in webi as follows:
formula= : ="'"+"="+"-"+[total]
This is how your column in webi should look like.
Export to excel and use below mentioned find replace:
now you are replacing '= with =.
This is as I had already mentioned, after exporting to excel, in order to make excel understand that formula being exported we need to validate that column. else it is considered as text.
This is one time activity for your complete export to excel because all the form;as will be following the same syntax and you can select Replace All in find replace window.
excel will work as expected and take account of blank column in values in calculation.
Hoe this has helped.
Thank you for the instructions yet again.
I did try this, but I might be missing some task due to which it still comes up as text.
However, as a workaround I have created a macro in the excel sheet to put in the formula which would take care of that part.
Nonetheless, information you provided was also quite handy.