Query design

[Former Member]

Hi

I need to create a query with characteristics Product ID,0CALDAY,Sales Region,Sales Amount where the report result should be like below:

User want to input Product ID and 0CALDAY and result should display how much this product was sold in various Sales Regions as shown below:

Sales Region----Sales Amount

A--------------------100

B--------------------200

South(=A+B)-----300(South is Sales Zone which has Sales Regions A,B.So South is sum of A&B=100+200=300)

C--------------------150

D--------------------200

North(=C+D)-----350(North is Sales zone which has sales regions C&D.So North is sum of C&D=150+200=350)

Total--------------650(Sales of South and North together)

How can I create this query?

Thanks.

[vinay_lohakare5]

Hello Simon,

You should have the region field in the data provider.

You can restrict the key figures with the specific region to get the data.

Example:

Each region will the product sold associated with it.

If Product A and B are sold in Jan and X and Y are sold in Feb in South region, the region south will have A,B,X and Y product assiciated with it.

So in the month of Jan it will have A and B and In Feb it will have X and Y, and if we see data for Jan and Feb it will show data for A,B,X and y.

Thanks,

Vinay

[Former Member]

Hi Vinay,

Thanks for your answer.

I donot have Region field in infoprovider.Is there a way to solve this report requirement?

[Former Member]

Hi

I cannot use hierrachy option as there is no plan to build hierarchy and also I cannot use workbook option as user wants this in query.

Can you please explain how to achieve this in query using calculated KF and Restricted KFs?

Thanks.

[former_member188080]

Create

RKF1 ---  restricting on sales region A and B  on main KF Amount

RKF2 ---- restrciting on sales region C and D on main KF amount

CKF = RKF1 + RKF2 or you can create 4 KF restricting on sales region A,B,C,D

SO for summation of A and B you can add those KF and similarly for C and D

IF there is any other logic you can put ABAP code for restricting some specific set of sales region also

Thanks and regards

Kiran

[Former Member]

Hi

Thanks for your reply.

The problem I have is,no-of products sold in Sales Regions are not fixed.South Sales region can have A,B only today but tommorrow I can sell many more different products.

I got a specific list of products that are sold in different regions(minimum 100 products in each region and one product sold in one region is not sold in other region)..I need to make a report that works dynamically.

How to achieve this?

[former_member188080]

Hi,

You can have hierarchy mentioned as above

2. You can maintain master data difefreently and use formulae in the query

3. You can define calculated KF to calcualte the summation,use 3 RKF and then sum them

4. you can use workbook for these cxalculation if they are fixed nature..you can put macros if needed..

Thanks and regards

Kiran

[Former Member]

Hi Simon,

This can be achieved by hierarchy on Sales Region. Multiple level of hierarchies will resolve your requirement.

Include Zones(South & North) in first level and Region (A & B) in second level. On drilling down to required level, Sales amount will be displayed.

Please post this question in BEx place (http://scn.sap.com/community/business-explorer) for clear quick and exact replies

Regards

Chandu