06-04-2024 10:42 AM
Hello,
Could you tell me if there is a way to search in which cube views a certain member is used/referenced ?
I thought of doing a export of XML and do a search in notepad++ but maybe there is a better way/more user friendly for administrators which are not technical (which is the case for my current client)
Thank you
Matthieu
Solved! Go to Solution.
06-05-2024 09:09 AM
That is a really good idea Henning. And I have written a BR to do just that. Here is the code I used for reading the XML string in case it helps:
Dim sql As New Text.StringBuilder
sql.Append("SELECT Name, Description, XMLData ")
sql.Append("FROM CUBEVIEWITEM ")
sql.Append("ORDER BY NAME ")
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows()
Dim CVName As String = dr("NAME")
Dim CVDesc As String = dr("DESCRIPTION")
Dim XMLString As String = dr("XMLData")
Dim DimensionStringID as String = "A#Revenue"
If Not String.IsNullOrEmpty(XMLString) Then
' Load the XML string into an XDocument
Dim xdoc As XDocument = XDocument.Parse(XMLString)
' Query all memberFilter nodes and filter those containing strings starting with the DimensionStringID eg A#, U1#
Dim memberFilters = From mf In xdoc.Descendants("memberFilter")
Where mf.Value.Contains(DimensionStringID)
Select mf.Value
' Process each memberFilter node value
For Each filter As String In memberFilters
' Split the filter value by colon and find elements starting with DimensionStringID eg A#, U1#
Dim elements = filter.Split(":"c)
Dim memberElements = From el In elements
Where el.StartsWith(DimensionStringID)
Select el
' Save the member element
For Each memberElement As String In memberElements
' Comes through as A#Revenue so get just Revenue
Dim HashIndex As Integer = memberElement.IndexOf("#"c)
Dim thisMember As String = memberElement.Substring(HashIndex + 1)
' Add to output somehow....
Next
Next
End If
Next
End If
End Using
06-04-2024 10:58 AM - edited 06-04-2024 10:59 AM
Hello @MatthieuR,
That's the way I do it too, I don't think there's any other supported way unfortunately.
06-04-2024 12:32 PM
Hi, you could create a quick dashboard with a view on the CubeViewItem table. You could allow the customer to search for his members in the XmlData column and return the Name column.
With some effort, this could be really nice and - of course - be re-used at other customers too. The XmlData column in itself is probably a little cryptic for non-technical customers, but if you apply proper filters, you could just pull the information that is needed and differentiate between columns and rows too.
06-05-2024 09:09 AM
That is a really good idea Henning. And I have written a BR to do just that. Here is the code I used for reading the XML string in case it helps:
Dim sql As New Text.StringBuilder
sql.Append("SELECT Name, Description, XMLData ")
sql.Append("FROM CUBEVIEWITEM ")
sql.Append("ORDER BY NAME ")
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows()
Dim CVName As String = dr("NAME")
Dim CVDesc As String = dr("DESCRIPTION")
Dim XMLString As String = dr("XMLData")
Dim DimensionStringID as String = "A#Revenue"
If Not String.IsNullOrEmpty(XMLString) Then
' Load the XML string into an XDocument
Dim xdoc As XDocument = XDocument.Parse(XMLString)
' Query all memberFilter nodes and filter those containing strings starting with the DimensionStringID eg A#, U1#
Dim memberFilters = From mf In xdoc.Descendants("memberFilter")
Where mf.Value.Contains(DimensionStringID)
Select mf.Value
' Process each memberFilter node value
For Each filter As String In memberFilters
' Split the filter value by colon and find elements starting with DimensionStringID eg A#, U1#
Dim elements = filter.Split(":"c)
Dim memberElements = From el In elements
Where el.StartsWith(DimensionStringID)
Select el
' Save the member element
For Each memberElement As String In memberElements
' Comes through as A#Revenue so get just Revenue
Dim HashIndex As Integer = memberElement.IndexOf("#"c)
Dim thisMember As String = memberElement.Substring(HashIndex + 1)
' Add to output somehow....
Next
Next
End If
Next
End If
End Using
06-05-2024 09:11 AM
Great, thanks a lot for sharing!!
06-07-2024 04:59 AM
Thank you all for your valuable insights, we will look into this!