Hi Everyone!! I am afraid my answer is going to be no but…
Can I insert a table that I created in Excel or Access into a universe created from tables in my database? I couldn’t find any way to do it but I thought I would give it a shot!! We are trying to avoid adding a table for one particular query that is only run once a year!!
Perhaps you may consider using a “linked” approach. At the report level, you can define a report to have more than one query, each using a different data provider and then link the two queries along thier common dimensions. Below is a prior response outlining how to accomplish this (please improvise as the response is specific):
Heather, what you will need to do in order to relate data from two different data providers (ie, universe and personal/Excel) is to link them. So, in your report create one query from your universe and be sure to include Medical Record Number. Then insert another table into the report and choose Personal Datafile for the dataprovider source. Again, be sure to include the Medical Record Number dimension from this source. At this point your report will include two tables, each based on a different query. Choose View from the Data menu. You will see two queries. Choose one and select the Medical Record Number field. Then click on the definition tab and click the Link to button and link it to the corresponding same field in the other query. Now you can insert tables using data from the existing document and since the two queries are linked, ALL fields will be available for your selection.
I have tried the linked approach and it doesn’t give us what we want. We have 5 queries that give us the number of veterans in 5 different categories. Then it is broken down by the job type. Somewhat similar to this:
Location X
Vet Cat
1 2 3 4 5
Type1 0 1 2 3 4
Type2 5 6 7 8 9
Type3 0 1 2 3 4
Type4 5 6 7 8 9
Type5 0 1 2 3 4
Location X 10 15 20 25 30
This part of the query works fine but we have another requirement. We have a query with about 15 conditions that give us the number of employees per each location. The user wants locations with 50 or less employees on one report grouped by work state and another report of locs with more than 50 emps grouped by work address.
I had tried to run a separate query to get the number of emps per loc and linked the queries. I created a variable that returned a value of 1 for all locations 50 or less emps and a 2 for locs with 50 or more emps. I made this variable my first break. However, this won’t fill the requirement because of the two different groupings required. If I filter on this variable, BO omits my Type detail lines. If I filter at the location level, it assumes location is what I want and gets rid of my detail.
I tried a subquery and the performance is poor and we would have to have 10 different queries with subqueries. (5 queries with 50 or less, 5 with more than 50). I tried to write the query giving me the count of vets and then saying IN with the statement giving emps per loc less than 50. I get the following error: ORA-01787: only one clause allowed per query block :-1787. I am assuming maybe this is because both the original query and the query with the number of emps both have counts and group bys.
My thought was since this report was run once a year, I could export my data provider that gives me the emps per location. I could pull this table in my universe and put the condition in my data provider. It was the easiest thing I could think of but I know how annual processes in the dba world can fall by the wayside and I wanted to avoid this.
Any thoughts??? We are about to go crazy here!! I have been looking at this for so long, I may have missed something obvious!! Any help would be great!!!
Perhaps you may consider using a “linked” approach. At the report level,
you can define a report to have more than one query, each using a different
data provider and then link the two queries along thier common dimensions.
Below is a prior response outlining how to accomplish this (please improvise as the response is specific):
