CODEWITHSUNDEEP
reporting-services
ferensilver
ferensilver

Reputation: 351

SSRS calling Monthname inside switch

I have a report that can be grouped monthly, quarterly, or annually. With my data, I have two columns, ActivationYear and ActivationMonthOrQuarter. I get ActivationMonthOrQuarter when I run the report, so I can have values ranging from 1-12 (1-12 if monthly, 1-4 if quarterly). If the grouping option is annually the ActivationMonthOrQuarter field will be null.

The issue I am running into is that it seems to still try and call the MONTHNAME function even when I use GroupingOption = "Annual" and throws an error since it cannot call it on a NULL value.

=SWITCH(
    Parameters!GroupingOption.Value = "Annual", "",
    Parameters!GroupingOption.Value = "Quarterly", "Q" & Fields!ActivationMonthOrQuarter.Value,
    Parameters!GroupingOption.Value = "Monthly", MONTHNAME(Fields!ActivationMonthOrQuarter.Value)
    )

Upvotes: 0

Views: 65

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try:

=SWITCH(
    Parameters!GroupingOption.Value = "Annual", "",
    Parameters!GroupingOption.Value = "Quarterly", "Q" & Fields!ActivationMonthOrQuarter.Value,
    Parameters!GroupingOption.Value = "Monthly", MONTHNAME(IIF(IsNothing(Fields!ActivationMonthOrQuarter.Value),1,Fields!ActivationMonthOrQuarter.Value))
    )

Let me know if this helps.

Upvotes: 1

Related Questions