locked
default message in a report RRS feed

  • Question

  • Hello All,

    I need to populate a Default message in the report area if a value in a Parameter is not available.

    Lets say There are 2 parameters in a report. The second is cascaded on the first parameter. When i select the first parameter, all related dates will populate. Now My problem is that, there are values in the first parameter where there are no dates in the second parameter.

    I have to populate a default message in the report area if there are no values in the second parameter.

    Thank you in Advance,

    Gupta.


    Tuesday, April 19, 2011 6:17 PM

Answers

  • Suppose the first parameter is company and the second one is store. A company may hold several stores. Then the SQL for the second parameter might be something like this:

     

    SELECT Store.Name, Store.Number
    INTO #Temp1
    FROM Store
    LEFT JOIN Company ON OIdCompany = FOIdCompany
    WHERE Company.Number in (@company)

    SELECT Name, Number FROM #Temp1
    UNION ALL
    SELECT 'None' as Name, -100 as Number WHERE NOT EXISTS(Select TOP 1 Number From #Temp1)

    Then no matter what the second parameter would show something even if the selected company has no stores yet.

     

     


    Remember to mark as an answer if this post has helped you.
    • Proposed as answer by Eileen Zhao Thursday, April 21, 2011 8:45 AM
    • Marked as answer by Challen Fu Monday, April 25, 2011 10:04 AM
    Tuesday, April 19, 2011 8:45 PM

All replies

  • Try something like

    =IIF(IsNothing(Parameters!SecondParameter.Value),"Default Message","")

    Tuesday, April 19, 2011 6:24 PM
  • This is not working. Because we have to provide the parameters to display the "Default Message". If you do not give the parameters then an error message will populate when "View Report" is hit.
    Tuesday, April 19, 2011 7:49 PM
  • Create a rectangle on the reports design surface and add your message to it.

    Now use an expression to toggle the visibility of the rectangle. Something like this...

    =IIf(Count(Fields!FieldName.Value, "DataSetName") > 0, True, False)
    


    Jason Long
    Tuesday, April 19, 2011 8:38 PM
  • Suppose the first parameter is company and the second one is store. A company may hold several stores. Then the SQL for the second parameter might be something like this:

     

    SELECT Store.Name, Store.Number
    INTO #Temp1
    FROM Store
    LEFT JOIN Company ON OIdCompany = FOIdCompany
    WHERE Company.Number in (@company)

    SELECT Name, Number FROM #Temp1
    UNION ALL
    SELECT 'None' as Name, -100 as Number WHERE NOT EXISTS(Select TOP 1 Number From #Temp1)

    Then no matter what the second parameter would show something even if the selected company has no stores yet.

     

     


    Remember to mark as an answer if this post has helped you.
    • Proposed as answer by Eileen Zhao Thursday, April 21, 2011 8:45 AM
    • Marked as answer by Challen Fu Monday, April 25, 2011 10:04 AM
    Tuesday, April 19, 2011 8:45 PM
  • Here you go:-

    Step1: Drag and drop a Text box on to the report designer area where you want to place it and type the error message in it ("Please provide a <date> parameter to view the report") and choose Red font color.

    Step2: Right click on the text box, choose Properties and navigate to "Visibility" tab and set it via "Expression". Th expressiobn would typically look like =IsNothing(Parameters!DateID.Value) (assuming you already have a report parameter named @DateID bound to your dropdown selection and have the DEFAULT value of that parameter set to NULL in Report Parameter properties tab).

    Step3: Change the qeuery of your Dataset to

    IF(@DateID IS NOT NULL)

    SELECT.............

    This way, the message wil appear only if the user selected form the first dropdown and hence the cascaded second dropdown populated itself with all dates but the user didn't select anything from second dropdown hance defauting the report parameter to NULL.

    Hope this helps!

    Cheers!!

    Muqadder.

    Tuesday, April 19, 2011 10:29 PM