Cube view in dashboard with rows displayed in the middle

[Adina_Miclea]

Hello all,

I need to create a dashboard based on a cube view where I have accounts on rows, and MTD and YTD view in columns for actual, actual vs budget, actual vs PY.

The request is to have in the dashboard the report with the rows displayed between the MTD and YTD view columns. 

Is there any way I could achieve this in OneStream?

Thank you!

Adina

[RobbSalzmann · Accepted solution]

Hi Adina , Thats a nice report. I would construct this in excel and use the spreadsheet component to display it in a dashboard.  

[NicolasArgente]

Hey @Adina_Miclea  I do not think it is possible to have the rows in the "middle" of the columns. Never saw that but I would love to be wrong 🙂
As it does not seem to be possible now, OneStream would have to create this. Please go to the "ideastream" section of the forum to drop a message asking them to create this new improvement. That is a good idea!
Thanks.

[RobbSalzmann]

How would this work?  If accounts are on the rows, and you put MTD on the rows (assuming nested with accounts) and YTD is in the columns with period and scenario, what data would you expect to see in a cell a_1000,MTD by Jan,Actual,YTD?

[NicolasArgente]

Hey @RobbSalzmann !
My understanding is that she wants something like this :

but... not sure 🙂

[RobbSalzmann]

@NicolasArgente  Thank you!  Yes that makes sense and for certain requirements can be the basis for some fairly elegant reports. 
While not a Cube View thing, I can imagine doing this with table views and referenced cells.  Quite doable with spreadsheets.  

[Adina_Miclea]

Hello Rob, MTD and YTD are both columns. Below a dummy sample of what I would need. Easily done in a cube view, however the accounts are of course first as default.

[RobbSalzmann · Accepted solution]

Hi Adina , Thats a nice report. I would construct this in excel and use the spreadsheet component to display it in a dashboard.  

[Adina_Miclea]

Thank you, Robb, for you input. A good solution, hopefully accepted by the client!

[JackLacava · Accepted solution]

Who's your daddy and what does it do? 🙂

First, create a UD8 member that will just return the current POV account name with type DynamicCalc and Member Formula as follows:

If api.View.IsAnnotationType Then
' if you wanted to target a specific view you could use this instead:
' if api.POV.View.Name.XFEqualsIgnoreCase("Annotation") then
   return api.POV.Account.Description
end if

Then create your CV. I'm going to hide icons:

And hide row headers:

First and last columns are up to you, just set the central one like this:

And that's it! You can play around with formatting to emphasize the central column.

@NicolasArgente @RobbSalzmann would you hire me? 😁

PS: there may be a way to do this with custom DataCell functions, without the extra UD8 member, but I couldn't get it to work because the returned cell is always treated as a number for some reason.

[NicolasArgente]

Ahahha!! Good job Jack!  and like always, thanks for sharing!

[mlopez]

@JackLacava Any idea how to make this work when the Account is a Dynamic Calc? 

[JackLacava]

In that case, the code I put on UD8 will have to go in the Account formula. If that formula already replies to Annotation views, you'll have to point to a particular one of them and check for that in the code as I described in the comment.

As I mentioned, there is probably a way to make this work with custom GetDataCell functions instead, but I couldn't make it work.

[mlopez]

Thank you!! It worked

[RobbSalzmann]

@JackLacava You're hired!  Nicely done, and thanks for the detailed explanation!