There has been some questions being asked recently that when exporting a Webi Report to text the column header are not getting aligned to text. I I though of sharing a work around which might prove helpful for the people facing the same issue in future. However this is something which I will create a idea place entry to add/fix this in the upcoming releases of BO.
I am using a BI 4.0 SP6 version of Webi so its applicable for BI4.0 only.
Defining the issue:
There has been a problem that if you export a Webi to text the output appears to be skewed (shown as below) as the text column is based on the value of the column making it unreadable. Although you might argue you can import it to excel to consume it.
Now the easiest solution to this is to add some additional padding of Spaces to the right of the column values and it will be better. However the problem here is
as the values will be dynamic the padding has to be dynamic as well.
Now to do this we can get the padding dynamic first we have to find out the length of the values in each rows of the table for a column. I am going to use the formula for a number type as that will be a little tricky and texts will be easier.
Here Fail Formula being the Dimention
=Length(FormatNumber( [fail formula] ; “0”))
Now the next thing is to create a dynamic padding for each row of the column for which there could be a better way to do it but I will stick to a plain and simple logic.. Note 9 is a hard coded value in the formula which is just the length of the column header (formula1)
lets name it Col1_Padding
= If 9 – [length1] = 9 Then ” ”
Else If 9 – [length1] = 8 Then ” ”
Else If 9 – [length1] = 7 Then ” “
Else If 9 – [length1] = 6 Then ” “
Else If 9 – [length1] = 5 Then ” “
Else If 9 – [length1] = 4 Then ” “
Else If 9 – [length1] = 3 Then ” “
Else If 9 – [length1] =2 Then ” “
Else If 9 – [length1] = 1 Then ” “
Now the next step will be pretty simple just create another formula to leverage this col1_padding to add the dynamic padding
Now once this is done for all columns you are all set to go ….Here is the output after dynamic padding the column 1 and the values are nicely aligned..
(p.s. Column 2 is not padded)
hope you like the little trick.. Please post your comment if you have any…