'Show All' in a parameter box

Say you want to use a parameter in your report with, for example, the names of the countries where you have customers. You will often want a choice in your parameter box that says ‘Show All’, in this case all countries. The solution is as follows. Create a dataset in your report

1
SELECT ' _Toon Alles' AS LandNaam,
2
9999999 AS LandID
3
UNION ALL
4
SELECT DISTINCT
5
LandNaam,
6
LandID
7
FROM DimLand AS DL
8
ORDER BY LandNaam

We now have a list with all unique countries from our country table. As the first country we have the country ’ _Toon Alles’. It will act as the parameter that shows all countries. Note the space at the start. It makes sure this one ends up at the top.

**
Create the parameter
**Go to your report parameters and create the following parameter:

Dataset for use in the data region of your report
Finally, the dataset that feeds the report needs something extra in the WHERE clause, so that all places are shown when ’ _Toon Alles’ is chosen!

1
SELECT *
2
FROM DimKracht
3
WHERE(@GeselecteerdeLandID = 9999999 OR LandID = @GeselecteerdeLandID)

By default the parameter has the value 9999999. This is ‘Show all’. This is what we do above in our main dataset: check if the parameter is 9999999. If it is, it stops and shows everything. If it is not equal to 9999999, it automatically goes to the OR part and checks the parameter that was entered.