locked
Passing NULL parameter from aspx page to Report Server RRS feed

  • Question

  • User-1069113530 posted

    Hi,

     

    I was wondering if anyone here could help me out.  I have an aspx page that has the ReportViewer control on it.  I have a report that has 3 parameters used for a stored procedure.  One of the parameter is a datetime parameter and it can be null.  For the purposes of this iteration of the report, this parameter needs to be null.

     

    I can not for the life of me figure out how to send over a null parameter from the aspx page.  Everything I have tried gives me this error:   "The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch) "

     

    I tried to send the parameter as "&StartDate:isnull", but that did not work.  Neither did: "&StartDate=", "&StartDate=NULL", "&StartDate=<NULL>"

     

    I'm reaching my wits end.  I can't seem to find any information anywhere about  sending a null parameter to ReportServer via the ReportViewer.

     

    Any help would  be greatly appreciated.   

     

    Tuesday, May 22, 2007 12:08 PM

Answers

  • User952543204 posted

    I tried a sample snippet and its working,

     

    ReportParameter rp1 =

    new ReportParameter("param2", new string[] { null }, false);

    Add it to reportviewer control

    List<ReportParameter> paramList = new List<ReportParameter>();

    paramList.Add(rp2);

    ReportViewer1.LocalReport.SetParameters(paramList);  //replace local with server report if you are hostin your reports on reporting server

     

    there you go on report side you can check

    =IIF( IsNothing(Parameters!param2.Value), "Null value","not nul")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2007 1:01 PM
  • User878321963 posted

    It looks like sundher_ganesh has come up with a working solution for sending a null value to the report.  If you want a default value of null for the report, you can do one of the following:

    In the report designer (At least in VS 2005) you can select Null for the default value (It's one of the three radio buttons).

    Otherwise, "=Nothing" (without the quotes, of course), works for the default expression.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2007 1:31 PM

All replies

  • User878321963 posted

    I can't find out how to pass NULL as the parameter for the report, either.  The report server is based in VB, so it uses Nothing instead of Null, but I can't get it to take that, either.  I'm not sure how you have the report set up, or what else you are using it for, but it may be easier if you could set NULL as the default value, and then just not pass in the date parameter.

    Tuesday, May 22, 2007 12:38 PM
  • User952543204 posted

    I tried a sample snippet and its working,

     

    ReportParameter rp1 =

    new ReportParameter("param2", new string[] { null }, false);

    Add it to reportviewer control

    List<ReportParameter> paramList = new List<ReportParameter>();

    paramList.Add(rp2);

    ReportViewer1.LocalReport.SetParameters(paramList);  //replace local with server report if you are hostin your reports on reporting server

     

    there you go on report side you can check

    =IIF( IsNothing(Parameters!param2.Value), "Null value","not nul")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2007 1:01 PM
  • User-1069113530 posted

    Unfortunately, I can't get the NULL to set for a default parameter in the report either.  I tried, setting it equal to:

    =NULL
    =null
    =System.DBNull
    =DBNull
    =isnull
    =:isnull
    :isnull
    <NULL>
    =""
    =
    just plain leaving it blank
     

    I have noticed that the when going through preview, the checkbox for NULL is checked.  But if I run the report, it is not defaulted to a checked state.

    It seems like there would be some information about this somewhere.  I have a hard time believing that no one has really found this to be an issue before.

    Thanks in advance for your help

    Tuesday, May 22, 2007 1:20 PM
  • User878321963 posted

    It looks like sundher_ganesh has come up with a working solution for sending a null value to the report.  If you want a default value of null for the report, you can do one of the following:

    In the report designer (At least in VS 2005) you can select Null for the default value (It's one of the three radio buttons).

    Otherwise, "=Nothing" (without the quotes, of course), works for the default expression.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 22, 2007 1:31 PM
  • User-1069113530 posted

    It looks like sundher_ganesh has come up with a working solution for sending a null value to the report.  If you want a default value of null for the report, you can do one of the following:

    In the report designer (At least in VS 2005) you can select Null for the default value (It's one of the three radio buttons).

    Otherwise, "=Nothing" (without the quotes, of course), works for the default expression.

     

    Yes, he did.  I guess we posted around the same time.  Thanks so much sundher_ganesh and Benners_J for your help.

    Tuesday, May 22, 2007 4:08 PM
  • User-1033550653 posted

    Just for posterity's sake in case someone is searching for how to do this in the future, the way you indicate a parameter is null in a querystring when accessing a report on the report server is like this:

     &Parameter:isNull=True

    Wednesday, April 2, 2008 6:05 PM
  • User905641233 posted

    Hi all,

    I recently had this same issue and want to share my (similar) solution in hopes that it will save someone the agony I went through trying to figure this out.  

    Project Background:

    Developing in Visual Studio 2008 (ASP.NET web project - VB)

    Connecting to SQL Reporting Services 2008.

    My report(s) have multiple parameters that can either be NULL or numeric values (all params are Integers but I'm assuming this will work with other data types also)

    Other important notes: SSRS report parameters are case-sensitive (make sure the parameter names you are passing in your URL are the same exact case as in the SSRS report itself.  Also - make sure the parameters are in the correct order in the SSRS report designer (the order can be different in your querystring though - I've tested this).  But the order issue in the RDL file drove me nuts for a while.

    Ok, on to the solution....

     ===============================================================================

     SSRS Report Parameter Settings:

    1. Check: Allow null value

     

     2. Set the Default Value of the parameter to   =Nothing  (this is equivalent to passing a NULL value but it's the only thing that worked for me)

     

     ===============================================================================

    ASP.NET Code Page (with Report Viewer Control):

    In this example, I'm just parsing through my querystring and adding report parameters dynamically on Page Load.  The important thing to note is what others mentioned above (if the value of the parameter in your querystring is blank or "", then do NOT pass a value:

                                     param(i) = New Microsoft.Reporting.WebForms.ReportParameter(ParameterName)

     as opposed to passing the value like this:

                                     param(i) = New Microsoft.Reporting.WebForms.ReportParameter(ParameterName, ParameterValue)

     

    Here is my full code snippet:

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                Dim sSSRSURL As String = SetSSRSURL()
                Dim sSSRSDomain As String = SetSSRSDomain()
                Dim sSSRSFolder As String = SetSSRSFolder()
                Dim sRenderFile As String = Request.QueryString("RenderFile").ToString

                Dim cred As New ReportServerCredentials(ConfigurationManager.AppSettings("SSRSUser"), _
                    ConfigurationManager.AppSettings("SSRSPass"), _
                    sSSRSDomain)
                Dim sParameters As String = Request.QueryString("Par").ToString
                Dim sParamArray() As String

                Try
                    ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote
                    ReportViewer1.ShowCredentialPrompts = True
                    ReportViewer1.ServerReport.ReportServerCredentials = cred
                    ReportViewer1.ServerReport.ReportServerUrl = New Uri(sSSRSURL)
                    ReportViewer1.ServerReport.ReportPath = sSSRSFolder + Request.QueryString("Rpt").ToString


                    'PARSES THROUGH PARAMETER QUERYSTRING AND ADDS A PARAMETER ARRAY TO THE REPORT VIEWER OBJECT:
                    If sParameters <> "" Then
                        sParamArray = Split(sParameters, "&")
                        If sParamArray.Length > 0 Then
                            Dim param(sParamArray.Length - 1) As Microsoft.Reporting.WebForms.ReportParameter
                            Dim i As Integer

                            For i = 0 To sParamArray.Length - 1 Step 1
                                Dim sP() As String = Split(sParamArray(i).ToString, "=")

                                '//IMPORTANT NOTES ABOUT PASSING PARAMETER VALUES TO A REPORT VIEWER CONTROL:
                                '//ON THE REPORT RDL FILE - IF THE PARAMETER CAN BE NULL - THEN YOU MUST
                                '//SET THE DEFAULT VALUE to:  =Nothing  (this is important)
                                '//THEN IF THE QUERYSTRING VALUE IS BLANK, THEN DO NOT PASS ANY VALUE (Code Below)
                                '//AND THE REPORT WILL PICK UP THE DEFAULT NULL VALUE

                               

                                If sP(1).ToString = "" Then
                                    param(i) = New Microsoft.Reporting.WebForms.ReportParameter(sP(0).ToString)
                                Else
                                    param(i) = New Microsoft.Reporting.WebForms.ReportParameter(sP(0).ToString, sP(1).ToString)
                                End If
                            Next

                            ReportViewer1.ServerReport.SetParameters(param)

                        End If
                    End If

                    ReportViewer1.ServerReport.Refresh()

                    'RENDER AN AUTOMATIC EXPORT OF THE REPORT INSTEAD OF DISPLAYING IT ON THE PAGE\
                    If sRenderFile <> "" Then
                        Dim warnings As Microsoft.Reporting.WebForms.Warning() = Nothing
                        Dim streamids As String() = Nothing
                        Dim mimeType As String = Nothing
                        Dim encoding As String = Nothing
                        Dim extension As String = Nothing
                        Dim bytes As Byte()
                        Try
                            bytes = ReportViewer1.ServerReport.Render(sRenderFile, Nothing, mimeType, encoding, extension, streamids, warnings)
                            Dim ms As New System.IO.MemoryStream(bytes)
                            Response.ContentType = "Application/" + sRenderFile
                            Response.BinaryWrite(ms.ToArray())
                            Response.End()
                        Catch ex As Exception

                        End Try
                    End If
                Catch ex As Exception

                End Try
            End If
        End Sub

      ===============================================================================

     Here is a sample query string I'm passing in to the page above:

    ReportViewer.aspx?Rpt=ClassScoresheets&Par=LowGradingPeriodId=2%26HighGradingPeriodId=5%26GradingPeriodId=2%26SchoolId=2651%26SchoolYearId=%26ClassroomAssignmentId=1100544&RenderFile=PDF

     So notice how the parameter "SchoolYearId" does not have a value.  My PageLoad function above will still ADD the parameter to the report, but the report will pick up the default value of  '=Nothing'  which is basically a Null value to SQL Reporting services.  (Note: "%26" is just a URL encoding for & - so I can keep all of my parameters together in the same string for parsing).

       ===============================================================================

    I hope this helps someone.  Feel free to respond if you have any questions.

    Mike

    Glyphic Solutions, LLC

    Baltimore, MD

     

     

     

    Tuesday, April 14, 2009 11:01 AM
  • User715581397 posted

    you can simply declare a string as null and pass it as report parameter.

            string nullValue = null;
            List<ReportParameter> parameters = new List<ReportParameter>();
            ReportParameter projectId = new ReportParameter("Project_ID", nullValue, false);
            parameters.Add(projectId);

    Note:parameter has to allow null in Report.

     

    Wednesday, June 24, 2009 10:54 AM
  • User-1922299067 posted

    Great Article Folks!

    That worked like a a charm for me too, Sundher! Thanks mate!

    and thanks to everyone, I have been looking wondering if this can be done. 

    I would like to share my feedbkack, that for those using VB.NET coding, then the correspodning syntax and steps I usually go through to pass parameters to my SSRS reports would be as follows ( which is working For me):

    1) My First Step Is Define My Array Of Objects: Dim myparameters() As Microsoft.Reporting.WebForms.ReportParameter

    2) My Second Step Is Redefine The Length Of My Array (In Here It Is an Array With One Element Only), yes In VB.NET we need to the use the number of the largest index when defining an Array Size which was something totally surprising for me at first, so in my case it is zero, only one element in the array exists:     ReDim myparameters(0)

    3) Third Step (Here is where My Correspodning VB.NET Line Of Code To Sundher's C# one:   :-)

    myparameters(0) = New Microsoft.Reporting.WebForms.ReportParameter("myParameterOne", New String() {Nothing}, False)

    4) FInaly I passed It To My ReportViewer Control:   ReportViewer1.ServerReport.SetParameters(myparameters)

    I hope this would be found useful for someone else as well. :-)

    Best Wishes To Everyone ! and thanks once again folks !

    ~Salman~

    Saturday, March 23, 2013 4:05 AM