Multi Query

building reports desktopintelligence
1

Is there a way in BusObj to perform queries based on data sets from two or more previous queries? I need to do a complex join, based on two attributes, from data sets that are retrieved via separate queries from the same universe. I have tried to do a sub-query but the complexity of the join is not allowed in BO - At least I haven’t figured a way. Further, one query involves an maximum calculation. I think the method I’m after is called inline (online?) views if that is of any help…

Thanks,
Chris Lovejoy
IBM Global Services
Phone: (916) 567- 2960
E-Mail: clovejoy@us.ibm.com

Listserv Archives (BOB member since 2002-06-25)

2

At 09:25 AM 2/10/2000 -0800, Chris Lovejoy wrote:

Is there a way in BusObj to perform queries based on data sets from two or more previous queries? I need to do a complex join, based on two attributes, from data sets that are retrieved via separate queries from the same universe.

Chris, you may want to try creating a new table that joins data from “existing Data Providers”, i.e., the results of your two (or more) previous queries. I think it’s a shame that the documentation doesn’t address this feature. If you query the archives of this list, however, you’ll see previous discussions on “Linking Data Providers”. Just to get you started, here are the first steps:

1.) Do your first query. BusObj automatically creates a report tab with a table.

2.) To do your second query, nsert a new table on the same, or on a new report tab. (From the main menu: Insert – Table and then draw out where you want the table inserted)

The New Table Wizard gives you the following choices:
. Use existing data from the document (the default) . Build a new query on the universe current in use . Access new data in a different way - you’d use this one if you
needed to run the query on a different universe, on a personal data source, etc.

So, you’d build your second query by selecting the radio button beside either option two or three above.

3.) I usually prefer to put the joined results together on a whole new tab. So,
a.) From the main menu: Insert – Report b.) Now insert a new table on that tab: Insert – Table When the New Table Wizard comes up this time, choose:
Use existing data from the document.
c.) The next screen will allow you to choose the variables to include in the new table. You will see the results columns from both queries. d.) If each of your queries has identical columns, you can choose them all to effectively get a full join of the two results sets. If you want a different kind of join, you’ll need to research the archives for all of the different questions and considerations thta have been discussed in the past.

Hope this helps you to at least get started. When I was new to BusObj 4, and I saw discussions on this mailing list regarding “Linking Data Providers”, I couldn’t figure out quite how to get started, because the manual had no information on the topic at all! We only hit onto it by
accident during experimenting with the product.

Regards,
Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

3

Oops, I mislead you at the end of the earlier message, and left out a step.

When you do steps 3c and 3d:

3c.) Click the radio button to swith to “By Data Provider” – it makes it a little easier to select the variables.

3d.) You only really want to select the dimension variables from one of the data providers. The corresponding dimension variables from the other data provider are automatically linked.

It’s been a while since I’ve done this, and I haven’t done it very often, so all of the detailed steps and possibilities are not fresh in my mind.

Again, see past postings in the BUSOB-L archives (at: http://listserv.aol.com/archives/busob-l.html ) for more on this topic of Linking Data Providers.

Regards,
Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

4

Careful when you do this though…
I have many reports that have between 10 and 15 separate data providers. When you start to have 3 or more, they do not link very well. I have to run a separate query that acts as a “dimension” query. For example: a sales by territory report that shows a combination of yesterday’s sales, month to date sales, year to date sales, last year to date sales, etc I run a query that brings back all of the territories available. If a territory has sales for last year and not this year, it might have a hard time joining to the rest of the queries. In this case I use the territory from the that query in the column on the report.
Simon

Listserv Archives (BOB member since 2002-06-25)

5

Chris,

If I understand correctly you want to use results of some queries in the conditions of another query. Seems it is possible only using VBA and some tricks.

First you have to create a script which will construct the condition based on other data providers data, Suppose, script “MyScript” produces string like:

“your_table.your_column IN (‘A’,‘B’,‘C’)”

  • where ‘A’,‘B’ and ‘C’ are the values this script takes from other data providers Columns.
    Script should provide application variable MyVariable with this string.

Then you will have to create a condition in the universe of your last query. Put following string to “Where” part of this condition:

@Script(‘MyVariable’,‘N’,‘MyScript’)
and choose “your_table” from Tables… list of this condition.

Use this condition in query, the @Script call will be replaced with MyVariable string content before sending query to data base.

Trick is following. You should choose Numeric type of script returning value - ‘N’ (like in example), otherwise Reporter will close this
script result string with ’ ’ in result SQL. Of course, this data provider should be refreshed after other DP refreshing.

Hope, it might help.

Ilya Gusev
Consulting Service dpt.
URBANATECHNOLOGIES & TERN
iliag@urbanatech.com

Listserv Archives (BOB member since 2002-06-25)