FilterPopup empty on predefined Cube

[Sven Grundmann]

Hello,

according to your documentation, we set up an OLAP Client with an preselected Cube:

  Private Function CreateOlapReport() As OlapReport

    Try

      Dim olapReport As OlapReport = New OlapReport()

      olapReport.Name = "Report"

      olapReport.CurrentCubeName = "Cube"

      Dim measureElementColumn As MeasureElements = New MeasureElements()

      Dim measureElement = New MeasureElement With {.Name = "Turnover"}

      measureElementColumn.Elements.Add(measureElement)

      Dim dimensionElementRow As DimensionElement = New DimensionElement()

      dimensionElementRow.Name = "Customers"

      dimensionElementRow.HierarchyName = "Levels"

      dimensionElementRow.AddLevel("Level 1", "Members")

      Return olapReport

    Catch ex As Exception

      Return New OlapReport()

    End Try

  End Function

The cube ist selected as we would expect it, but now, we want to filter Customers by clicking the selected Customer dimension. Unfortunately, the member editow window appers, but is empty. When dragging the Customer dimension into the client and clicking the Customer Dimension, the members appear as they should. 

The question:

How can I achieve programmatically, that members of a pre selected dimension appear in the member editor. 

Best regards

Sven Grundmann

Attachment: sf_empty_member_editor_cd096741.zip

[Sastha Prathap Selvamoorthy]

Hi Sven, 

We have analysed your code mentioned in the query. The hierarchy element that you have added to the row in the report seems to be not appropriate. Please find the below appropriate code sample below. 

    Private Function CreateOlapReport() As OlapReport 

        Dim olapReport As New OlapReport() With { _ 

            .Name = "Default Report" _ 

        } 

        olapReport.CurrentCubeName = "Adventure Works" 

 

        Dim measureElement As New MeasureElements() 

        measureElement.Elements.Add(New MeasureElement() With { _ 

            .UniqueName = "[Measures].[Customer Count]" _ 

        }) 

 

        Dim dimensionElementRow As New DimensionElement() 

        dimensionElementRow.Name = "Date" 

        dimensionElementRow.AddLevel("Fiscal", "Fiscal Year") 

 

        olapReport.SeriesElements.Add(dimensionElementRow) 

        olapReport.CategoricalElements.Add(measureElement) 

 

        Return olapReport 

    End Function 

Also we have attached a sample with the above mentioned report for you reference below. 

Sample Link: http://www.syncfusion.com/downloads/support/forum/124664/ze/OlapClient(VB)-1861505541 

Regards, 

Sastha Prathap S. 

[Sven Grundmann]

Hello,

thank you for your reply. As I could not find any real structural difference between your code example and mine I looked at my Dimension Definition and found, that the Level Name I used, was not the Level Name created by the dimension. This solved the issue. 

So, maybe you can help me with a second issue related to the filter popup window. 

We have another dimension, called Regions, that contains countries grouped together to some regions. What we want to do now is to permit access to the Cube data only for specific countries. We do this with a hidden Dimension that is assigned to the currentReport SlicerRangeFilter like this:

      olapReport.SlicerRangeFilters.AddRange(getUserCountriesFilter())

and 

  Private Function getCountriesOfCurrentUser(user As User) As IList(Of Country)
    Dim returnCountries As IList(Of Country) = New List(Of Country)
    Dim countryDao As ICountryDao = DaoFactory.GetCountryDao
    returnCountries.Add(countryDao.GetById(1, False)) 'Germany
    returnCountries.Add(countryDao.GetById(1039, False)) 'Canada
    Return returnCountries
  End Function

With the code snippet above, only data is shown in the Cube, that is related to either Germany or Canada in some way.

When I now drag the regions dimension to the Row Elements and click on the dimension to filter the dimension elements all regions with all countries are shown.

The question is:

Is there a way to show only some of the dimension elements? In the case described above I would like to show only Germany and Canada and the related regions in the member editor window...

Best regards 

Sven Grundmann

[Ramesh Govindaraj]

Hi Sven,   

We suggest that you use “Roles” property in connection string to achieve your requirements. Role will give permission to display the desired members in OlapClient.   

Please find the below links which illustrate how to configure the “Roles” property in SSAS for OLAP Cube.   

Links:       

https://msdn.microsoft.com/en-us/library/ms174840.aspx  

https://msdn.microsoft.com/en-us/library/bb500251.aspx  

https://www.mssqltips.com/sqlservertip/2776/configuring-permissions-for-sql-server-analysis-services/  

   

Please find the syntax of the connection string to enable “Roles”.    

Connection String:   "Data Source = Server Name; Roles = Role Name"; Initial Catalog = Database Name;      

Regards,   

Ramesh G.   

 

[Sven Grundmann]

Hello Ramesh,

I was thinking of using roles, too, but for my requirement roles do not seem to be suitable. Let me give an example: 

Suggest, a user A is responsible for Germany and Switzerland, another user B is responsible for France and Spain. For this case i would need two roles to cover permissions for each user. Next year, user permissions change. User A gets responsibility for Switzerland and France, user b for Spain only, and user c is assigned to Germany. One could argue, that we could solve this by introducing one role for each country (which would mean a huge administrative effort), but if data is supposed to be shown according to a products hierarchy I would get in trouble because I do not know in advance which products will exist in the future. Once the analysis Services Cube is deployed to its productive environmnent, there will be no option to modify the Cube definition, so we won't be able to add extra roles for new products. Additionaly this will end up in a huge amount of roles...

So the question is, if there is a simpler way to restrict dimension data to spefific entries?

Thank you and best regards

Sven Grundmann

[Ramesh Govindaraj]

Hi Sven, 

Based on your requirement, the best solution is to set the “Role” in OLAP Cube. Since it’s not possible at your end, you can utilize the below work-around to achieve the same at control level.  

In the below example, we have excluded the members “Canada” and “France” on “TreeNodeDropped” action for different users. Likewise you can set the excluding logic separately for the different users based on your requirement.  

Code: 

C#(Inside Service): 

Public Function NodeDropped(ByVal action As String, ByVal dropType As String, ByVal nodeInfo As String, ByVal olapReport As String, ByVal clientReports As String) As Dictionary(Of String, Object) Implements IOlapClientService.NodeDropped 

        Dim DataManager As New OlapDataManager(connectionString) 

        DataManager.SetCurrentReport(OLAPUTILS.Utils.DeserializeOlapReport(olapReport)) 

        DataManager.Reports = olapClientHelper.DeserializedReports(clientReports) 

 

        'Temporary Solution  

 

        Dim dict As Dictionary(Of String, Object) = olapClientHelper.GetJsonData(action, DataManager, dropType, nodeInfo) 

        Dim nodeInfomation = nodeInfo.Split(New String() {"-"}, StringSplitOptions.RemoveEmptyEntries) 

 

        If dropType.Equals("TreeNode") AndAlso nodeInfomation(1).Equals("[Customer]") Then                'You can set the dimension name here to exclude its members 

            Dim currentReport = OLAPUTILS.Utils.DeserializeOlapReport(dict("UpdatedReport").ToString()) 

            Dim reportCollection = olapClientHelper.DeserializedReports(dict("ClientReports").ToString()) 

            currentReport = FilterReport(nodeInfo, currentReport) 

            For i As Integer = 0 To reportCollection.Count - 1 

                If reportCollection(i).Name.Equals(currentReport.Name) Then 

                    reportCollection(i) = currentReport 

                    Exit For 

                End If 

            Next 

            dict("ClientReports") = Syncfusion.Olap.Common.Common.SerializeObject(Of OlapReportCollection)(reportCollection).Compress() 

            dict("UpdatedReport") = OLAPUTILS.Utils.SerializeOlapReport(currentReport) 

        End If 

        Return dict 

    End Function 

 

    Public Function FilterReport(nodeInfo As String, currentReport As OlapReport) As OlapReport 

        Dim nodeInfomation = nodeInfo.Split(New String() {"-"}, StringSplitOptions.RemoveEmptyEntries) 

        Dim axis = nodeInfomation(2).ToLower() 

 

        If userName.Equals("abc") Then 

            ' for user "abc" we have exclude the member "Canada" from the dimension "Customer". 

 

            If axis = "categorical" Then 

                For Each item In currentReport.CategoricalElements 

                    If (TypeOf item.ElementValue Is DimensionElement) AndAlso TryCast(item.ElementValue, DimensionElement).UniqueName.ToLower() = nodeInfomation(1).ToLower() Then 

                        Dim excludeDimensionElement As New DimensionElement() 

                        excludeDimensionElement.Name = item.ElementValue.Name 

                        excludeDimensionElement.AddLevel(TryCast(item.ElementValue, DimensionElement).HierarchyName, TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name) 

                        excludeDimensionElement.Hierarchy.LevelElements(TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name).Add(New MemberElement() With { _ 

                             .Name = "Canada", _ 

                             .UniqueName = "[Customer].[Customer Geography].[Country].&[Canada]" _ 

                        }) 

                        item.ExcludedElementValue = excludeDimensionElement 

                    End If 

                Next 

            ElseIf axis = "series" Then 

                For Each item In currentReport.SeriesElements 

                    If (TypeOf item.ElementValue Is DimensionElement) AndAlso TryCast(item.ElementValue, DimensionElement).UniqueName.ToLower() = nodeInfomation(1).ToLower() Then 

                        Dim excludeDimensionElement As New DimensionElement() 

                        excludeDimensionElement.Name = item.ElementValue.Name 

                        excludeDimensionElement.AddLevel(TryCast(item.ElementValue, DimensionElement).HierarchyName, TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name) 

                        excludeDimensionElement.Hierarchy.LevelElements(TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name).Add(New MemberElement() With { _ 

                             .Name = "Canada", _ 

                             .UniqueName = "[Customer].[Customer Geography].[Country].&[Canada]" _ 

                        }) 

                        item.ExcludedElementValue = excludeDimensionElement 

                    End If 

                Next 

            End If 

        ElseIf userName.Equals("xyz") Then 

 

            ' for user "xyz" we have exclude the member "France" from the dimension "Customer". 

 

            If axis = "categorical" Then 

                For Each item In currentReport.CategoricalElements 

                    If (TypeOf item.ElementValue Is DimensionElement) AndAlso TryCast(item.ElementValue, DimensionElement).UniqueName.ToLower() = nodeInfomation(1).ToLower() Then 

                        Dim excludeDimensionElement As New DimensionElement() 

                        excludeDimensionElement.Name = item.ElementValue.Name 

                        excludeDimensionElement.AddLevel(TryCast(item.ElementValue, DimensionElement).HierarchyName, TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name) 

                        excludeDimensionElement.Hierarchy.LevelElements(TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name).Add(New MemberElement() With { _ 

                             .Name = "France", _ 

                             .UniqueName = "[Customer].[Customer Geography].[Country].&[France]" _ 

                        }) 

                        item.ExcludedElementValue = excludeDimensionElement 

                    End If 

                Next 

            ElseIf axis = "series" Then 

                For Each item In currentReport.SeriesElements 

                    If (TypeOf item.ElementValue Is DimensionElement) AndAlso TryCast(item.ElementValue, DimensionElement).UniqueName.ToLower() = nodeInfomation(1).ToLower() Then 

                        Dim excludeDimensionElement As New DimensionElement() 

                        excludeDimensionElement.Name = item.ElementValue.Name 

                        excludeDimensionElement.AddLevel(TryCast(item.ElementValue, DimensionElement).HierarchyName, TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name) 

                        excludeDimensionElement.Hierarchy.LevelElements(TryCast(item.ElementValue, DimensionElement).Hierarchy.LevelElements(0).Name).Add(New MemberElement() With { _ 

                             .Name = "France", _ 

                             .UniqueName = "[Customer].[Customer Geography].[Country].&[France]" _ 

                        }) 

                        item.ExcludedElementValue = excludeDimensionElement 

                    End If 

                Next 

            End If 

        End If 

        Return currentReport 

    End Function 

Regards,
Ramesh G.