[solved] Qaaws, possible to agregate ?

other Xcelsius - Crystal Dashboard Design
1

Hi everyone,

I am using QaaWS and wondering if it is possible to get agregated data ?

What I get with Webi (example) :
M. XXX - Holidays - 2 days
M. XXX - Working Days - 5 days

What I get with QaaWS :
M. XXX - Holidays - 1 day
M. XXX - Holidays - 1 day
M. XXX - Working Days - 1 day
M. XXX - Working Days - 1 day
M. XXX - Working Days - 1 day
M. XXX - Working Days - 1 day
M. XXX - Working Days - 1 day

Database is Oracle.

I think the answer is “use an olap cube” or “use life office” but this is not satisfying (cannot make a cube, live office too much bugs).

Thank you

titlola :fr: (BOB member since 2007-01-10)

2

hi!

you can try putting SUM() in your day measure in your universe.

regards,
r.A.

R.A. :philippines: (BOB member since 2008-10-28)

3

I already have SUM in my day measure.

Actually, to be more accurate, I get this result :

With Webi :
M. XXX - Holidays - 2 days
M. XXX - Working Days - 5 days

OR with object “date” :

date1 - M. XXX - Holidays - 1 day
date2 - M. XXX - Holidays - 1 day
date3 - M. XXX - Working Days - 1 day
date4 - M. XXX - Working Days - 1 day
date5 - M. XXX - Working Days - 1 day
date6 - M. XXX - Working Days - 1 day
date7 - M. XXX - Working Days - 1 day

In fact my QaaWS query seems to see one row per date even if I don’t take the object “date” in the query (I assume date is linked to my object “holiday/working date” in the universe).

Tell me if I am not clear
Thank you for your help

titlola :fr: (BOB member since 2007-01-10)

4

hi!

is the:
“date1”
“date2”
.
.
.
“date7”

are they included in the query?

thanks,
r…A.

R.A. :philippines: (BOB member since 2008-10-28)

5

No they aren’t but as I said, object “working days/holidays” is the object that “duplicates” the rows. As I said, I assume that object “date” is linked to my object “holiday/working date” in the universe.

titlola :fr: (BOB member since 2007-01-10)

6

hi!

sorry but i don’t understand clearly what you mean by

does this mean that you can’t see inside your universe?

thanks,
r.A.

R.A. :philippines: (BOB member since 2008-10-28)

7

I can but I didn’t make it and it is quite complicated.
Well all that I know is that object “working days/holidays” is creating the duplicate rows on the date

titlola :fr: (BOB member since 2007-01-10)

8

Hi Titlola,
did you get the duplicates even without your date dimension?

That’s quite weird what is happening.
What you can do is copy/paste of your QAAWS in oracle and check what’s going on also there. I think your problem is the SQL…
By the way if you are using the date object the “group by” will not sum the holidays/working days.

Could you post the sql of your QAAWS?

Cheers,

Luca Spinelli :it: (BOB member since 2008-08-27)

9

Yes

Do I have the option somewhere to get the SQL in QaaWS ?

titlola :fr: (BOB member since 2007-01-10)

10

Well, if it doesn’t aggregate it means that your “group by” somehow is not working.

About the sql from QAAWS, well you cannot check it…you can just check the pseudo-code from the exported link but it doesn’t help.

If I will get any idea I will let you know.
Apologies for not being helpful :frowning:

Regards,

Luca Spinelli :it: (BOB member since 2008-08-27)

11

I reproduced the query in webi and got the SQL. It seems I have no group by clause, but I don’t know where to add it.

titlola :fr: (BOB member since 2007-01-10)

12

Titlola,
can you post that sql?
If there is not group by I assume that you will not see neither a sum(measure) in the query…

Try to go back to the universe and check wether your measure is like =sum(measure) instead of =measure.

Hope this helps,

Luca Spinelli :it: (BOB member since 2008-08-27)

13

Really nice!! This is working well!
Thank you very much for your help Luca!

titlola :fr: (BOB member since 2007-01-10)

14

I’m glad to know that it works ;-)!

Have a nice week end!!!
Cheers,

Luca Spinelli :it: (BOB member since 2008-08-27)

15

hi!

[quote:a3de2e08bb=“r.A.”]you can try putting SUM() in your day measure in your universe.
[/quote]

i thought you already have the sum in the beginning. hehehe! but anyways, i’m glad you got it!

regards,
r.A.

R.A. :philippines: (BOB member since 2008-10-28)

16

What I meant is that I already had “sum” agregation checked in my measure options, not sum(measure) in the select!

titlola :fr: (BOB member since 2007-01-10)

17

hi!

it’s ok! a little misunderstanding there… next time i’ll go into details.

regards,
r.A.

R.A. :philippines: (BOB member since 2008-10-28)

18

No problem, that’s ok now it’s working :slight_smile:

titlola :fr: (BOB member since 2007-01-10)