IIF and Join on parameters

  • I have a multivalue parameter that can be 4 choices, or all. I want to display those parameters on the second subreport. I get it to work just fine on the first report because i specify the parameters and give them a label of what I want and the value that is in the database. However, when it goes to the second and third subreports its only showing the value.

    So, what I want is if all 4 are selected, display "ALL", if there are 2 or 3 selected, join those parameters but display alternate text. The parameter is frequency and the values in the database are A, Q, M or W, and I want to display Annual, Quarterly, Monthly or Weekly. These are the expressions I have tried.

    =iif(Parameters!prm_frequency.Count > 3, "ALL", join(iif(left(Parameters!prm_frequency.Value, 1) = "A", "Annual", iif(left(Parameters!prm_frequency.Value, 1) = "Q", "Quarterly", iif(left(Parameters!prm_frequency.Value, 1) = "M", "Monthly", "Weekly"))), "&"))

    this didn't work and just gave me an error because of the object to string

    I think I am close with this one but it only gives me the first value and doesnt join them:

    =iif(instr(join(Parameters!prm_frequency.Value, "&"), "A") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "Q") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "M") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "W") > 0, "ALL", "Annual"), "Quarterly"), "Monthly"), "Weekly")

    Any help would be greatly appreciated as my only other alternative is to have database create a view to use instead of the tables, but that may take a while, lol.

    TIA

  • Kheri (7/1/2014)


    =iif(Parameters!prm_frequency.Count > 3, "ALL", join(iif(left(Parameters!prm_frequency.Value, 1) = "A", "Annual", iif(left(Parameters!prm_frequency.Value, 1) = "Q", "Quarterly", iif(left(Parameters!prm_frequency.Value, 1) = "M", "Monthly", "Weekly"))), "&"))

    =iif(instr(join(Parameters!prm_frequency.Value, "&"), "A") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "Q") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "M") > 0, iif(instr(join(Parameters!prm_frequency.Value, "&"), "W") > 0, "ALL", "Annual"), "Quarterly"), "Monthly"), "Weekly")

    Any help would be greatly appreciated as my only other alternative is to have database create a view to use instead of the tables, but that may take a while, lol.

    TIA

    Can you create a dataset and use that as the source for the parameter available values?

    Then instead of using the parameter value, use the label?

    = Join(Parameters!prm_frequency.Label,", ")

  • Unfortunately, no. Since this is the second page of a multiple page drill through report, the label doesn't carry through to this page, just the value. So the only thing that displays is the "A, Q, M" when I use that expression.

  • Problem solved.

    The solution that I came up with is to add another parameter and pass the label to this new parameter as the value, that way when it is used further on in the drill through reports it becomes the value and I no longer have to change the text. I couldn't change the original parameter as it is used in the SQL where clause and saved in the database as the letter and not the word.

    Then I can simply use the expression:

    =iif(Parameters!prm_freq_lab.Count > 3, "ALL", join(Parameters!prm_freq_lab.Value, " & "))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply