CODEWITHSUNDEEP
ssrs-2008-r2
Thomas Pollack
Thomas Pollack

Reputation: 59

SSRS - Can I hardcode Select ALL for multivalue parameter

I'm facing a known problem with default values for a cascading parameters. Since my parameters are coded in MDX I cannot find any specifc idea for a workaround.

Rather than trying to change a parameter query, I thought to check if there is a possibility of hard coding SELECT ALL for a cascading, or any parameter for that matter.

This is my Parameter dataset:

WITH 
    MEMBER [Measures].[ParameterValue] AS [Organisation].[Cost Centre Code and Description].CURRENTMEMBER.UNIQUENAME 
SELECT  
    [Measures].[ParameterValue]
ON COLUMNS , 
[Organisation].[Cost Centre Code and Description].[Cost Centre Code and Description].ALLMEMBERS ON ROWS 
FROM 
(SELECT STRTOSET(@CC_Caption, CONSTRAINED) ON COLUMNS FROM 
[CUBE] )

Would anyone have an idea how to hardcode SELECT ALL for an SSRS parameter?

Thanks for any points :)

Upvotes: 0

Views: 1337

Answers (2)

thomas
thomas

Reputation: 2642

It can be done in Custom Code. Not straightforward though. I have used in when using SharePoint list as a source. Should be applicable here. I will use a sample "Client" parameter.

  1. Create a parameter @ClientHelper. Set it to hidden, multi-value, available values come from your dataset query and default values from your dataset query as well.
  2. Create a parameter @Client. Set it to visible, not multi value, and available values of "specify values". Leave the label column blank and put in "=Split(Code.RemoveDups(JOIN(Parameters!ClientHelper.Value, “|”)), “|”)" as the values expression. You can set the default value to whatever you want.
  3. Note that @ClientHelper has to come before @Client in the list of parameters.

The custom cod you need is below. You can perhaps change it to meet your need if you don't need to remove dups.

Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()

Dim SpStr as String()
SpStr = Split(items ,"|")

For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Replace("&","&").Trim()) Then
noDups.Add(SpStr(i).Replace("&","&").Trim())
End If
Next

Dim uniqueItems As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueItems)
uniqueItems.Sort(uniqueItems)

Dim strRetValue AS String = String.Join("|", uniqueItems)
strRetValue = "All|" + strRetValue
Return strRetValue
End Function

Now you will still need to have the filters and logic in place in your dataset queries that do the cascading...but this gives you the "ALL" option.

Upvotes: 1

Neil
Neil

Reputation: 919

Can you not go to default values for the parameter and use "Get values from a query" and then supply a query that has all of the values?

Upvotes: 0

Related Questions