Thoughts, ideas and solutions from a few EPM consultants.

Multiline Formulas using Load Rules

I've been racking my brain for weeks on how EPMA loads multiline formulas but there doesn't appear to be a way to do it manually. Finally I have a breakthrough.

First I have replaced all the newline characters in my formulas with \n. For a test I created a small csv file with a single line.

"Measure","Keith","CASE WHEN Is([Scenario].[Actuals], [Scenario].CurrentMember) THEN\n[Net Income]\nEND"


Next I created a simple parent/child dimension building load rule. With a small twist. On column 3 I created a replacement indicating that "\n" should be replaced with "!".

Replace \n with !

Once the load rule is saved. I copied the rule back to my desktop using the "Save as" functionality.

Save As

Once the file existed on my computer, I opened the file in notepad++ and looked for the !. Once I found the ! I highlighted it and hit enter to replace it with a carrige return and newline.

Replace ! with Carrige Return & Line Feed

Next I used the open in EAS to load the modified rules file back into Essbase.

Open in EAS

Pick file

Once again I used the save as capability in EAS to save the rule back to the target application.

Save As to Target Application

Select Target Application

Last I loaded the file I created at the begining and tada the member formuala now spans multiple lines in the outline. I have expanded on this since at my current project to do tabs as \t and since we are using DRM I have been using the JavaScript derrived properties to automatically have our formuals replace newlines and tabs with \n and \t.

Obviously this has no effect on the performance of Essbase but it sure is nice to see our rules in a little more readable format.