Question Re: SAP BO WEBI report function like SQL having clause in Technology Q&A

SAP BO WEBI report function like SQL having clause

horynao — Thu, 23 Feb 2023 08:13:44 GMT

Hello everyone,

I would like to ask how to do with some function in WEBI report like SQL having clause? I have a dataset I like to calculate first measure (an average of sold items in sales manager dimension) and second measure (total costs per sold items on row level). But if I filter second measure the first one is recalculated. I tried to use NoFilter function, but I like to keep one dimension report filter. For this one dimension it is all right, that the measures are recalculated.

Thanks in advance

Best Regards

Ondrej

Re: SAP BO WEBI report function like SQL having clause

ayman_salem — Thu, 23 Feb 2023 12:02:26 GMT

Can you provide a screenshot with sample data so we can properly understand your issue and give you a possible correct answer

Re: SAP BO WEBI report function like SQL having clause

horynao — Fri, 24 Feb 2023 08:50:11 GMT

Hello,

yes of course, there is my sample dataset.

row_id;datum;prodavac;kategorie;produkt;pocet;cena
11;2020-2;Pepa;místní;jablka;10;15
12;2020-10;Pepa;místní;hrušky;3;12
13;2020-2;Adam;místní;švestky;5;14
14;2020-12;Adam;cizokrajné;pomeranče;10;5
15;2020-4;Pepa;cizokrajné;banány;7;7
16;2020-8;Adam;cizokrajné;manga;6;7
17;2020-7;Pepa;cizokrajné;ananas;1;20
18;2020-3;Adam;cizokrajné;pomelo;2;15
19;2020-11;Pepa;místní;švestky;8;14
20;2020-6;Pepa;cizokrajné;liči;8;5

And as I wrote, I need to calculate first measure (an average of sold items in sales manager dimension) and second measure (total costs per sold items on row level). But the first measure needs to by calculate after filter of kategorie = "cizokrajné" is applied. And then I need to filter table with both measures limits. I imagine this part as like SQL having clause. SAP BO probably needs to know, what to calculate first.

The first measure.

The second measure

I hope my issue would be more clear now.

Thank you in advance

Ondrej

Re: SAP BO WEBI report function like SQL having clause

krisa24 — Fri, 24 Feb 2023 13:11:09 GMT

Hi this should work:

[first measure] = average ( [sold items]) where (category = "cizokrajné") In ([sales manager])

Re: SAP BO WEBI report function like SQL having clause

horynao — Fri, 24 Feb 2023 14:07:30 GMT

Hello, thank you. But I think the condition category = "cizokrajné" is set up hardly, user cannot change it.

Thank you,

Ondrej

Re: SAP BO WEBI report function like SQL having clause

horynao — Sat, 20 May 2023 08:44:16 GMT

Hello everyone,

would have someone another advice?

Thank you in advance,

Ondrej