Tuesday, August 08, 2006

Non-aggregated items and joins

The message "more than 1 of the detail folders users non-aggregated items" means that you must have a numerical item using SUM in one folder and using Detail in the other. It could even be the column that you are joining on. Common examples of this are Sales Order Numbers and Line Numbers. If these are defined as numbers, as opposed to VARCHARs, by default Discoverer will apply a mathematical aggregator to these. This could be SUM or it could be Detail depending upon what you chose as the default when you created the business area.

However, later on you may have added a new folder and inadvertently assigned the other aggregator. So, what can you do? Well, there are two ways of handling this. You could go through and change the aggregator so that they align but to me this is still wrong if the data is something like a Sales Order or Line Number. For these, you need to change the Default Position to one of Top, Page, Side or Top or Side. Basically you want it to be defined as anything other than Data Point.

To check the above, use this workflow:
1. Launch Discoverer Administrator
2. Open the business area in question
3. Open one of the folders
4. Look at your items, particularly those with a little calculator icon alongside, and right-click on the item
5. From the pop up menu, select Properties
6. In the Item Properties dialog box, look at the current setting for the property called Default Position
7. If this setting is wrong, click in the cell and select the appropriate setting - I usually select Top or Side
8. Click Apply
9. Go back to step 4 and repeat steps 4 to 9 until you have checked all of the items
10. Click OK to close the Item Properties dialog box
11. See if you can now create the join

Discoverer will create an outer join if the column you are joining can have null values. Basically if there is no database constraint defined matching up the columns Discoverer will assume that the columns can be null, therefore creating an outer join.

I will be posting some more very interesting Discoverer snippets and tricks in the coming weeks so stay tuned. In the meanwhile, if you have a question or trick that you would like to see published, drop me a line at michael@learndiscoverer.com

9 comments:

Anonymous said...

Is there anyway to accomplis this for a column that appears in multiple folders in one shot rather than one by one.

Thanks.

P.S Have your book (Thanks a lot for the book too) use it a lot, howevere do have something along the lines of an advanced version of this book that gives more details?

Michael said...

No, I'm sorry but this is not possible. You will need to do this on an item by item basis.
Best wishes
Michael

Anonymous said...

Hi, I appreciate this information but I tried your suggestion and am still getting the error. I even created a new business area and reimported my folders (2 tables and a view) so I would know they were all setup with the same default aggregates. (sum) and I still have the problem. I then went through all three and changed all numeric fields to 'top' and tried again. I logout of Desktop between each try so I am seeing the changes. Any other suggestions?

Michael said...

Hi
I know this might sound odd but try setting the join properties between two of the folders as being 1 to 1. You should also reinstate all of the other changes you made such as changing the numeric fields to top.
Best wishes
Michael

Anonymous said...

I've just upgraded from 4i to 10gv2 and a report that happily displayed totals for MEAN and STANDARD DEVIATION now just shows blank fields - have the rules around either non-agregated items or totals changed?

Anonymous said...

Hi,

I have a user that is not able to view a particular workbook but i can view it perfectly on my end. I have checked the settings on discoverer desktop and he seems to have the access to open this workbook. Do you know what can be causing the user not to be able to view a workbook?

Thanks,

Velia
vee.robles@gmail.com

Michael said...

Hi
There are two usual causes of this problem. First, the user has not been granted access to the business area used by the workbook. This would be done by the Discoverer administrator. Second, the user does not have SELECT privilege over the tables / views in the database.

Best wishes
Michael

Anonymous said...

Hi Michael,

I tried giving Default position as Top instead of Data point to all items in the report and I am pulling Invoice Number and Invoice Date from another folder. Invoice Number and Invoice Date have datatype as varchar2 and DATE respectively. As these are just normal items, Discoverer didnt applied any mathematical aggregator to this. When I selected Invoice Number and Invoice Date, I am getting the error "more than 1 of the detail folders users non-aggregated items". How can I proceed?

Anonymous said...

Thank you very much, this article saved me lot of time.

Followers