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.
