Reporting Services Optional Multi-Valued Parameters with Analaysis Services as the source

Creating multi-valued parameters with reports is a common requirement and it can be easily done with Reporting Services, making Allow multiple values property enabled with the report. However, if you need to make it as optional, allowing user to continue with the report without selecting any item in the parameter, you will face an issue because Reporting Services does not allow user to continue without selecting an item if the parameter is multi-valued parameter. Not only that, you cannot enable Allow null value property too.

If your source for the parameter is SQL Server database, workaround is, adding a NULL value to the dataset manually (or as a part of the query) and use it for handling optional requirement. If you google, you can find the way of implementing this workaround in many forums and sites. What if the source for the parameter is Analysis Services, then this workaround does not work as it is, it needs some extra work.

Here is the way I implemented, it may not be the perfect method but it worked fine for me.

Assume that I need to load Products for a parameter and make it as multi-valued parameter, my MDX for that will be something like below;

WITH 
MEMBER [Measures].[Product Name]
 AS [Product].[Product].CurrentMember.Name
MEMBER [Measures].[Product Unique Name]
 AS [Product].[Product].CurrentMember.UniqueName
SELECT
 { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
 , {[Product].[Product].[Product].Members } ON 1
FROM [Adventure Works];

However, I cannot make the parameter as optional because of the limit, hence I need a member that represents NULL. For that, I need to introduce query-scoped calculated member and add it to the resultset;

WITH 
MEMBER [Measures].[Product Name]
 AS [Product].[Product].CurrentMember.Name
MEMBER [Measures].[Product Unique Name]
 AS [Product].[Product].CurrentMember.UniqueName
MEMBER [Product].[Product].[All Products].[(NULL)]
 AS Aggregate([Product].[Product].&[(NULL)])
SELECT
 { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
 , {[Product].[Product].[Product].AllMembers } ON 1
FROM [Adventure Works];

Now just like parameters loaded from SQL Server databases, I have a (NULL) item in the parameter for handling the situation. If you get an issue with sorting the result that MDX produces, alternative way for that is, executing the MDX using a TSQL Stored Procedure connecting to Analysis Services via a Linked Server object.

CREATE PROC dbo.GetProductsFromSSAS
AS
BEGIN

 DECLARE @Query nvarchar(max)
 
 SET @Query = N'
  SELECT 
   "[Measures].[Product Name]"
   , "[Measures].[Product Unique Name]"
  FROM 
   OpenQuery(OLAPDatabase, ''
     WITH 
     MEMBER [Measures].[Product Name]
      AS [Product].[Product].CurrentMember.Name
     MEMBER [Measures].[Product Unique Name]
      AS [Product].[Product].CurrentMember.UniqueName
     MEMBER [Product].[Product].[All Products].[(NULL)]
      AS Aggregate([Product].[Product].&[(NULL)])
     SELECT
      { [Measures].[Product Name], [Measures].[Product Unique Name] } ON 0
      , {[Product].[Product].[Product].AllMembers } ON 1
     FROM [Adventure Works];
    '')
  ORDER BY 1;'
 
 EXEC SP_EXECUTESQL @Query;
 
END
GO