O'Reilly logo

SQL Server 2012 Reporting Services Blueprints by Mickey Stuewe, Marlon Ribunal

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The ALL parameter

There are other options on how to add ALL as a variable to return the whole dataset in a single parameter. Normally, you would do this on the dataset query:

SELECT 'ALL' as col1, NULL as col2, NULL, as col3
UNION ALL
SELECT col1, col2, col3
FROM…

Then allow the parameter to accept NULL.

You can also do what we've done here.

Take a look at the WHERE clause of our stored procedure dbo.uspSalesPersonSalesByYear.

WHERE ('ALL' IN (@SalesTerritory)) OR (pvt.SalesTerritory IN (@SalesTerritory))

That's it.

Note

If you use the ALL parameter in combination with a multivalued parameter, you need to use the MultiValueParamSplit function as explained in Chapter 1, Let's Breakdown the Numbers.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required