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 😉