We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
  1. Home
  2. Forum
  3. ASP.NET Web Forms
  4. FilterPopup empty on predefined Cube

FilterPopup empty on predefined Cube

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

5 Replies
SP Sastha Prathap Selvamoorthy Syncfusion Team June 22, 2016 05:21 AM UTC

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. 


Regards, 
Sastha Prathap S. 


SG Sven Grundmann June 22, 2016 11:57 AM UTC

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

RG Ramesh Govindaraj Syncfusion Team June 24, 2016 06:11 AM UTC

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:       
   
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.   
 


SG Sven Grundmann June 27, 2016 12:57 PM UTC

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

RG Ramesh Govindaraj Syncfusion Team June 28, 2016 12:26 PM UTC

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. 


Loader.
Up arrow icon