Forum Discussion

Rithik_Prem's avatar
Rithik_Prem
New Contributor III
2 years ago

Get Cube View Profile and Group Name using Business Rules

Is there any way to get the cube view profile and Group Name of the CubeView using Business Rules?

Thanks.

Cube View
  • EricOsmanski's avatar
    EricOsmanski
    2 years ago

    Here is some code where you can loop through the rows of the data table and get the information needed (will have to change the "BalanceSheetCurrentPeriod_WF" to your CV Name):

    Dim sqlStatement As String = String.Empty
    sqlStatement = $"Select CubeViewItem.Name AS [Cube View Name], CubeViewGroup.Name AS [Cube View Group Name], CubeViewProfile.Name AS [Cube View Profile Name]
    From CubeViewItem
    JOIN CubeViewGroup On CubeViewGroup.UniqueID = CubeViewItem.CubeViewGroupID 
    JOIN CubeViewProfileMember On CubeViewProfileMember.GroupID = CubeViewGroup.UniqueID
    JOIN CubeViewProfile ON CubeViewProfile.UniqueID = CubeViewProfileMember.ProfileID
    Where CubeViewItem.Name = 'BalanceSheetCurrentPeriod_WF'"
     
    Using objDbConnInfoApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
     
    Dim dt As DataTable = BRApi.Database.ExecuteSql(objDbConnInfoApp, sqlStatement, True)
    For Each record As DataRow In dt.Rows
     
    Dim cvName As String = record.Item(0)
                Dim cvGroupName As String = record.Item(1)
                Dim cvProfileName As String = record.Item(2)
     
    brapi.ErrorLog.LogMessage(si, "cvName: " & cvName & "     cvGroupName: " & cvGroupName  & "     cvProfileName: " & cvProfileName )
     
    Next
     
    End Using
  • EricOsmanski's avatar
    EricOsmanski
    Valued Contributor
    2 years ago

    There is, but a Group can exist in multiple Profiles. What would you like to return in that case? A list of all profiles?

    • Rithik_Prem's avatar
      Rithik_Prem
      New Contributor III
      2 years ago

      So we want to show the path of the Selected Cube View, like if I run a CubeView it should show the corresponding Group and Profile of that cubeview.

  • EricOsmanski's avatar
    EricOsmanski
    Valued Contributor
    2 years ago

    Understood. But what if the group exists in multiple profiles (such as the below)? What Profile should show?

     

  • Rithik_Prem's avatar
    Rithik_Prem
    New Contributor III
    2 years ago

    Okay understood, so if a group exists in multiple profiles then list of all the profiles and the group name should be displayed.

    If the group is only in one profile then the Group name and the profile name should be displayed.

    • EricOsmanski's avatar
      EricOsmanski
      Valued Contributor
      2 years ago

      Here is some code where you can loop through the rows of the data table and get the information needed (will have to change the "BalanceSheetCurrentPeriod_WF" to your CV Name):

      Dim sqlStatement As String = String.Empty
      sqlStatement = $"Select CubeViewItem.Name AS [Cube View Name], CubeViewGroup.Name AS [Cube View Group Name], CubeViewProfile.Name AS [Cube View Profile Name]
      From CubeViewItem
      JOIN CubeViewGroup On CubeViewGroup.UniqueID = CubeViewItem.CubeViewGroupID 
      JOIN CubeViewProfileMember On CubeViewProfileMember.GroupID = CubeViewGroup.UniqueID
      JOIN CubeViewProfile ON CubeViewProfile.UniqueID = CubeViewProfileMember.ProfileID
      Where CubeViewItem.Name = 'BalanceSheetCurrentPeriod_WF'"
       
      Using objDbConnInfoApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
       
      Dim dt As DataTable = BRApi.Database.ExecuteSql(objDbConnInfoApp, sqlStatement, True)
      For Each record As DataRow In dt.Rows
       
      Dim cvName As String = record.Item(0)
                  Dim cvGroupName As String = record.Item(1)
                  Dim cvProfileName As String = record.Item(2)
       
      brapi.ErrorLog.LogMessage(si, "cvName: " & cvName & "     cvGroupName: " & cvGroupName  & "     cvProfileName: " & cvProfileName )
       
      Next
       
      End Using