cancel
Showing results for 
Search instead for 
Did you mean: 

No result if sections are used in WEBI

danny_lepomme
Explorer
0 Kudos

Hi All,

I have a quite complex formula, which works fine in a (cross)table or a chart.

But if I add sections (adhoc regio) to my WEBI, then I don't get any results for this variable.

My business case =

I'm looking for the minimum value for each day for each adhoc-regio (which is a group of measurement points).

The data contains meter-readings every quarter of a hour.  But because sometimes there are meter-readings missing, I only want to take into account, those quarters that has records for each meter within that quarter.

I also want to excludes exorbitant records with a "sigma 3"-rule. If the groups-total for a certain quarter transcends 3 times the standard-deviation, than I don't take this quarter into account.

This is my formula of the variable :

=Min( [Adhoc klein-Uurverbruik] In ([Tijd van meting];[Kalenderdag];[ZMPM12_BO_Q004].[Adhoc Regio])

Where (  (  Abs( ([Adhoc klein-Uurverbruik] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting]) ) - (NoFilter(Average([Adhoc klein-Uurverbruik]) In Report ))) < (3 *Abs(NoFilter(StdDevP(([Adhoc klein-Uurverbruik])ForEach  ([Tijd van meting];[Kalenderdag];[ZMPM12_BO_Q004].[Adhoc Regio]) In Report) In Report)  ))  ) And ([Counter] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) = Max([Counter] ForEach ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag]) ) )

Has anyone an idea, what I'm doing wrong ? Or is it a bug of BO 4.1 ?

Thanks in advance,

Kind regards,

Danny Lepomme

Accepted Solutions (1)

Accepted Solutions (1)

danny_lepomme
Explorer
0 Kudos

Here are some snapshots

danny_lepomme
Explorer
0 Kudos

And finally the one that illustrates the problem

kohesco
Active Contributor
0 Kudos

Danny,

Can you show me screenshot, where you have added the regio to the crosstable and at the sametime the section is on, please?

Thanks in advance

Koen

danny_lepomme
Explorer
0 Kudos

Koen,

I posted that screenshot at 9:23 am, called "snapshot_with_sections.jpg". Where you will see the sections "HodB HD",  "Balen" and "Bonheiden". In the crosstable I've put the [Adhoc regio] in the columns, and [Kalenderdag] in the rows.

Kind regards,

Danny

kohesco
Active Contributor
0 Kudos

I see, but if you take snapshot_datasource and add the regio twice in the left header.  Then put one as section

danny_lepomme
Explorer
0 Kudos

Koen,

This is the result ...

kohesco
Active Contributor
0 Kudos

Danny,

Crap

You can try to add: ForEach into the formula (and then sum up your dimensions, so the calculator has to consider those dimension)

danny_lepomme
Explorer
0 Kudos

Crap indeed !

The ForEach doesn't fix it either.

Nevertheless, thanks for your effort.

Grtz

kohesco
Active Contributor
0 Kudos

Danny,

The only thing that I can help you with, is to advise to start your formula from scratch and built it up piece by piece, and check from which point it goes wrong.

If you can let us know where the error jumps in the picture ....

Grtz

Koen

danny_lepomme
Explorer
0 Kudos

Koen,

That's how I've reduced the whole formula to a stripped formula as posted at 10:45AM. I've allocated the problem. It has something to do with the comparison with "Max ([Counter] ....". As mentionned in that post, if I replace this part of the formula by the (hard-coded value) 11, then I do get (correct) results.

If I put a part of the fomula : "Max([Counter] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag])" into a seperate variable, I also get a correct result of 11 for calendarday 22-12-2013.

I don't think it is too complex, because it works fine without sections.

Grtz

kohesco
Active Contributor
0 Kudos

Can you try to make a variable "check"->

if(   [Counter] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting]) =  Max([Counter] ForEach ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag]) ) ; 1 ;0)

Please syntaxcheck the "("

danny_lepomme
Explorer
0 Kudos

Koen,

I  had this check already in my previous post (Jun 23, 2014 3:00 PM). This is the value in the last column "take into account" in the snapshot Example_22122013_data.jpg. Notice that this only works on a lower level than initially ment. I had to add the dimension "time" (= [tijd van meting]) in my output. Otherwise I would get #MULTIPLEVALUE.

Normally my formula in the first column should has given blanks (NULL-values) for the first 4 records, since there are only 7 records on each time-moment. But starting of 01:00:00 I would expect to see values.

Very strange. I've opened in meantime an OSS-message. I "hope" it's a bug.

Grtz

kohesco
Active Contributor
0 Kudos

Great,

I 'm looking forward to the sap response, maybe you can play with the formula: "in section" as well in the mean time.

Happy hunting

Grtz

Koen

danny_lepomme
Explorer
0 Kudos


Thx

danny_lepomme
Explorer
0 Kudos

Yesterday I was already playing with "in section" and "in block" . But obviously not on the correct position. But now, out of the blue, I have a breakthrough

This formula gives me results :

=Min( [Adhoc klein-Uurverbruik]  ForEach ([Adhoc Regio];[Kalenderdag];[Tijd van meting]) Where (  ([Counter] In ([Adhoc Regio];[Kalenderdag];[Tijd van meting])) = Max([Counter] ForEach ([Kalenderdag];[Tijd van meting]) In Section) In ([Adhoc Regio];[Kalenderdag])  )  )

Now, I can start building up again to the complete formula, with also the first part of the where-clause.

To be continued ...

Answers (2)

Answers (2)

danny_lepomme
Explorer
0 Kudos

I could reduce the problem to the part with the max count of the measurement-readings.

=Min( [Adhoc klein-Uurverbruik] In ([Tijd van meting];[Kalenderdag];[ZMPM12_BO_Q004].[Adhoc Regio])

Where ( ([Counter] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) = Max([Counter] ForEach ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag]) ) )

For a certain regio ("HodB HD") and calenderday 06-12-2013, I know that de max counter = 11 for that calenderday. If a adapt my formula to

=Min( [Adhoc klein-Uurverbruik] In ([Tijd van meting];[Kalenderdag];[ZMPM12_BO_Q004].[Adhoc Regio])

Where ( ([Counter] In ([ZMPM12_BO_Q004].[Adhoc Regio];[Kalenderdag];[Tijd van meting])) = 11) )

I do get a result : 144,45

I keep on searching, but tips are still welcome. 😉

Former Member
0 Kudos

I too had this type of issue in one of my project.

With complex formula, when I add section, I was getting unavailable error.

Instead of adding section, I added report level filter or input controls to solve my problem.

This may help you.

Thanks,

Raj

danny_lepomme
Explorer
0 Kudos

Rajendra,

I was thinking at this solution too, as plan B.

But initially, we want to start with a chart for each region. And if the end-user notices an abnormal behaviour, he could drill-down to another WEBI with detail-information. I also had to use a work-around here, since WEBI still doesn't support "hyperlinks" in charts.

So it would be nice to have all the regions underneath each other. And observe strange evolutions in a blink of an eye.

But I appreciate your suggestion. And it is good for other users to know this could be a possible solution ! Therefor I will mark it as a helpful answer.

Thanks,

Grtz,

Danny

kohesco
Active Contributor
0 Kudos

Danny,

You use in your calculation [Adhoc Regio] so it might be needed in your table. If you set it as section it disappears from your table.  A simple workaround is adding the regio-dimension for a second time in your table and put in on hide

Grtz

Koen

danny_lepomme
Explorer
0 Kudos

Thanks for your reply.

I was aware of this. Addind [Adhoc Regio] to the table or chart, still doesn't solve my problem.

I inserted the section both ways :

- dragging [Adhoc Regio] out of the table

- inserting section [Adhoc Regio] via menu > Report Elements > Section > Insert Section

Grtz,

Danny