0

Today i have one problem with charts in SSRS as per below i want to generate filter on charts so for that i added one parameter(@SaleYearParameter) in dataset query.

 SELECT COUNT(*) AS SaleCount, pc.Name, YEAR(so.ModifiedDate) AS SaleYear
FROM     Sales.SalesOrderDetail AS so INNER JOIN
                  Production.Product AS po ON so.ProductID = po.ProductID INNER JOIN
                  Production.ProductSubcategory AS ps ON po.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory AS pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE  (YEAR(so.ModifiedDate) = @SaleYearParameter)
GROUP BY pc.Name, YEAR(so.ModifiedDate)

then i bind different values to parameter but filter is display in design section but it is not visible in preview tab.

Design: enter image description here

Preview:

enter image description here

as per above design tab have filter according to parameter but why preview don't have that filter i tried different expects but the same query working without chart filter and parameters.

Can any one have any suggestion on this?? Thanks in advance!!!!

Nikhil.Patel
  • 959
  • 9
  • 17
  • 1
    This might help you: http://stackoverflow.com/questions/38568734/using-parameters-in-visual-studio-2015-reporting-services/38578883#38578883 – R. Richards Aug 16 '16 at 13:45

3 Answers3

0

I can't give you a screen shot example but I'm guessing its either a) caching the results because ssrs does dumb stuff like that Or b) the parameter hasn't been defined properly.

Another thing I noticed in your query.

SELECT COUNT(*) AS SaleCount, pc.Name, YEAR(so.ModifiedDate) AS SaleYear
FROM     Sales.SalesOrderDetail AS so INNER JOIN
                  Production.Product AS po ON so.ProductID = po.ProductID INNER JOIN
                  Production.ProductSubcategory AS ps ON po.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory AS pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE  (YEAR(so.ModifiedDate) = @SaleYearParameter)
GROUP BY pc.Name, YEAR(so.ModifiedDate)

Maybe try the following because this is what I usually use and it hasn't caused me any trouble yet.

SELECT COUNT(*) AS SaleCount, pc.Name, YEAR(so.ModifiedDate) AS SaleYear
FROM     Sales.SalesOrderDetail AS so INNER JOIN
                  Production.Product AS po ON so.ProductID = po.ProductID INNER JOIN
                  Production.ProductSubcategory AS ps ON po.ProductSubcategoryID = ps.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory AS pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE  DatePart(YEAR,so.ModifiedDate) in (@SaleYearParameter)
GROUP BY pc.Name, DatePart(YEAR,so.ModifiedDate)

Most importantly on the left under parameters just check that your parameter is defined and hasn't get a default value

Dheebs
  • 398
  • 1
  • 6
  • 19
  • the hint about looking at parameter definition and setting value is good but the part about the changing the WHERE line of the query doesn't matter while the syntax is different the results/outcome will be the same as the logic is the exact same. – Matt Aug 16 '16 at 17:55
0

Make sure your Report Parameter is Visible.

  • View Report Data
  • Navigate to Paramters
  • double click on the parameter name and ensure Visible is selected.
Matt
  • 13,833
  • 2
  • 16
  • 28
0

I've seen some people with this issue and it seems to be a (sigh) new bug in SSRS.

Try restarting Visual Studio and re-opening your report.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39