Skip to Content
avatar image
Former Member

Webi Formula Help-- Region values

I have a web intelligence report which has this formula:

=([Title in Org]+": "+[Parent Manager Name])Where((Left([Primary Loc];2)="18") And (Right(UserResponse([ProSales];"Region or Division");2)=Right([Primary Loc];2))) -- this formula is working fine when the Region or division has single value.

Now for example if Region 2 has 2 regional managers instead of showing the latest manager name it is showing as #multivalue. How to edit this formula so that i can get the correct value in the report.

Appreciated in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Jun 03, 2017 at 05:11 AM

    Multivalue means that you are trying to insert 2 values into single sheet, which cannot be done. You need to define extra filters, or this is not the case here, then create 2 rows in the first add min () and max() to the second, this will split these values. Second option would be to use pivoting on DB side.... but if you get 3 or more managers, what's then ?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 03, 2017 at 04:48 PM

    I don't want 2 names to reflect in the report, i want only one name, it is not a measure it is a dimension how can i add min() or max(), Even if i have 3 managers it should reflect only one in the report.my backend is a bex query not a universe.any inputs are highly appreciated.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 03, 2017 at 05:00 PM

    try this.you can try with Last function.

    =([Title in Org]+": "+last([Parent Manager Name])in report) Where((Left([Primary Loc];2)="18") And (Right(UserResponse([ProSales];"Region or Division");2)=Right([Primary Loc];2)))

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 03, 2017 at 05:34 PM

    Will try the above formula, but this formula is in a single cell, do i have to take any precautions?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member AMIT KUMAR

      Amit, after implementing the given formula, I'm still getting the #multivalue error in the report.

      [ Parent Manager Name] is a detail of type Text, is it causing any issues ?

  • avatar image
    Former Member
    Jun 04, 2017 at 09:16 AM

    Hello ,

    Use Max () function or LAST() funtion

    as Amit said it will work

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 05, 2017 at 02:12 PM

    Amit, I'm trying with the formula given by you.

    In the given formula

    =([Title in Org]+": "+last([Parent Manager Name])in report) Where((Left([Primary Loc];2)="18") And (Right(UserResponse([ProSales];"Region or Division");2)=Right([Primary Loc];2))).

    [ Parent Manager Name] is a detail of type Text, will it work?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 05, 2017 at 03:55 PM

    After implementing the below formula, it is still showing as #Multivalue.

    =([Title in Org]+": "+last([Parent Manager Name])in report) Where((Left([Primary Loc];2)="18") And (Right(UserResponse([ProSales];"Region or Division");2)=Right([Primary Loc];2))).

    [ Parent Manager Name] is a detail of type Text, will it work?

    Add comment
    10|10000 characters needed characters exceeded