Slicer implementation in SSRS with cascading parameters

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

enter image description here

Why? Or should I change my mind?

+4
source share

Source: https://habr.com/ru/post/1619774/


All Articles