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 😉
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.
Regards,
Philip
Thank you Philip
Yes that was the requirement, I guess they wanted to check whether the user opened/saved data (they also save it as EXCEL books, oh I forgot to mention that they just use Smartview)
Deeper into the matrix
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.
Thanks
Did you read the post ;). I used @CalcMgrDateToExcel and @CalcMgrGetCurrentDateTime. This will work if the forms are opened in Smartview.
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”
Thanks’
Prajin
That’s an issue with the function. You might have to apply the patch on Essbase side too.
Hi Celvin,
Below are the patching level on the environment I am testing this feature.
Cal Manager : 11.1.2.4.006
Oracle Hyperion Essbase Server : 11.1.2.4.011
Oracle Hyperion Essbase RTC : 11.1.2.4.011
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..?
Thanks,
Prajin
What I meant was you need to apply the same patch of calc mgr on Essbase server too.
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..?
Thanks,
Prajin
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.