1

Topic: Oracle BI 11g, aggregation on two fields

Good afternoon;
Help,  to understand with an aggregation question on two fields in the cutoff table.
In data domain there are fields: region, service, kpi, weight and some more fields with indexes. It is necessary to make the cutoff table in which regions are transposed in columns, services at lines, intersection of lines and columns - kpi.
Such table to construct it turns out.
But here with totals it is not got on. On each region in totals it is necessary to do not simply aggregation on kpi, and aggregation on kpi * weight.
Tried to do in section for services a new calculated member in which to do aggregation for kpi * weight but as them there correctly to specify I do not understand.
I will add, the data domain is constructed on a basis pipelined functions. And its data is used also at creation of one more table in other section. To write one more pipelined function for an output of the necessary aggregates on kpi * weight I think it is inexpedient, since great volume of the data from several DB will be repeatedly scanned. It would be desirable to use results of one function for creation of two tables of sections with various aggregations.

2

Re: Oracle BI 11g, aggregation on two fields

vint44;
The question is not so clear. You want to add . A field kpi*? He can be added as in a repository, and it is immediate in the report, specifying the formula of an index and aggregation.
If for an index kpi the total line kpi* in a plotter of reports it is possible to make an union (2 intersected circles on the right above over the selected indexes) is necessary to you and in a column service to write ' Total ', in a column with an index to write the appropriate formula.

3

Re: Oracle BI 11g, aggregation on two fields

>> you want to add . A field kpi*?
No since in this case in columns value kpi, and kpi* will be deduced not.
>> If for an index kpi the total line kpi* is necessary to you
Yes. Thanks, I will try join of reports. The idea is clear, but yet  still this mechanism since I am engaged bi recently. I hope without reefs manages.

4

Re: Oracle BI 11g, aggregation on two fields

All the same stumbled at this join of reports.
Help to learn on an example how to use it.
Let's admit at us is weeding region, service, kpi, weight and an amount.
It is necessary to construct two tables of the report.
The first: Columns - regions, lines - services. Their intersection is kpi and an amount.
The second: Columns - regions, lines - services. Intersection - kpi. The total line contains the totals kpi* for each column.
For the first table I constructed a cutoff in which to area of lines transferred a field - services, to area of columns transferred a field regions, to area of indexes transferred fields kpi and an amount. Rules of convergence any since to one region and service there corresponds one record with kpi and an amount.
For the second table I constructed a cutoff in which to area of lines transferred a field - services, to area of columns transferred a field regions, to area of indexes transferred fields kpi. Rules of convergence any.
Now I try to make an union of tables in which for the second dial-up of fields instead of I do a field norm_kpi = kpi * weight. Plus still, I apply to this field aggregation on the total.
And here there are problems since for creation of the second table of cutoffs we receive the incorporated data set and the data from this union will influence results of the first table of a cutoff. I.e. the field kpi will contain two values, one for original value kpi, another -  taking into account weight.
And it is not clear as such data set to apply to obtaining of the necessary results of the second and first table.

5

Re: Oracle BI 11g, aggregation on two fields

vint44;
Unfortunately both that, and another in one analysis not to receive in any way.
For the first table of a cutoff the analysis without an union, for the second with an union is necessary