I am trying to implement slicer in SSRS with cascading parameters using AdventureWorks2012 ( http://msftdbprodsamples.codeplex.com/downloads/get/165399 ). There are three data sets: one (SalesbyCategory) for the report, others for the parameters.
Here is the query: SalesbyCategory:
SELECT PC.Name AS Category, PSC.Name AS Subcategory, P.Name AS Product
FROM Production.Product AS P INNER JOIN
Production.ProductSubcategory AS PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID INNER JOIN
Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
WHERE (PC.Name IN (@Category)) AND (PSC.Name IN (@Subcategory))
CategoryValues:
SELECT DISTINCT Name AS Category
FROM Production.ProductCategory
SubcategoryValues:
SELECT DISTINCT PSC.Name AS Subcategory
FROM Production.ProductSubcategory AS PSC INNER JOIN
Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
WHERE (PC.Name IN (@Category))
for inverse parameter:
Function parameterInverse(ByVal paramsArray As String(), ByVal parameter As String) As String()
For i As Integer = 0 To paramsArray.Length - 1
If paramsArray(i).Equals(parameter) Then
paramsArray(i) = paramsArray(paramsArray.Length - 1)
ReDim Preserve paramsArray(paramsArray.Length - 2)
Exit For
ElseIf i = paramsArray.Length - 1 Then
ReDim Preserve paramsArray(paramsArray.Length)
paramsArray(paramsArray.Length - 1) = parameter
End If
Next
Return paramsArray
End Function
text field action: Category
=Code.parameterInverse(Split(Join(Parameters!Category.Value,","),","),Fields!Category.Value)
Subcategory = [@Subcategory]
to show if the option is selected:
=iif(Join(Parameters!Subcategory.Value,",").Contains(Fields!Subcategory.Value),"Turquoise","LightGrey")
When I click the Category text box, an error has occurred
missing subcategory option

Why? Or should I change my mind?