SSRS Report Not Returning All Results When Input Parameter is Blank

SSAS and SSRS
1

I am designing a report based off of a stored procedure with the following variables. (@UserID, @MonthBegin, @MonthEnd) The Dataset Properties use a dataset embedded in my report and call a stored procedure via Query Type Text:

exec prcStoredProcedureName @UserID, @MonthBegin, @MonthEnd

For the @UserID Report Parameter Properties, I have Allow blank value ("") selected.

When I run the report using the Preview tab and enter a specific @UserID @MonthBegin, and @MonthEnd, it works fine and shows all data for that particular @UserID.

However, I would like to have the option of either entering a specific @UserID, @MonthBegin, @MonthEnd or, I would like to be able to leave the @UserID blank and just enter the @MonthBegin, @MonthEnd and have the report display all @UserID's within the specified @MonthBegin, @MonthEnd parameters that were entered.

Currently when I leave the @UserID blank, and just enter the @MonthBegin, @MonthEnd, it is just returning a blank report.

Any help would be greatly appreciated.

I have tried numerous changes to the Data Set Properties and Parameter Properties and have also looked all over the internet to see if I can find some related resources. I was expecting to find a tutorial with specifics, but I have yet to find any specific resources.

2

It's not complicated, you can use the WHERE statement. It would be something like this:

WHERE (yourtable.UserID=@UserID OR ISNULL(@Userid,'')='') AND MonthBegin=@MonthBegin AND MonthEnd=@MonthEnd.

Maybe you should check the parameter in SSRS if blank value is checked.

SSRS Reports With Optional Query Parameters (mssqltips.com)