SSRS: Allow NULL in parent parameter only when child parameter is also NULL

Question

I have a report with parameters @Region and @Store as drop-down selections. The selections for @Store will filter according to the value selected for @Region. NULL for either parameter represents ALL records (no filters applied).

The challenge is that I don't want the user to be able to select @Store without specifying @Region first. However, if BOTH are NULL (ALL Stores in ALL Regions), that's ok.

Another way of putting it:

ACCEPTABLE:

@Region NOT NULL and @Store NOT NULL (a selected store in a selected region)

@Region NOT NULL and @Store NULL (all stores in a selected region)

@Region NULL and @Store NULL (all stores in all regions)

UNACCEPTABLE:

@Region NULL and @Store NOT NULL (a selected store with NO region selected)

Any ideas?

Answer 1

I would handle this at the SQL query level and make it clear in the prompts what is expected of the user. Such as where ... and not (@Region is null and @store is not null).

Answer 2

You have to populate the @Store parameter based on @Region parameter

If Regions and Store are related somehow you can do something pretty similar to the below example.

Example:

I have @ProductCategory and @ProductSubCategory SSRS parameters. I populate these parameter like this:

Available values for @ProductCategory set to this query:

select ProductCategoryKey,EnglishProductCategoryName from DimProductCategory

Available values for @ProductCategory @ProductSubCategory:

select ProductSubcategoryKey,EnglishProductSubcategoryName
from DimProductSubcategory where ProductCategoryKey = @CategoryID

Just map the parameter of the query to the report parameter

Then set the default values for both parameters to a value that you can catch and handle for @Store and @Region null case (when you show all stores and regions).

Let me know if this was helpful