Using webi ForEach function to solve complex aggregation problems

When you create variables in web intelligence, they must be assigned a qualification. This qualification assigns a “strong” type of either a measure, or a dimension (or a detail) kind of object to your variable. This strong tying has a profound impact on how webi handles aggregation of values in the variable you create, and can be the cause of many headaches for report developers trying to handle complex reporting requirements. The following example uses the e-Fashion universe for illustration purposes:
Imagine you need to produce a report that contains a reporting year in column one. Column 2 should display the revenue for the year in prior year, and a revenue state for the current year, and the revenue by state in the third column, for the current year only. Seems fairly simple, right. You can create a fairly simple variable in webi to satisfy the condition for column 2: If([Year]=”2006″;[State];[Sales revenue]) (If the year is 2006, the current year, display the State, else, display the revenue). Your variable will have to have a qualification, and will default to a dimension most likely. When you place your variable in the report, you will see a result far from what you were looking for:

What is happening is that webi is treating the variable value as a dimension and is not aggregating it across the states for the prior years. So even though the states are not displayed, we still get multiple rows for the years, as if the states were included in the calculation context.
Try to change the variable qualification to a measure, and you get the flip side of the problem: now you get the infamous #MULTIVALUE error because webi is treating the list of States like a measure and is unable to aggregate multiple States per Year.

What can you do? Using the ForEach webi function, you can enforce the proper calculation context for each part of your variable and “loosen” the tight typing of your variable due to its qualification:
If([Year]=”2006″;[State];[Sales revenue] ForEach ([Year]))
Make sure you set the variable qualification to a dimension, and now webi will be able to properly calculate the proper aggregation for each year, as expected.

Add a similar formula as your third column variable (If([Year]=”2006″;[Sales revenue] ForEach([State]);””)), apply some formatting (with NumberFormat()), and there you go, requirement met.

This entry was posted in Web Intelligence and tagged . Bookmark the permalink.

20 Responses to Using webi ForEach function to solve complex aggregation problems

  1. Louise Just says:

    Hi Ron,
    I like your article, is comes very close to addressing a #MULTIVALUE issue I am experiencing. I am having trouble getting my context correct in my formula and thought you might be able to help.

    I have a cross tab with product groups down the left column and date periods across the top (eg TY MTD, LY MTD etc). I have a total column for each Date set eg total column for MTD, YTD, MAT etc. I have a variable that provides +/- variance. The variable is ([TYMTD$s]-[LYMTD$s])/[LYMTD$s]. As I have MTD, YTD, MAT in the one table, I have created an If statement variable that shows the correct calculation variance in the correct total column eg =If [Date Period]=”MTD” Then [MTD $s +/- Total] Else If [Date Period]=”YTD” Then [YTD $s +/- Total] Else If [Date Period]=”QTR” Then [MQT $s +/- Total] Else If [Date Period]=”MAT” Then [MAT $s +/- Total].

    This works fine for each of the rows in the table, however my issue is with the total row, where I get #MULTIVALUE. For the total row in other reports I have built I have used an aggregation function like sum, min, max etc which have worked, however, in this case none of these are suitable.

    I am happy to email you the report to assist with understanding my calculations.

    Your thoughts on how I could achieve my desired result? Thank you in anticipation of your assistance.

    Regards
    Louise

    • Ron Keler says:

      Hi Louise, i think you are on the right track with the aggregate function, but there might be some additional complexity in your particular example. Another approach you might be able to use is multiple queries (data providers) for the different date cuts. As long as you can merge the date dim (and any other dim you use in the block), this could simplify your variables a lot. Assuming your query time is short.

  2. Christos says:

    In your final result above, suppose you wanted another column, BestYear, listing the state’s best year. e.g., suppose Illinois had its best year in 2002 so you’d want “2002″ to appear in the new column on the row with “Illinois” in it.

    I would assume you’d do this:
    BestYear = [Year] Where ([Revenue]=(Max([Revenue]) In ([State])))

    It seems that adding a column for
    BestRevenue = Max([Revenue]) In ([State]))
    works just fine, but the context doesn’t work within the Where clause in the BestYear calculation above – it instead returns #MULTIVALUE errors.

    • Ron Keler says:

      Christos, your calc looks fine as “pseudo code”, hard to say where the multivalue error is coming from. In the specific example i published, the first year column might be causing this. My suggestion in these situations is always to start simple and gradually increase the complexity level. So begin by creating a block with states and max revenue. Then, try to add another column where you can compare revenue to the calc from column 2. Also, you might be able to accomplish this with ranking, could be simpler solution.

  3. Jay C says:

    I have a situation somwhat similar to above. I have a report like below:

    Id DP
    1098 FIN
    1098 DRM
    1098 CES
    1098 CTS
    I would like to create a variable that replaces the four rows of DP and put the four values in a comma delimited text resulting in one row of data. Is this possible?

    Thanks
    Jay

    • Ron Keler says:

      Hi Jay, you can use webi formulas to accomplish this, but the solution you can accomplish in webi will probably be limited. I think this will be better solved on the data side. For example, if you are using oracle as your backend, you can use the listagg function to “convert” your rows to a delimited list, either in the universe or further down stream..

  4. teja says:

    hi,
    this is teja i have a requirement like

    cities–>column below values
    Kolk
    Noid
    gurgaon
    chennai

    i want to display the in place of Kolk,Noid,gurgaon–>NCR HOw to reslove this

    • Ron Keler says:

      Hi Teja, i’m not 100% sure i understand the question, but if what you are looking for is replacing the values Kolk,Noid,gurgaon with the single value NCR, you can simply create a variable in the report and use an if statement to say if cities = Kolk or Noid,gurgaon then NCR. You can look up the webi syntax in the help files. If you would like to do this for more then one report, it would be better to create a universe object with a case statement to accomplish this. Thanks.

  5. Sumit Pillai says:

    Hi Ron, great article!! I was working on a similar issue, hope you will be able to give me a solution. I have a dimension object – [customer]
    and a measure object –
    [sales]
    I created a variable [top customers] as a dimension having formula –
    =If([sales]>100000;[customer];”others”)

    Now I pull the variable alone in the report and expect to see result like this –
    customer1
    customer2
    customer3
    others

    But I’m getting #MULTIVALUE error.

    I tried the ForEach approach but still the error comes.

    Not sure how to resolve this.

    • Ron Keler says:

      This should work Sumit. I assume you need to create a block with two columns, customer and sales. Make sure to convert the formula to a variable of type dimension for the customer, and sum() the [sales} measure in the next column.

  6. Kiruthika says:

    Hi I have a similar situation. But FOREACH does not seem to solve my issue.

    Here is my scenario.
    Date Count of orders
    1/1/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//2/14 – 1
    1//3/14 – 1
    1//3/14 – 1

    The data is displayed as above.

    But I want it to be displayed as below.
    1//1/14 – 1
    1//2/14 – 4
    1//3/14 – 2

    Any help is greatly appreciated.

    • Ron Keler says:

      Hi Kiruthika, this does not seem to be a scenario that requires any special calculation context formulas, a simple count([order number]) should do the trick. What i suspect might be happening is that your dates have time stamps in them, so while their format only display the date portion on the report block, the actual value includes the time, so webi is not aggregating by day. Try to use a formula to convert the date to a character type in the format you need (like m/d/yy as you have in your example)

  7. Gayathri says:

    I am running into a kind of similar issue. I have a report with 2 similar queries for different businesses with 3 tabs for Business A, Business B and Business A+B. Each of the tabs need to display Top N dimensions (along with the rank), their attributes and the measure value. One of the attributes is derived from another table. It changes over time and we only need the latest attribute but the total value for the measure (I can achieve this with a calculation context). The rank still works. But I have 2 lines for each of the attributes even with a calculation context for the measure.

    Current scenario:

    Rank Dim Attribute1 Attribute 2 Attribute 3 Measure
    1 X ABC 2000
    2 Y ABC 100
    2 Y DEF 1000
    3 Z ABC 50
    3 Z DEF 1000

    What I need: Assuming DEF is the latest attribute based on a date

    Rank Dim Attribute1 Attribute 2 Attribute 3 Measure
    1 X ABC 2000
    2 Y DEF 1100
    3 Z DEF 1050

    Your suggestion is most welcome and appreciated.

    Thank you!

    • Gayathri says:

      Oops! The values are not aligned under the right header. Please match the numbers to the measure, ABC/DEF to Attribute 3 and X/Y/Z to Dimension. Let me know if you have any questions.
      Thanks!

      • Ron Keler says:

        Gayathri, i think your issue is related to the ranking, so you need to rank on the right combination of dims. Also, look for info on dense rank in webi, there are many posts about this topic with nice work around related to generating random numbers to make the ranking dense.

  8. Kven says:

    Hi,
    I have similar kind of issue.
    I have created a dimension and using “ReportFilter” function, i am fetching the result from query object. The result look like
    A;B;C;D…

    But, my requirement is to get result in,
    A
    B
    C
    D

    Is there any function which can convert the result from one single row into columns?

    Your help is greatly appreciated.

    thanks,
    Kven

    • Ron Keler says:

      Hi Kven, I suggest you push this back to the database level, oracle for example has string aggregation functions like listagg that will do something like this…

  9. Kevin says:

    I would like to choose the first of surgeon cases in each of the 23 operating rooms and if a surgeon is schedule to operate in the first case in multiple rooms, I only want his earliest case. I am using the Min function =if(Min[scheduled startdate and time] In ([Actual OR Number])=(Scheduled Start Date and Time] And (First Scheduled for each surgeon]=Scheduled Start Date and Time]);1). I also have a variable =Min([scheduled Start Date and Time]) in ([scheduled Primary surgeon]). My results on the report show the same surgeon in Multiple OR rooms, I only want the surgeons earliest case. Any suggestions will be appreciated.

  10. Roshan says:

    Hi Ron,

    After spending hours browsing through websites looking for a solution I finally found this. Thanks to you I was able to solve the #MULTIVALUE error and keep the little hair I have left after this ordeal :-)

    Thanks again!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>