Smart View formula replace mystery


You might be thinking what’s the mystery. I pray continue reading.

We all use formulas in Smart View, and I came across a recent issue when helping a user. He was using a formula to derive months and pull data from Essbase.

I’m trying to replicate the “issue” in good old Sample Basic.

That is how his sheet looks!!!

Aug, Jul, and Jun are there. However, they are not getting recognized by Essbase. I was thinking about

  1. Did someone change the member names?
  2. Is he using different alias tables?
  3. What is that date doing there?

After having a conversation with the user, I found that the months that show up on the sheets are not real months!!!!

What he did was to apply a custom format and made it look like MMM

Essbase was still getting the full date field and since there was nothing there in Essbase as a member name to return it replaced that with the default “Period.”

The correct way to get month name from date is using an EXCEL formula. TEXT(date_text,”mmm”)

There you go, and it now starts showing the exact numbers.

It was a fun exercise, I should say.


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.