none
Report Parameters with a space between, not working

    Question

  • I have a problem with a report in SQL Server 2012 that uses cascading multi-valued parameters. I am using AdventureWorks2012DW as a sample, and all parameters work except for one with a space in this case its SalesTerritoryCountry and the parameter is 'North America' which has a space. How do i get it to return data? here is my Stpre procedure for you to try.

    Create Procedure dbo.uspReportParams
    (
    	@Region varchar(15),
    	@Country varchar(60)
    )
    AS
    BEGIN
    SET NOCOUNT ON
    Select st.SalesTerritoryGroup [Region], st.SalesTerritoryCountry [Country], r.BusinessType [Business Type], SUM(rs.SalesAmount) [Sales Amount]
    from DimReseller r join FactResellerSales rs on r.ResellerKey = rs.ResellerKey
    join DimDate d on rs.OrderDateKey = d.DateKey
    join DimProduct p on p.ProductKey = rs.ProductKey
    join DimSalesTerritory st on rs.SalesTerritoryKey = st.SalesTerritoryKey
    where st.SalesTerritoryGroup IN (Select [splitdata] FROM dbo.fnSplitString (@Region,', '))
    --AND st.SalesTerritoryGroup <> 'NA'
    AND st.SalesTerritoryCountry IN (Select [splitdata] FROM dbo.fnSplitString (@Country,', '))
    Group by st.SalesTerritoryGroup, st.SalesTerritoryCountry, r.BusinessType
    END
    

    IF i check it with other parameters it works except for North America

    EXEC dbo.uspReportParams @Region = 'Europe,Pacific,North America', @Country = 'Germany,France,United Kingdom,Australia,Canada'

    Sunday, August 24, 2014 4:31 PM

Answers

  • One question.

    why do you use ,<space> as delimiter inside Split function? As I see there's no space character after , in your delimited value list. So I think just , would be enough.

    Also one more thing to take care of is the length of the parameters @Region and @Country. you need to make it has sufficient length to hold the full string of values. As of now length given is too less


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 8:16 AM
  • Thanxs for the reply i will explain more. The Issue is on parameter region which is 'North America', it should return data for United states and Canada.... the spaces for the SalesTerritoryCountry dont matter but for the SalesTerritoryGroup. 

    I have made another discovery which has puzzled me more: If i place paramater 'North America' as the first value when executing the procedure, everything works well but if i place it elsewher ther is a problem here is a example to show what im saying

    EXEC dbo.uspReportParams @Region = 'North America,Europe,Pacific', @Country = 'Germany,France,United Kingdom,Australia,Canada'

    The results for Region 'North America' and Countries 'United States' + 'Canada' Show 

    Its working as expected

    You're using an AND condition in the filter so it will only return the countries which are included in @Country filter as it is the lower level

    I guess may be what you want is this??

    Create Procedure dbo.uspReportParams
    (
    	@Region varchar(15),
    	@Country varchar(60)
    )
    AS
    BEGIN
    SET NOCOUNT ON
    Select st.SalesTerritoryGroup [Region], st.SalesTerritoryCountry [Country], r.BusinessType [Business Type], SUM(rs.SalesAmount) [Sales Amount]
    from DimReseller r join FactResellerSales rs on r.ResellerKey = rs.ResellerKey
    join DimDate d on rs.OrderDateKey = d.DateKey
    join DimProduct p on p.ProductKey = rs.ProductKey
    join DimSalesTerritory st on rs.SalesTerritoryKey = st.SalesTerritoryKey
    where (st.SalesTerritoryGroup IN (Select [splitdata] FROM dbo.fnSplitString (@Region,', '))
    --AND st.SalesTerritoryGroup <> 'NA'
    OR st.SalesTerritoryCountry IN (Select [splitdata] FROM dbo.fnSplitString (@Country,', ')))
    Group by st.SalesTerritoryGroup, st.SalesTerritoryCountry, r.BusinessType
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 12:55 PM

All replies

  • Hi TheGuyFromMatare,

    Could you please elaborate a bit more on "not working"?

    Per my testing, the Stored Procedure(SP) and function fnSplitString works well.
    And in the sample you posted, "United Kingdom" has a space in it as well. It works. So, space won't be an issue in the function fnSplitString.

    If "not working" means no data return for country "North America", then it is because of there is no data for "North America" in DimSalesTerritory.

    Thanks,
    Jinchun Chen

    • Proposed as answer by Avijit Swain Monday, August 25, 2014 8:38 AM
    Monday, August 25, 2014 8:10 AM
    Moderator
  • One question.

    why do you use ,<space> as delimiter inside Split function? As I see there's no space character after , in your delimited value list. So I think just , would be enough.

    Also one more thing to take care of is the length of the parameters @Region and @Country. you need to make it has sufficient length to hold the full string of values. As of now length given is too less


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 8:16 AM
  • Thanxs for the reply i will explain more. The Issue is on parameter region which is 'North America', it should return data for United states and Canada.... the spaces for the SalesTerritoryCountry dont matter but for the SalesTerritoryGroup. 

    I have made another discovery which has puzzled me more: If i place paramater 'North America' as the first value when executing the procedure, everything works well but if i place it elsewher ther is a problem here is a example to show what im saying

    EXEC dbo.uspReportParams @Region = 'North America,Europe,Pacific', @Country = 'Germany,France,United Kingdom,Australia,Canada'

    The results for Region 'North America' and Countries 'United States' + 'Canada' Show 

    Monday, August 25, 2014 12:00 PM
  • Thanxs for the reply i will explain more. The Issue is on parameter region which is 'North America', it should return data for United states and Canada.... the spaces for the SalesTerritoryCountry dont matter but for the SalesTerritoryGroup. 

    I have made another discovery which has puzzled me more: If i place paramater 'North America' as the first value when executing the procedure, everything works well but if i place it elsewher ther is a problem here is a example to show what im saying

    EXEC dbo.uspReportParams @Region = 'North America,Europe,Pacific', @Country = 'Germany,France,United Kingdom,Australia,Canada'

    The results for Region 'North America' and Countries 'United States' + 'Canada' Show 

    Its working as expected

    You're using an AND condition in the filter so it will only return the countries which are included in @Country filter as it is the lower level

    I guess may be what you want is this??

    Create Procedure dbo.uspReportParams
    (
    	@Region varchar(15),
    	@Country varchar(60)
    )
    AS
    BEGIN
    SET NOCOUNT ON
    Select st.SalesTerritoryGroup [Region], st.SalesTerritoryCountry [Country], r.BusinessType [Business Type], SUM(rs.SalesAmount) [Sales Amount]
    from DimReseller r join FactResellerSales rs on r.ResellerKey = rs.ResellerKey
    join DimDate d on rs.OrderDateKey = d.DateKey
    join DimProduct p on p.ProductKey = rs.ProductKey
    join DimSalesTerritory st on rs.SalesTerritoryKey = st.SalesTerritoryKey
    where (st.SalesTerritoryGroup IN (Select [splitdata] FROM dbo.fnSplitString (@Region,', '))
    --AND st.SalesTerritoryGroup <> 'NA'
    OR st.SalesTerritoryCountry IN (Select [splitdata] FROM dbo.fnSplitString (@Country,', ')))
    Group by st.SalesTerritoryGroup, st.SalesTerritoryCountry, r.BusinessType
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 12:55 PM
  • Its solved it was the space that i had allocated to hold the string, dont know why i missed something minor, i changed it to vharchar(max) and everything works well even if North America is the last parameter
    Monday, August 25, 2014 1:15 PM