I have problem with a report that was built using two aggregate measures from two different tables.
If the object contains SQL aggregate function such as SUM in its SELECT clause, a GROUP BY clause is generated.
When using aggregate measure objects from different tables, my Web Intelligence report creates 2 SQL statements and joins them. If I use two regular objects from these same tables, only one SQL statement is created.
However, the report has a condition on one of the aggregate measures and because of the ‘join’ it returns the wrong results.
it sounds like the universe you are using has no contexts ??
multiple queries are being created when you use measures from different tables but when you use dimensions from different tables just one query is generated.
This is because in the universe if you do file > parameters > sql (I think !) there is an option worded something like "multiple sql statements for each measure.
without knowing more detail it’s difficult to know what to say, but my guess is that you need to consider using contexts in your universe
Thanks for you reply.
I actually do use contexts in my universe, and I checked that both tables belong to the same context.
I don’t think “multiple sql statements for each measure” parameter is a problem, because if I pull non-aggregated fields from these same tables, I get only one SQL statement. The problem is only when I pull two objects from different tables that contain SQL aggregate functions.
You have just described “multiple SQL statements for each measure” exactly. As an experiment, turn that feature off, save your universe, and check the same query and see what the results are…
(BOB member since 2003-04-15)
As an experiment, turn that feature off, save your universe, and check the same query and see what the results are…
(BOB member since 2002-06-06)