Creating a Trend report for 6 quarters using Multi Value Parameters, how to check each of the six Parameters for values? RRS feed

  • Question

  • Hello, i'm created a Trend report for 6 quarters and i want to be able to check each of 6 Parameters(#) to see if a value was entered and if a value was entered, display a table for the quarter, so the reports design has 6 tables side by side and each table will be filtered by one of the 6 Parameters.

    The problem is if someone only selects 5 of the 6 parameters, there doesn't seem to be an error proof way to see if Parameter(5) (which is the 6th Parameter) has value in it and if not and i keep getting the "#Error" and i've also tried all of the IsNothing, is Nothing, = Nothing combinations.   

    So i'm now checking the Count, but apparently if there's any reference to a non existent Parameter(5), the report errors out:


    Public Function CheckParmCnt(ByVal cnt As Integer, ByVal parm As String) As Integer
    If cnt = 5 
    Return 999
    Return parm
    End If
    End Function

    Thanks for any way suggestions how how to check ReportingPeriodID.Value(5) and prevent the "#Error" showing  up?

    =iif(IsNothing(Parameters!ReportingPeriodID.Value(5)),"no value",Parameters!ReportingPeriodID.Value(5))

    Wednesday, June 7, 2017 1:05 PM

All replies

  • Hi bobk5444,

    May I know whether you can successfully get the result by using below expression? 

    =iif(IsNothing(Parameters!ReportingPeriodID.Value(5)),"no value",Parameters!ReportingPeriodID.Value(5)) 

    If not, what error did you get? 

    And is your requirement to check the current value of the sixth parameter Parameters!ReportingPeriodID.Value(5)? Aslo, when previewing the report, do you need to manually type the parameter values? 

    Please correct me if I misunderstand your issue.

    Best Regards,


    Thursday, June 8, 2017 9:58 AM
  • Hi Henry thanks for responding!

    Yes I still get a "#Error" for each row column value if I only enter in 5 of the 6 required parameters via a "drop down" list of quarters and use the following expression in that 6th column:

    =iif(IsNothing(Parameters!ReportingPeriodID.Value(5)),"no value",Parameters!ReportingPeriodID.Value(5))

    Henry in the following example, I just simulated the using 3 columns, where I only selected 2 quarters from a "drop down" list and in the 3rd column I entered the following expression:

    =iif(IsNothing(Parameters!ReportingPeriodID.Value(2)),"no value",Parameters!ReportingPeriodID.Value(2))

    thanks Henry!

    Thursday, June 8, 2017 10:33 AM