Post reply

Syntax Error

  • August 18, 2009 at 3:57 am

    #224913

    Hi Can someone please tell me what is wrong with the statement below. I ma trying to use it for report heading in a text box but when I run the report it just display error.

    =IIF(Parameters!Consolidate.Value = "ALL", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value,

    Parameters!Consolidate.Value & " HeadCount Report for Period: "& Parameters!EndPeriod.Value)

    Please help

  • August 18, 2009 at 4:19 am

    #1040718

    HI

    I was having trouble replicating your error. Your provided info is not supplying enough to build a test report with.

    Offhand if you are using a Multi-value parameter for Consolidate, then that expression won't work, as well as, if your EndPeriod parameter is an integer then you should convert it to a string:

    =IIF(Parameters!Consolidate.Value = "ALL", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString ,

    Parameters!Consolidate.Value & " HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString)

    If you are using a multi-value parameter for Consolidate then you will have to use the JOIN function:

    =IIF( Join(Parameters!Consolidate.Value,", ") = "ALL, ", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString , Join(Parameters!Consolidate.Value,", ") & " HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString)

  • August 18, 2009 at 4:40 am

    #1040721

    Hi Gavin

    Thanks for the feedback, it works but I would like to replace the word "ALL" with "Retail" during run time.

    ie. the heading should be "retail headcount report for period 200907" currently it says "ALL headcount report for period 200907"

    Kind Regards

  • August 18, 2009 at 4:51 am

    #1040731

    Hi Gavin

    I just don't know what I changed or didn't but it's now working fine. Thank you very much for your help

  • August 18, 2009 at 4:57 am

    #1040732

    Hi

    Please post the expression and "setup" of the Consolidated parameter. Then we all can see

  • August 18, 2009 at 5:10 am

    #1040739

    Hi

    I use the expression you provided

    =IIF( Join(Parameters!Consolidate.Value,", ") = "ALL", "Retail HeadCount : "& Parameters!EndPeriod.Value.ToString ,

    strConv(Join(Parameters!Consolidate.Value,", "),3) & " HeadCount : "& Parameters!EndPeriod.Value.ToString)

    and the consolidate parameter is as follows:

    Name = Consolidate

    Data Type = String

    Prompt = Consolidate

    Multivalue = Yes

    again thanks for your help

  • August 18, 2009 at 5:28 am

    #1040747

    Excellent, I'm glad you got it figured out.

    Interestingly the StrConv doesn't actually do anything, as the Join has already concatenated the substrings into a single string. So you can leave it out of your expression:

    =IIF( Join(Parameters!Consolidate.Value,", ") = "ALL", "Retail HeadCount : "& Parameters!EndPeriod.Value.ToString ,

    Join(Parameters!Consolidate.Value,", ") & " HeadCount : "& Parameters!EndPeriod.Value.ToString)

  • August 18, 2009 at 5:32 am

    #1040749

    So the actual difference between your working expression and my failed one is the "ALL, " part. I assumed the Join function would add the comma&space at the end of the string regardless of the number of substrings concatenated. When there is only a single one joined then it doesn't and it returns only the contents of the first substring.

    Cool - learnt another thing today.

Viewing 8 posts - 1 through 7 (of 7 total)

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