Wednesday, July 15, 2015

MDX and Reporting Services: How to remove duplicates from a parameter?

What if you get duplicate values from a dimension that is going to be used for loading a Reporting Services parameter? Generally, you do not get duplicates from a dimension because we do not configure the dimension like that. For an example, we do not expect duplicate names from Product dimension. But there can be an exception that shows duplicate names but unique keys, something like below;

Id Name ...
1 Product A ...
2 Product B ...
3 Product A ...
4 Product C ...

If Id of this has been used as the key and name has been used as the name of the key, even though we write a MDX query like below, we still get duplicates as they have unique keys.

SELECT 
  {  } ON COLUMNS, 
  { Distinct([Product].[Product].MEMBERS) } ON ROWS 
FROM [Cube];

Best way is, fix this in database level without taking it to reporting level. But if we cannot do anything at database level (assume that no permission, have to use as it is) and still should not show duplicates with the parameter, what is the best way?

I have solved this in two ways. One is, execute the MDX from the database engine, connecting with OLAP database using a linked server. In this case, TSQL SELECT statement will be used and source for the parameter will be database engine, not OLAP database.

SELECT
 DISTINCT [Measures.ProductName]
FROM OpenQuery(LinkedServerForOLAP,
 'WITH 
 MEMBER [Measures.ProductName] AS [Product].[Product].CurrentMember.Name
 SELECT 
    { [Measures.ProductName] } ON COLUMNS, 
    { Distinct([Product].[Product].MEMBERS) } ON ROWS 
  FROM [Cube]')

Other method is, write VB.Net code inside the report for removing duplicates. This requires a hidden parameter that holds data coming from the OLAP database and another new parameter for showing values. New parameter has to be loaded from a function written in the Code segment that accesses the hidden parameter for getting values, programatically removes duplicates and returns distinct values.

Here is the second way;

First create a hidden parameter. Make sure it is set as hidden.


Then set Available Values and Default Values with the data set created based on OLAP database.



First part is done. Then implement the following code in Code segment.

Function GetDistinctLabels(parameter As Parameter) As String()


 Dim labels As Object() = parameter.Label
 System.Array.Sort(labels)

  Dim x As Integer = 0

 For i As Integer = 0 to labels.Length - 1

  If i > 0 And labels(i).Equals(labels(i - 1)) Then
   
   Continue For
  End If
  labels(x) = labels(i)
  x += 1
 Next

  Dim distinctlabels As [String]() = New [String](x - 1) {}
 System.Array.Copy(labels, 0, distinctlabels, 0, x)

  Return distinctlabels

End Function


After that, load the main parameter that is used for showing values as below;

Set the Available Values using Specify values and call the function written for loading distinct values passing parameter name. Do it for both Label and Value.


Set Default Values using same technique if required.


That is all. I have used both methods and both worked fine. However if keys of these labels have to be passed to another dataset loading more data, additional work is required for maintaining keys of the parameter.

5 comments:

  1. The following error is while running the report

    The ValidValue expression for the report parameter ‘TM’ contains an error: Unable to cast object of type 'System.Object[]' to type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter'. (rsRuntimeErrorInExpression)

    please let me know

    ReplyDelete
  2. Hi Varun,

    How did you write the expression for the second parameter? It should be written as Code.GetDistinctLabels(Parameters!ReportParameter1). Do not use "value" property.

    If you still have the issue, can you let me know more details on it please.

    ReplyDelete
  3. HI Dinesh,

    I removed the .Value property from parameter and rewrote the same as =Code.GetDistinctLabels(Parameters!remdupTM). Now I am getting the below error

    "The ValidValue expression for the report parameter ‘TM’ contains an error: Index was outside the bounds of the array. (rsRuntimeErrorInExpression)

    Please let me know in case you need more details. If possible can you please upload the .rdl for the report you created?

    Thanks
    Varun

    ReplyDelete
  4. I am also getting the same error .

    @Varun -- Were you able to resolve the issue ?

    ReplyDelete
  5. Hi,
    You can use the below method to remove duplicates.

    1. Copy the custom code to your report.


    Public Shared Function RemoveDups(ByVal items As String) As String
    Dim noDups As New System.Collections.ArrayList()
    Dim SpStr
    SpStr = Split(items ,"|")
    For i As Integer=0 To Ubound(Spstr)
    If Not noDups.Contains(SpStr(i).Trim()) Then
    noDups.Add(SpStr(i).Trim())
    End If
    Next
    Dim uniqueItems As String() = New String(noDups.Count-1){}
    noDups.CopyTo(uniqueItems)
    Array.Sort(uniqueItems)
    Return String.Join("|", uniqueItems)
    End Function

    This will remove duplicates and also order the values in ascending order

    2. Create another parameter that will be used for filtering the maindata.

    3. Please set the available value to be
    =Split(Code.RemoveDups(JOIN(Parameters!name.Value, ",")), ",")

    And the default value to be the value you what such as the first value:
    =Split(Code.RemoveDups(JOIN(Parameters!name.Value, ",")), ",")

    And then use the newly created parameter to filter the data.

    ReplyDelete