FCC - Showing Calc Status on a Report

With HFM and Financial Reporting, there is a function that pulls the calculation status into a report. This is useful for those who want to ensure the data has been consolidated when looking at the results. I've seen conditional formatting used in conjunction with this to alert the users that the data is suspect if the consolidation hasn't been ran.

With FCC and Reporting/Narrative Reporting, there isn't such a function. But there's still the same need. I filed an idea lab submission to request the function. I thought I (and my clients) was out of luck, but then I thankfully ran across a simple solution, and it's built in!

When building the report, look at account FCCS_CSTATUS FILTER. This account just doesn't give you some number that you have to convert - you get the proper text status. Along with specifying the account, you have to set the point of view to FCCS_No Data Source (which isn't used often) and FCCS_No Movement. The rest of the point of view should be normal for other data retrieves, but the intercompany and custom dimensions should be set to FCCS_No Intercompany or No custom.

When the report runs, it looks like this. Note this report is also showing the out of balance amount. The column headers for the data source and the movement are hidden. Not shown, the rows for the report are level 0 entities.

And being an account, this means that the calculation status can be pulled into Smart View with either ad hoc or functions, which is a big bonus.

When was the last data load?

I had a request recently for a simple way for users to know when the last data load occurred. They could go into process details or jobs, maybe, but was there a simple way to check from within a report, data grid, or even Smart View. There may be something better, but here's what I did.

I created an account called LL_DateTime. I originally was going to put the year in one account, month in another, etc. but I ended just using the one account.

Within data management (this part isn't in the new interface yet) I created a logic group to duplicate a record. I picked a retained earnings account for one of the primary legal entities so there would always be a data point to duplicate.

After assigning the logic group to the location, I used a SQL map to change the amount. The two SQL functions in use here are SUBSTR (get a subset of a string) and SYSTIMESTAMP (date and time on the server).

The SYSTIMESTAMP returns a format of 28-MAY-24 02.34.56.654321 PM -00:00  which isn't something we would be able to load as an amount. So SUBSTR is used to pull out pieces of this and to combine them into something useful. There are a few components that need mapping. The month of May needs to be converted to a number. The AM/PM indicator needs to be converted to a 24 hour clock (again, trying to load the date and time as one amount. There are a couple of different WHENs in use to direct the calc (if May, then 05; if PM, then add .12 [12 hours]). Admittedly there is probably a more elegant way of converting the month to a number without having to enumerate all of the options.

So, the format of the output looks like this: 20240528.1434. This is an easy number to load and report.

The last part of the output (00:00) indicates the timezone. In this case, it is UTC. This could be converted to a specific timezone but in the case of worldwide users, most people would have to convert anyway. It's better to just use UTC and let each user deal with summer time, etc.

The last part of the puzzle here was to create a simple data grid with all of the point of view members locked except for the year and period. This way users can check and see when the data was last loaded. They can also replicate the point of view and use Smart View, etc.