Add : "All " in Cascading parameters

  • I am trying to add " All" in the parameter field and so

    wrote the following query for the 1st parameter

    SELECT DISTINCT TerritoryID

    FROM Sales.Customer

    UNION

    SELECT ' All' AS TerritoryID

    FROM Sales.Customer

    ORDER BY TerritoryID

    But it gives me a conversion error for converting Varchar value "All" to data type int.

    territoryId is Int. n All is varchar. So , I added an expression in Parameters Cstr().

    But its still showing the same error.

    Kindly advise

  • convert(varchar,columname) helped in SSRS.

    But Can I use All for two parameters?

  • Taking the original query as below the TerritoryID is numeric. The workaround you suggested with convert(varchar(xx),TerritoryID) would work but may cause issues with sorting the 'ALL' into the correct position in the parameter list.

    SELECT DISTINCT TerritoryID

    FROM Sales.Customer

    UNION

    SELECT ' All' AS TerritoryID

    FROM Sales.Customer

    ORDER BY TerritoryID

    Usually the territory will have a territory table (i.e. 1:M relationship to Sales.Customer). This table may hold a name for the territory as well as the ID.

    Choice 1 : Show all territories regardless of whether any sales customers exist. The query below would return the parameter list with the TerritoryID, Name and sortorder which is used to force the '-ALL-' to the top of the list.

    SELECT TerritoryID, TerritoryName, 1 as SortOrder

    FROM Sales.SalesTerritory

    UNION

    SELECT -99, '-ALL-', 0

    ORDER BY SortOrder, TerritoryName

    Choice 2 : Show only territories where sales customers exist. The query below would return the parameter list with the TerritoryID, Name and sortorder which is used to force the '-ALL-' to the top of the list.

    SELECT ST.TerritoryID, ST.TerritoryName, 1 as SortOrder

    FROM Sales.SalesTerritory as ST

    WHERE EXISTS (

    SELECT TerritoryID FROM Sales.Customers as SC WHERE ST.TerritoryID = SC.TerritoryID

    )

    UNION

    SELECT -99, '-ALL-', 0

    ORDER BY SortOrder, TerritoryName

    ----------------------------------

    A similar technique can be used for NULL values.

    SELECT TerritoryID, TerritoryName, 2 as SortOrder

    FROM Sales.SalesTerritory

    UNION

    SELECT -99, '-ALL-', 0

    ORDER BY SortOrder, TerritoryName

    UNION

    SELECT -98, '-NULL-', 1

    ORDER BY SortOrder, TerritoryName

    This would lead the report query to have :

    SELECT *

    FROM Sales.Customer

    WHERE

    (TerritoryID = @TerritoryID OR @TerritoryID = -99)

    OR

    (TerritoryID IS NULL AND @Territory = -98)

    ----------------------------------

    You can use the '-ALL-' in cascaded parameters.

    Example:

    Show all selected products where the subcategory (whether specific or all) leads to the colors available then both parameters are used in the report query.

    (Parameter) Subcategory ... (Parameter) Color ... (Report) Products

    1. Subcategory_Parameter_DataSet

    SELECT Name, SubCategoryID, 1 as SortOrder

    FROM Production.ProductSubcategory

    UNION

    SELECT '-ALL-',-99,0

    ORDER BY SortOrder, SubcategoryID

    2. Color_Parameter_DataSet

    SELECT DISTINCT Color, 1 as SortOrder

    FROM Production.Product

    WHERE ProductSubcategoryID = @Subcategory

    UNION

    SELECT '-ALL-', 0

    ORDER BY SortOrder, Color

    3. ProductList_Report_DataSet

    SELECT *

    FROM Production.Product

    WHERE

    (ProductSubcategoryID = @Subcategory OR @Subcategory = -99)

    AND

    (Color = @Color OR @Color = '-ALL-)

    Hope this helps.

    Fitz

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply