0

I have SSRS Report that is based on user's decision to bring data from selected Procedure.

I have 5 parameters:

@Country, @StartDate, @EndDate, @MinLenght, MaxLenght

When user Selects Just one value we have report like this [![enter image description here][1]][1]

It's my first try. As you can see there is Country Parameter called France, When I try to choose 3 country like

France, Germany, Italy

Builder gives me an error such as: [![enter image description here][2]][2]

I have DataSet's for each Parameter, If any other query needed in my project I can update it, Here is my Procedure for Report Body

ALTER PROC [dbo].[GET_SalesByCountryTier1]
(
@Country AS VARCHAR(30),
@Startdate AS DateTIME,
@EndDate AS Datetime,
@MinLenght as INT,
@MaxLenght as INT

)



AS
BEGIN

SELECT 
(SELECT COUNT(1) FROM [DBCenter].[dbo].[VW_FactInternetSales] 
    WHERE Country IN (@Country) AND Orderdate BETWEEN @Startdate AND @EndDate) AS SalesCount,
(SELECT SUM(TaxAmt) FROM [DBCenter].[dbo].[VW_FactInternetSales] 
    WHERE Country = @Country AND Orderdate BETWEEN @Startdate AND @EndDate) AS TotalTaxAmt,
(SELECT AVG(ProductStandardCost) FROM [DBCenter].[dbo].[VW_FactInternetSales]
    WHERE Country = @Country AND Orderdate BETWEEN @Startdate AND @EndDate) AS Cost,
(SELECT AVG(TaxAmt) FROM [DBCenter].[dbo].[VW_FactInternetSales] 
    WHERE Country = @Country AND Orderdate BETWEEN @Startdate AND @EndDate) AS AvgTax,
(SELECT AVG(ProductStandardCost) FROM DBCenter.dbo.VW_FactInternetSales 
    WHERE Country = @Country AND OrderDate BETWEEN @Startdate and @EndDate AND ProductStandardCost >= @MinLenght AND ProductStandardCost < @MaxLenght ) AS MinLenght

END

UPDATE

My Code in DataSet for Parameter: SELECT DISTINCT Country FROM DBCenter.dbo.VW_FactInternetSales WHERE Country IN ('France', 'Germany', 'Italy') [1]: https://i.stack.imgur.com/G4y5Q.png [2]: https://i.stack.imgur.com/hTWzv.png

7
  • Any chance you use your County report parameter in an SSRS expression somewhere in the report?...or anything similar on the report side?
    – J.D.
    Oct 9 '21 at 21:19
  • I have @Country in My DataSet Query, is that what you mean?
    – Hamza
    Oct 10 '21 at 10:42
  • No I mean do you use it anywhere else in your report with additional logic? This is what expressions are in SSRS.
    – J.D.
    Oct 10 '21 at 12:57
  • Also can you please post screenshots of your Report Parameters Properties? It's possible the issue is somewhere in how those are configured, but not sure.
    – J.D.
    Oct 10 '21 at 13:02
  • 1
    @J.D. I have edited my Question, You can see it in there as image
    – Hamza
    Oct 10 '21 at 18:52
1

I think your problem is in the = operator before: @Country.

When you select one Country everything works fine because Country = Italy, but when you select more than one country Country = France, Germany, Italy, the operator = doesn't work. You need to change = with in, Country in ('France', 'Germany', 'Italy'), and I think it will work fine.

Another suggestion:

@Country AS VARCHAR(30) --- the datatype length is too short for three or more countries.

4
  • I tried your answer it works but I am getting only the country I declare, This is how I execute my procedure: Code DECLARE @RC int DECLARE @Country varchar(30) DECLARE @Startdate datetime DECLARE @EndDate datetime DECLARE @MinLenght int DECLARE @MaxLenght int EXECUTE @RC = [dbo].[GET_SalesByCountryTier1] @Country = 'France' ,@Startdate = '2010-12-29' ,@EndDate = '2014-01-28' ,@MinLenght = 10 ,@MaxLenght = 100
    – Hamza
    Oct 9 '21 at 19:29
  • And if you declare all the three Countries? Oct 9 '21 at 19:38
  • How am I Going to? When I try to write like EXECUTE @RC = [dbo].[GET_SalesByCountryTier1] @Country = 'France' , 'Germany', 'Italy' It does not accept it
    – Hamza
    Oct 9 '21 at 19:41
  • I don't believe this OP's problem per se but is a good guess anyway. OP's @Country parameter is a VARCHAR in his SQL code. If anything, SQL wouldn't give an error, rather he'd just get no results matching on the list of values he's selecting in the report parameters. Rather, the error he's getting appears to be happening on the client side in SSRS itself, possibly. Such as if he has an SSRS expression somewhere that uses his report parameter, and that expression expects a single value but breaks when a collection of values is selected. So I think you got the right problem but wrong place.
    – J.D.
    Oct 9 '21 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.