Forum Discussion

Adina_Miclea's avatar
Adina_Miclea
New Contributor II
2 years ago

Cube view in dashboard with rows displayed in the middle

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

 

 

 

Cube View
Dashboards
  • RobbSalzmann's avatar
    RobbSalzmann
    2 years ago

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

  • JackLacava's avatar
    JackLacava
    2 years ago

    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.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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's avatar
      NicolasArgente
      Valued Contributor
      2 years ago

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

    • mlopez's avatar
      mlopez
      New Contributor
      2 years ago

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

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

        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.

    • mariopoves's avatar
      mariopoves
      New Contributor
      7 months ago

      Hello Jack. I have one doubt about this solution. It is possible to do it with attribute members? I'm trying to do it with attribute members in UD1 and the descriptions are not displayed.

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        7 months ago

        I honestly don't know - Attribute Members do all sort of black magic and in many ways they're not real members. I note that in the doc it says:

        "Attributes can only be called through a business rule using a DataBuffer. The function property includeUDAttributeMembersWhenUsingAll can be enabled to allow rules to reference the attribute results for use in formulas."

        Rather than going to the (expensive) trouble of retrieving a buffer and inspecting it, I would try to use the brapi functions to read metadata. That assumes the member ID exists though...

        ' to make sure you actually get an ID in pov with attribute members, uncomment next line and check errorlog
' BrApi.ErrorLog.LogMessage(si, $"ud1 id is: {api.Pov.UD1.MemberId}")
Dim mbr As Member = api.Members.GetMember(DimTypeID.UD1, api.Pov.UD1.MemberId)
return mbr.Description

         

  • Adina_Miclea's avatar
    Adina_Miclea
    New Contributor II
    2 years ago

    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's avatar
      RobbSalzmann
      Valued Contributor II
      2 years ago

      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's avatar
        Adina_Miclea
        New Contributor II
        2 years ago

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

         

  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    2 years ago

    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's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    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?

      • RobbSalzmann's avatar
        RobbSalzmann
        Valued Contributor II
        2 years ago

        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.