Refresh Time in Webforms and a Calc Manager Surprise 11

I’m working at a client now and I got a request (well we all get those isn’t it) which was really interesting. What they are looking for is a refresh stamp. In my mind I was like, oh I’ve done that a lot of times (even wrote a CDF for that one). Now when I got the details I found out that they are not really looking for a data refresh time stamp, they want a refresh time stamp on every form. Well if you look at it is kind of an auditing to see when the form was downloaded (they use Smartview and download webforms).

I was like okay, I can give you the time stamp, however it is not going to be pretty. (In my mind I was looking at @CalcMgrGetCurrentDateTime() function).

All I did was to create a dynamic member (to calculate at a default Cost center, that is the one which is in rows for all forms). Now when the form came back all I saw was 20161214141520.

It took me a while to understand what I was seeing. (It was ugly 🙁 considering that the client produces extremely good UI).

Me and my colleague Alex went on a quest to find out how to format YYYYMMDDHHMMSS to a pretty format. Did try lot of custom EXCEL format and none worked. So I got an idea and I went and looked at how EXCEL stores date, DATEVALUE explains how it does that. All I had to do is now to find out the days passed from January 1, 1900!!!!!

I was about to write a CDF and then I remembered the awesome Calc Manager team already have a set of EXCEL functions converted to BSO functions, so started looking at all the functions which has EXCEL in it and unfortunately I couldn’t find one.

It turns out that I was lucky, or we (me and Alex) were so persistent that we got it done.

Here was what the formula looks like.

 There you go @CalcMgrDateTimeToExcel, it converts YYYYMMDDHHMMSS to EXCEL DATEVALUE. (I’ll explain how I was able to get all the functions as list there in member editor, it’s magic)

Now let’s see the form.

Well that is not an EXCEL date, trust me I’m a doctor 🙂 (it’ll become an EXCEL date)

All you’ve to do is to format that cell in EXCEL.

Now once the formatting is completed, save the format.

There you go a neatly formatted form refresh time 🙂

Now let’s understand what happens behind the scene. The moment you save the format in Smartview it gets updated in Form XML (well I’m not a fan of adding that in the form XML, however I can see that some of the earlier code got changed – user name is not getting stored now)

The first thing to understand the XML form formatting is every cell that you apply formatting (even the ones that didn’t) starts appearing in the XML file as a datacelltuple. It is pretty easy to find out the cell that you are looking for (or it is going to be easy from now on ;))

In my form FormRefresh member is in Column C (Webform layout) so it becomes Segment 3.

No Cost Center is in 4th row, however I’ve two dimensions in columns, so it becomes Segment 6.

All I’ve to do is search for segment=”6.0″ in the xml and here is what I got.

It says my cell style id is 8

Now perform a second search, this time I’m going to search for id=”8″ or even better you can search for cellStyle id=”8″

You’ll see that every formatted cell (the non formatted ones too) get a number format (numFrmt) and there is where the formatting is recorded. It’s easy from here onwards, search for numFrmts and we are going to look for id 2.

id 1 is used for rest of the world, so if you look at the thousand separator, you can go pretty creative here. I can have different thousand-separator and decimal-places for every cell.

id 2 is still treated as a number(makes senses, everything is a number for Essbase) however it gets a tag dateFormat and it shows what formatting got applied from EXCEL.

Now I did say that I’ll explain how I got all the functions listed in formula editor. It was an accident, I was talking to Alex and pressed space, since I do a little bit of java coding I knew what I did.

I called Sree Menon(Calc Manager PM) and told him that I discovered something new!!!(See I’m brave ;)). Then he told me that it is not just that and there is more to it. So let’s watch a video so see what happens

There you go what you witnessed was Auto Completion (Shift + Space) and Auto Suggestion (Ctrl + Space) in Calc Manager.

You can also hide the member names in form using a simple formatting trick.

Just change the color of the font to what is shown in the picture. That’s all member names are gone from the form (save the formatting).

Hope you enjoyed the show 😉

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.

11 thoughts on “Refresh Time in Webforms and a Calc Manager Surprise

  • Philip Hulsebosch

    Hello Celvin, nice work. I hope I remember when I get one of these requests.

    But, do I understand it correct, the date/time stamp shows the time the form was opened. What is the point of doing this? It is not directly related to saving data. One might open the form without saving data.

  • Aamir Zaveri

    Hi Celvin,
    Do you know how to display date and time stamp on planning forms. I am using @CalcMgrGetCurrentDateTime() but it’s not showing time properly.

    • Celvin Kattookaran Post author

      Did you read the post ;). I used @CalcMgrDateToExcel and @CalcMgrGetCurrentDateTime. This will work if the forms are opened in Smartview.

      • Prajin Gopi

        Hi Celvin,

        I am able to capture date stamp using @CalcMgrGetCurrentDate function but @CalcMgrGetCurrentDateTime doesn’t work for me. I am trying to assign the output of @CalcMgrGetCurrentDateTime to a member through business rule. It give validation error as below.

        “Operator expected after @CalcMgrGetCurrentDateTime”


          • Prajin

            Hi Celvin,

            Below are the patching level on the environment I am testing this feature.

            Cal Manager :
            Oracle Hyperion Essbase Server :
            Oracle Hyperion Essbase RTC :

            By your earlier comment that Essbase patch is also required, can you help to confirm if Calc Manager also has to be patched up to the level of Essbase..?


          • Celvin Kattookaran Post author

            What I meant was you need to apply the same patch of calc mgr on Essbase server too.

          • Prajin Gopi

            Hi Celvin,

            As per your previous update, we did apply the cal mgr patch on essbase server and now this function works as expected. I am able to see the time stamp in smart view after formatting the cell to custom date type. But on saving the form, the value gets back to the old form (junk value). Every time I want to see the time stamp, I have to format the cell to the custom date type. I could see that the xml is getting updated with this date format but from smart view date format doesn’t change permanently.

            Any thoughts here..?


          • Celvin Kattookaran Post author

            You need save the format using an admin account. Open webform in Smartview apply the format and save it. You should be able to see that formatting in the form xml.