Hi,
I've come recently against some little problem I am unable to implement in ReportNet.
Here is the simplified description:
Assume we have ORDER table in our db with the following fields:
- Order_number
- Customer_number
- Product_name
- Product_type
- Date
We would like to obtain "CustomerBuys" measure. This should be combined with the "NumberOfBuys" attribute.
e.g. reports:
CustomerBuys by NumberofBuys by Product_type
PT\NoB 1 2 3 ...
ProductA 100 30 10
ProductB 120 40 13
ProductC 30 10 5
Total 160 48 15
So, that means that 160 customers bought once any product, but 120 customers bought once only ProductB. This can be complicated further by adding time. This measure is non additive in Product and Time dimensions. e.g. the same customer who bought once in July and once in May, will be counted in "2" category for a year, therefore will go to different totals.
I managed to implement this in PowerPlay using external rollup with separately aggregating each segment using SQL.
I wonder if this can be done in ReportNet?
What I have done so far:
I created a query subject having:
- Product and Date fields
- count(distinct Order_number for Customer_number) calculation as "NumberOfBuys)
- count(distinct customer_number) calculation as CustomerBuys
Unortunately this is not working, for example, instead of getting "number of customers who bought once Product B" I am obtaining "Number of customers who bought once anything limited by Product B". So, the customers who bought twice but only once Product B are excluded from the results and the results are smaller.
I will be thankfull by any suggestion...
Seweryn
I've come recently against some little problem I am unable to implement in ReportNet.
Here is the simplified description:
Assume we have ORDER table in our db with the following fields:
- Order_number
- Customer_number
- Product_name
- Product_type
- Date
We would like to obtain "CustomerBuys" measure. This should be combined with the "NumberOfBuys" attribute.
e.g. reports:
CustomerBuys by NumberofBuys by Product_type
PT\NoB 1 2 3 ...
ProductA 100 30 10
ProductB 120 40 13
ProductC 30 10 5
Total 160 48 15
So, that means that 160 customers bought once any product, but 120 customers bought once only ProductB. This can be complicated further by adding time. This measure is non additive in Product and Time dimensions. e.g. the same customer who bought once in July and once in May, will be counted in "2" category for a year, therefore will go to different totals.
I managed to implement this in PowerPlay using external rollup with separately aggregating each segment using SQL.
I wonder if this can be done in ReportNet?
What I have done so far:
I created a query subject having:
- Product and Date fields
- count(distinct Order_number for Customer_number) calculation as "NumberOfBuys)
- count(distinct customer_number) calculation as CustomerBuys
Unortunately this is not working, for example, instead of getting "number of customers who bought once Product B" I am obtaining "Number of customers who bought once anything limited by Product B". So, the customers who bought twice but only once Product B are excluded from the results and the results are smaller.
I will be thankfull by any suggestion...
Seweryn
