It will always do an outer join. The purpose is to connect the 2 cubes.
What you can do is this: Include the column from the spreadsheet in the
tablular report from the database. Then apply a filter to hide the #Empty
ones. This typically works well.
Listserv Archives (BOB member since 2002-06-25)
In a message dated 99-02-17 10:42:44 EST, you write:
It will always do an outer join. The purpose is to connect the 2 cubes.
What you can do is this: Include the column from the spreadsheet in the
tablular report from the database. Then apply a filter to hide the #Empty
ones. This typically works well.
Possibly.
If you select one of the “linked” columns, then it will never be empty! To
complete the full outer join, BusObj “fills in” the missing data. In other
words, on a linked column you won’t find #EMPTY values to filter on. At least
that has been my experience.
For this to work, you have to insert one of the “non-linked” columns from the
data provider (spreadsheet or otherwise) and filter on that value. Depending
on your data, you may or may not get the results you need. Depending on your
linking, you may or may not even be able to insert the column!
I really would like to see the ability to select the join type in a future
release of the product.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
“Clayton, Cindy, HRBUS” wrote:
We are trying to link data providers and have run into a stumbling block.
We want certain info from a table in the database but only for a certain
set
of ssn’s. The ssn’s will be from a dynamic (constantly changing) list of
many ssn’s. Currently the list is already manually maintained within
Excel.
We want to run a query against Excel to get the list of ssn’s. Then run a
query against the database to get the other info. Then we want to link
these data providers and only see info for the ssn’s that were in the
Excel
Spreadsheet.When we do the link, we get all the records that came back from the
database. i.e. if the excel database had 200 ssn’s and the query brought
back 300, after linking on ssn, we see 300 records. It is like the
linking
process is a built in outer join. We had this before and ended up
dropping
the two tables into Access. However, this is an ongoing process that will
be run by a user who does not know nor wants to learn Access.Does anyone have any ideas on this?? We feel like it would be nice to be
able to choose how we want this link to work (inner or outer join).Thanks!!
You can do this in “BusinessQuery” for Excel…
Hope this helps
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria
Tel: +43-1-8151456, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Listserv Archives (BOB member since 2002-06-25)
Can anyone help me?? I have a Business Objects 4.1.2 report with 4 Data Providers (BO Queries) that I have linked by Fiscal Week Nbr, but when I combine the results into one table or graph the fiscal weeks that do not contain data are given a default value of ‘1’ rather than zero. This default value sku’s my graph and misrepresents the data.
Example:
Fiscal Week # Days A
Nbr
199945 3
199946 1
199947 3
199948 4
Fiscal Week # Days B
Nbr
199946 5
199948 4
When Combined:
Fiscal Week # Days A # Days B
Nbr
199945 3 1
199946 1 5
199947 3 1
199948 4 4
You can see that BO gives weeks 199945 & 199947 the value of ‘1’ for the # Days B field and throws off the results when graphing my results.
Any input would be GREATLY appreciated.
Gail McGuire
Sr. Applications Analyst
CVS Corporation
Pharmacy Data Warehouse
401-765-1500 x2236
gbmcguire@CVS.com
Listserv Archives (BOB member since 2002-06-25)
Gail,
Can anyone help me?? I have a Business Objects 4.1.2 report with 4 Data Providers (BO Queries) that I have linked by Fiscal Week Nbr
If possible pick up all in one dataprovider, otherwise migrate to 4.1.4 at least, synchronization has been known to fail in earlier releases (Especially with missing data, like in your case).
Good luck,
Marianne
Listserv Archives (BOB member since 2002-06-25)
I agree. I have some reports with 3 queries pulling multiple time periods. To get everything to sync up I have one query that pulls all records, independent of time period.
Shelley
If possible pick up all in one dataprovider, otherwise migrate to 4.1.4 at least, synchronization has been known to fail in earlier releases (Especially with missing data, like in your case).
Listserv Archives (BOB member since 2002-06-25)
Hi All,
I have two data providers one on universe and the other one uses a stored procedure.I linked both the data providers on common fields.I have 8 tabs on my report,when switching from detail to summary tab the report keep computing and Iam getting an hour glass and I can’t get the summary report on the screen and I have to kill the session and relogin.I have lot of calculations in my report.If I unlink the two of the common objects the summary report displays with out any problem but the detail tab totals are wrong.What am I doing wrong? The common fields linked are Region ,District and Territory.After unlinking Region and District I can get data displayed on summary report.Any inputs will be appreciated.
Arkay
Listserv Archives (BOB member since 2002-06-25)
Arkay
No, you’re not doing anything wrong. If you have a lot of rows of data, and a lot of calculations, then these need to compute, each ‘new’ time you enter the report.
My suggestion is to open the document, click on a report tab, , click on the next report tab , until all eight have been selected and saved thereafter. This way you’ll be able to traverse through the document without the reports recalculating each time you enter the tab.
By the way … would there be any logical dissection of the document into multiple documents to cut down the amount of data therein?
Ang.
Listserv Archives (BOB member since 2002-06-25)
Angela,
I have 4 Tabs in my document. After refreshing the document, I clicked on each tab and saved the document. Then I closed the document and re-opened it. When ever I click on a tab it is re-computing again.
Am I missing something.
Thanks
Vasan
mrsbish@OZEMAIL.COM.AU on 04/26/2000 02:56:17 AM
Arkay
No, you’re not doing anything wrong. If you have a lot of rows of data, and a lot of calculations, then these need to compute, each ‘new’ time you enter the report.
My suggestion is to open the document, click on a report tab, , click on the next report tab , until all eight have been selected and saved thereafter. This way you’ll be able to traverse through the document without the reports recalculating each time you enter the tab.
By the way … would there be any logical dissection of the document into multiple documents to cut down the amount of data therein?
Ang.
Listserv Archives (BOB member since 2002-06-25)
I have two universes:
I have two data providers in my report:
A. From Mainframe DB2, select Fiscal Year and JVID#1 B. From Unix DB2, select JVID#2 and TIN
I would like to retrieve records from Data provider B where JVID#1 = JVID#2.
When I link the data providers at the report level (view data/definition), the result appears to be a union of the two data providers. Thus I am getting data from data provider B when JVID#2 <> JVID#1.
Please help!
__________________________________________________ Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
Listserv Archives (BOB member since 2002-06-25)
Linked dataproviders always do a double outer join. That’s why they call them LINKED data providers instead of JOINED dataproviders.
You have to do clever things with filters in order to do what you want.
Listserv Archives (BOB member since 2002-06-25)
On Tue, 31 Jul 2001 16:34:02 -0700, Steve Krandel skrandel@BASECONSULTING.COM wrote:
Linked dataproviders always do a double outer join. That’s why they call them LINKED data providers instead of JOINED dataproviders.
You have to do clever things with filters in order to do what you want.
I have two universes:
- Mainframe DB2
- Unix DB2
I have two data providers in my report:
A. From Mainframe DB2, select Fiscal Year and JVID#1 B. From Unix DB2, select JVID#2 and TIN
I would like to retrieve records from Data provider B where JVID#1 = JVID#2.
When I link the data providers at the report level (view data/definition), the result appears to be a union of the two data providers. Thus I am getting data from data provider B when JVID#2 <> JVID#1.
I would love to hear any suggestions for implementing such filters. My mind is a blur right about now. I could use all the help I could get.
Thanks for your time,
Kim
Listserv Archives (BOB member since 2002-06-25)
Kim
Steve is right (as usual) you will need a complex filter to eliminate the rows where JVID#1 not = JVID#2. If your detail row displays JVID1, Fiscal Year, and TIN you can add a complex filter on TIN where TIN is NOT NULL. Just like a rdbms when not matching values exists the field is assigned a NULL value, which you can test for!
There are some good examples in the archives if you are not sure.
Mike
Listserv Archives (BOB member since 2002-06-25)
This may not be exactly what you want, but it should help you think of something.
With eFashion:
Query 1:
SKU, Sales Revenue
Where Year=1999
Query 2:
SKU, Sales Revenue
Where Year=2001
Queries will be linked on SKU.
Display SKU, Sales Revenue (Q1) and Sales Revenue Q2
Format->Filters->Add->Select SKU->press Define. Add this formula: =Not IsNull(<Sales revenue(Query 1 with EFASHION)>) And Not IsNull(<Sales revenue(Query 2 with EFASHION)>)
What is left are only those SKUs that had revenue in both 1999 and 2001.
Let me know what you think.
Listserv Archives (BOB member since 2002-06-25)
Hi Steve,
While we are on to it, i have another query regarding linked dps. Suppose i have customers ids and sales in a db. I use an universe to query these data.
In an excel sheet i have some of the customer ids (it is a subset of all the customer ids in the database) I want to create a report that shows the sales figures for ONLY those customers whose id appears in the excel sheet…
Here again linking the dataproviders (One from universe and one from excel) creates a cross product and all values from the db are retreived. so how do i restrict the report to show values only for those customers whose id appears in the excel ?
Regards,
Debashis.
Listserv Archives (BOB member since 2002-06-25)
One way you could achieve this is by inserting a new column on the excel sheet next to the customer id’s with a columnheader like for instance ‘link’. Put in a value 1 for each customer id in this ‘link’ column. Your Excel sheet should now look like this
Cust ID Link
1050 1
1055 1
1056 1
1060 1
etc. etc.
Now query this excel sheet again and make the Link column a detail (thus use view instead of run the first time!).
Link the customer id of the excel sheet to every other cust id in your other queries. In your tables you can know filter the ‘Link’ column to show only those with a value of ‘1’. After applying this filter your table will only display the cust id’s that are in your excel sheet.
Regards,
Robert D.J. Duindam
Fortis/Raaplex ICT Services
The Netherlands
Hi Steve,
While we are on to it, i have another query regarding linked dps.
Suppose i have customers ids and sales in a db. I use an universe
to query these data.
In an excel sheet i have some of the customer ids (it is a subset of all the customer ids in the database) I want to create a report that shows the sales figures for ONLY those customers whose id appears in the excel sheet…
Here again linking the dataproviders (One from universe and one from
excel) creates a cross product and all values from the db are retreived. so how do i restrict the report to show values only for those customers whose id appears in the excel ?
Regards,
Debashis.
DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.
This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.
Listserv Archives (BOB member since 2002-06-25)
Try using the exact same method I explained in the previous post. You will get them all in the original link, but filtering will remove the unwanted records.
Don’t forget to LINK the dimension.
Listserv Archives (BOB member since 2002-06-25)
Thanks all, for your replies…
I used the method of having an extra column in the excel sheet as suggested by Robert. It worked perfectly. Thank you Robert for the solution…
But Steve, i couldn’t really make it work using u’r idea… I will tell u what i did, and maybe you can point out the problem : I created the two data providers… One using Excel and one using the universe.
Then I linked the common dimension (say Cust Id). I created a report containing the Common dimension from BOTH the dps, and a measure.
As expected, the report now shows ALL values from the db and not only the ones present in the excel sheet. But the dimension column from the excel sheet now shows ALL values from the database !!! So any filter on that column (like not isnull() or = ) is not having any effect … It is still showing all the data from the database… Please tell me where i am going wrong.
Thanks,
Debashis.
Listserv Archives (BOB member since 2002-06-25)
I’ve got a big problem.
I’ve got a Personal Data File (PD) which I am querying with one data provider. This file just contains one column.
I the created another Data Provider using the universe and linked the two.
I was hoping that the second data provider would restrict its results to the results of the first data provider - this is not happening - it displays everything in the second query.
Does anyone have any idea how to get this functionality working? Is linking the way forward.
Thanks in advance
Listserv Archives (BOB member since 2002-06-25)
Make sure that the common dimension in the block is the one from the restrictive source (Your PD) then it “should” work.
Ken.
Listserv Archives (BOB member since 2002-06-25)