We have some reports that we have to turn into the Government and therefore have non-negotiable needs in the formatting. Here is my problem. We have a crosstab that looks something like this:
There are 9 different job categories but not every location has employees in all nine job categories. Therefore, for one location I may have a line for job cat 1,2,3,5, and 8 and one location may have a line for cats 2,3,4,6 and 7. My problem is the gov’t requires us to show a line for each of the job categories regardless of whether or not there are any employees in it.
I don’t know how to force BO to do this. I ran the query, grouping by location code, in SQL*Plus and got the same type of results so it may be something I need to do on the Oracle side to get this to work. Maybe there is some way to fake it out… I am at a total loss on how to force this but we must find a way. If anyone has any ideas, I would greatly appreciate it, and so would our government, HA!!
There are 9 different job categories but not every location has employees in all nine job categories. Therefore, for one location I may have a line for job cat 1,2,3,5, and 8 and one location may have a line for cats 2,3,4,6 and 7. My problem is the gov’t requires us to show a line for each of the job categories regardless of whether or not there are any employees in it.
Maria:
This question has come up before, so you may be able to search the archive. On the other hand, I don’t know what keywords to use…
What you can do is create a table with all of the job categories in it. A look-up table, if you will. Then create a query that is just against that lookup table, which will guarantee that you get all of the categories. Creating a link in your document between the two queries on categories should force BusObj to display all of the categories for each location.
You should be able to try this with your current document. Create a second query that does a “No Duplicates” listing of job categories from the regular table; this will simulate the look up table. If this works, then you will likely want to create a lookup table for performance reasons later on. Anyway, after creating this second query, link the data providers, and switch the current category code with the one from the second data provider.
I had this exact problem. We solved it by creating a table, like Dave mentioned, only the table contained all combinations of rows and columns, with a field for the data value which was set to 0 (zero) for all rows in the table. I then added a UNION to my query. The query than displayed all rows even if there was no data in the original query.
The drawback here is that the table that you create must contain all columns that are in the original query to be able to create a UNION. I think your case this table would be:
Use an outer join on the table with the Job Categories. That’ll put blanks in whenever there are no groups with people in those jobs. Oracle can do this through Designer but it gets tricky if there is more than one field being joined between tables.
If you need to join on more than one field (and maintain the outer join), you’ll have to edit the join syntax in the bottom of the join dialog. Look at the join after one field is joined and the Outer flag is turned on. One side of the join will have an (+). This will go away after you join more than one field (or did last time I tried in early 4.1). You’ll need to go back and add the (+) for each field in the join from the table that originally had the (+). Experiment with it, you’ll get the hang of it.
