Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add : "All " in Cascading parameters Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 3:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:00 AM
Points: 243, Visits: 736
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

Post #1496148
Posted Wednesday, September 18, 2013 4:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:00 AM
Points: 243, Visits: 736
convert(varchar,columname) helped in SSRS.

But Can I use All for two parameters?
Post #1496159
Posted Thursday, September 19, 2013 2:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:09 PM
Points: 3,187, Visits: 1,392

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
Post #1496264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse