SQL Server Reporting Services (SSRS) 2005 has various limitations but it does have some work-around to perform certain tasks some may think is not possible. Here I am sharing one such experience which I hope will be useful to all SSRS developers out there.

Case In Point

Requirement was to display a pop-up message box if one of the user entered parameter was not between 1 & 25. As soon as user clicked the 'View Report' button, before the report is displayed, this pop-up Confirmation Dialogue box with 'Yes' & 'No' options should be displayed.

FYI: It is also possible to display an error message box, or just plain message box or Confirmation box with 'Yes', 'No' & 'Cancel' and such as you want.

That being said, let's assume we have a report which accepts a Parameter - an Integer from the user. If user enters a number less than 1 or more than 25, a pop-up message dialogue will appear asking user for confirmation. Something like - "You selected '30'. Are you sure you want to Continue?"

If user still decides to run the report and clicks "Yes", we should run the report. If user clicks "No", the report will not be displayed, instead a message box will be displayed asking user to correct their parameter supplied. In any case user enters a number between 1 and 25, it is valid and therefore no pop-up is displayed but just the report. You can of course change any of this functionality to suite your need.

Solution

Let's get started by creating a parameter, say a parameter name 'Number', of Integer type. Under its Default values check Non-queried and set a value of 1, you can modify according to your requirement.

Now let's create another parameter 'IsValidNum' of type Boolean. This will determine if the user entered number is between 1 and 25. Make this second parameter a Hidden parameter. Based on the value of this Hidden parameter IsValidNum, we will control what user gets to see.

The whole idea of building the report as a single control is to control what gets displayed based on our parameter - IsValidNum. Of course, you do not have to make it a single control, but in that case you will have to change the "Hidden" property of each and every control based on this parameter. Now let us put a Textbox outside our main control. This Textbox will display the message to the user saying something like "Please enter the number between 1 and 25". The Hidden property of this Textbox will be just opposite to that of our main control. So if the parameter 'IsValidNum' returns TRUE, this Textbox's Hidden property will be set to FALSE and that of the main control to TRUE. Thus showing the Textbox and hiding the report.

Let's name our textbox "MsgTxtBx".

Go to its properties > Visibility > Hidden and change the expression to:

         = Parameter!IsValidNum.Value

This should assure that you see your Report only when the user wants to or only if user selects the value between 1 and 25. In any other case, user gets a message asking to enter the number between 1 and 25. Of course based on our parameter – 'IsValidNum'.

Okay, so how do we decide the Value for 'IsValidNum' parameter?

It's simple right? If the user enter the value between 1 and 25 for the parameter 'Number' it is TRUE and for all others value it is FALSE.

Not exactly! Remember value of the number decides if a Message Box should be popped-up or not and then based on user's decision i.e. whether they click 'Yes' or 'No', 'IsValidNum' gets its value set. So let us now write a method to handle this functionality.

Our goal of this function would be to check the value of the parameter Number and if it is not between 1 and 25, display a pop-up message asking user if they want to continue running the report. If user clicks 'Yes' - we display the report, if 'No' - a message will be displayed asking user to simply enter the correct value.

FYI:We are displaying the message to enter the correct value in a Textbox here, but you could also display the same message in a pop-up instead.

To handle this, let us write a VB function.

Go to Report Properties and under the Code tab write the following VB function:

Function CheckNum(NumEntered as Integer) as Boolean
   Dim prompt as String
   Dim usrResponse As MsgBoxResult
   prompt = ""
   usrResponse = MsgBoxResult.No
   If (NumEntered > 25 OR NumEntered < 1) Then
     prompt = "You entered " & Str(NumEntered) _
                             & ". Are you sure you want to continue?"
   End If
   If prompt <> "" Then
     usrResponse = MsgBox(prompt,4,"Message Box Title" )
     If usrResponse = MsgBoxResult.Yes Then
                 Return TRUE
     Else                 
                 Return FALSE
      End If
   Else
   Return TRUE
   End If
End Function

This function accepts an Integer as a parameter and returns a Boolean value. Every time the value of the parameter is not between 1 & 25, it creates a String prompt which is just asking user for confirmation and the confirmation prompt is displayed on a pop-up message box. Based on whether user clicks 'Yes' or 'No', the function returns the Boolean value. The function returns 'TRUE' if its parameter value is between 1 & 25.

Now how do we trigger this function call?

This is the trickiest part. We want the pop-up before report is displayed. So this should be triggered with the parameter.

Let's go back to our hidden report parameter 'IsValidNum'. Under Default values, check Non-queried and edit the expression as:

=Code.CheckNum(Parameters!Number.Value)

This will assign the boolean value returned from our function to this hidden parameter 'IsValidNum'.

Now run the report if you enter value less than 1 or greater than 25 you should now get a pop-up.

This concept can be used to validate your report parameters and displaying user with friendly pop-up message. Enjoy!!