September 18, 2013 at 3:22 pm
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
September 18, 2013 at 4:01 pm
convert(varchar,columname) helped in SSRS.
But Can I use All for two parameters?
September 19, 2013 at 2:38 am
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