1

Recently started learning Reporting Services using Microsoft SQL Server 2016 Reporting Services, Fifth Edition, Brian Larson. I am creating this one report which uses UNION to create a dataset, FuelType, that will be used for assigning values to a dropdown list.

SELECT 'All' AS Fueltype, '_All' AS SortField
UNION
SELECT Description, Description FROM Propulsion ORDER BY SortField

Now there exists another dataset that will help generate the chart for me and there.

SELECT Propulsion.Description AS Fueltype, FuelPrice.PriceStartDate, FuelPrice.Price    
FROM  FuelPrice 
INNER JOIN Propulsion ON FuelPrice.PropulsionID = Propulsion.PropulsionID
WHERE        (YEAR(FuelPrice.PriceStartDate) = @year) AND  (Propulsion.Description = @PropulsionType) 
OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')
ORDER BY Fueltype, FuelPrice.PriceStartDate

I do not understand, how by using "@PropulsionType = 'All'" I am able to pass in all available values to the parameter.

enter image description here

For someone who has the book, this is under Chapter 6 :Fuel Price Chart, Version 2

1
1

SSRS isn't doing anything special. It's actually the query that's making the distinction to either pull all fuel types or just the one specified, though I suspect the book has a typo because it looks to be missing an extra pair of parenthesis. The real magic is supposed to be happening in the predicate, here:

WHERE  (YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType) 
   OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')

This really should be written in one of the two following ways:

WHERE  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (Propulsion.Description = @PropulsionType))
   OR  ((YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All'))

Or, another way:

WHERE (YEAR(FuelPrice.PriceStartDate) = @year) 
 AND  ((Propulsion.Description = @PropulsionType) 
        OR  (@PropulsionType = 'All'))

Basically what this is doing is in the event your report is passed the 'All' parameter, don't filter on Propulsion.Description. If a parameter is passed that is not 'All', you want to filter on that specific value. Taking SSRS out of it, you can see the working example via this dbfiddle.uk link.

1
  • Thank you , I was in a tunnel vision and couldn't grasp the usage. I now understand that using the string as a filter that is not present in the column will basically mean, "filter on nothing"! P.S.: The typo is not in the book, but at my end, while typing out the query while posting the question I made that mistake. Jun 11 '19 at 16:56

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.