To create a new Format, right click the Data Source you would like to manipulate.
The new Format must be given a proper name. To add a new column to the Format, click one of the ‘+’ buttons.
The new column must also be named properly. Expand the Formula field to start entering the text manipulating functions. From the panel on the left hand side, you can search for available functions. And once found, drag them into the formula field.
References to other columns in the Source Data uses the ‘&’ syntax. You can search for the column names in search field on the left hand side, or, by typing ‘&’ and then pressing CTRL + Space, you will get a list of all columns and functions matching the partial string you have written.
Once you have completed your formula, a small green indicator in top right corner of the formula field indicates that the formula has been correctly syntax checked. Also, after clicking ‘Save’, the new column should appear in the preview on top.
The formula syntax “translated”: If the first three characters of the No. column equals “SPI” then keep the number of characters, counted from the right, that equals the total length of the string minus 3; if not, then just keep No. as it is.
So, now we have a new column with transaction ID numbers, where any prefixed “SPI” have been removed.
When clicking ‘Save’ a second time, the new Format will be saved. A saved format will be available as any other Data Source, while keeping the original Data Source.
Comments
I have done all the above steps and the excel file appears bellow the original file and if i preview the file i can see the calculaded/newly added columns, however i am not able to access the new columns that have been calculated why is that in order to create graphs and crosstabs. Why is that happening?
When you add new columns they are not automatically selected in the cube. You have to edit the cube and select the new dimensions and measures. Have you done that?
No didnt know that i just did the steps you suggested, and it works thanks for the support it was really helpful.
Thanks
Please sign in to leave a comment.