Trying to use the results of a combined query as a filter or data source for another query in a report. Is this possible?
Here’s the situation. need to find people who did X and then isolate the people who did X as well as Y. I have been able to do that using a cimbined query, but then to report on the people in my intersect result, I am unable to include those people as a filter. The number of people we are talking about exceeds 2 million.
How can I do this in BO without having to create a dervied table or any other Universe modifications?
Get the number of people who did X in a query (Query1).
Get the number of people who did X and Y in a separate query (Query2).
Subtract result of Query2 from Query1 (if you only need the total count)
Thanks, that gets me part of the way there, but not where I need to end up. I can get the number of people who did x and y. Where I’m now struggling is to find information about the people who did x and y and that information is all about data in a fact table. I need to count the number iof claims that group of people have for a particular period of time.
What I’m struggling with is how to isolate the people who did x and y and find the fact level information about them.
use nithya_raj’s suggestion, then add a query with all your fact table information,
then create a query filter in that query [name object] where [name object]=result from another query.
Is it possible to make a query on results from another query in Webintelligence? I have always used Desktop intelligence and I know its possible from there, but I can’t find that function in WebI. Can anyone explain exactly how to create this query?
Tried using the option of object = result of another query, BUT webi does not seem to write the sql with an “=” operator, rather it uses “in” and because of this my query fails:
A database error occured. The database error text is: ORA-01795: maximum number of expressions in a list is 1000 . (WIS 10901)
The result set of people I have to isolate is around 2 million.
I know this is late to the game, but I am struggling with a similar issue. I also am fighting the maximum number of expressions is 1,000.
However, my issue stems from the required data being stored in multiple universes.
For your issue, could you build a query with two sub queries?
That is, if you wanted to get all customers with claims in 2008 AND 2009:
Sub Query 1:
Customer # in list Customer #
Claims in 2008
Sub Query 2:
Customer # in list Customer #
Claims in 2009
This will get customers which fit both criteria. Now bring in the necessary fields into the result objects. From your information it seems like you will need the measure number of claims.
However, this may be too simple as I do not know if I misinterpreted your variables in the last equation you presented “X”… “X” and “Y”.
A easy way to get the intersection of data from different providers is to merge them. Let’s suppose we got two distincts databases of clients , dp1 e dp2. We want to find the customers that belongs to both. Query from do1 returns [custno1] and [measure1] . Query from dp2 returnd [custno2] and [measure2]. Merge [custno1] and [custno2] in [custmerge]. Drop [custmerge], [measure1] and [measure2] into the report. The intersection will occur when both [measure1] and [measure2] are not null.
(BOB member since 2011-02-03)
(BOB member since 2011-08-21)