BEx Report Requirement

[Former Member]

Hi All

I’m working on IS Retail.

In one of the Query, the output is as follow

Product	Amount $
1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
1 Chips 1 Ice Cream 1 News Paper	1.60 1.50 1.10
Chips	1.60
15 Fuel 1 Chips	20.00 1.60

What the end user would like to see is

Tender Type	Product	Amount $
Cash	1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
Credit Card	1 Chips 1 Ice Cream 1 News Paper	1.60 1.50 1.10
Debit Card	Chips	1.60
Gift Card	15 Fuel 1 Chips	20.00 1.60

If the Tender Type is dragged, Amount is getting aggregated and Products Info is missing

Any help is much appreciated

Environment: SAP BW 7.4, MS SQL Server

Regards

Raj

[Former Member]

Hi Vijay,

Thanks for the Document.

Apologies for the delay in getting back!!

Redesigning is last option that client wants to...

Looks like Loed Option [Constant Selection] is simple but didnt give a try...

Any thoughts ?

Looked at this document, on line of Loed inputs

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70dcd7b5-6a3d-2d10-c984-e835e3707...

cheers

Raj

[Former Member]

Hi Vijay,

Are you able to figure out to upload the sample design doc

regards

Raj

[Former Member]

Hi Anupama and Vijay

Thanks for the inputs and Ill get back to you

regards

Raj

[Former Member]

Hi Rajeev,

I have create design document but unable to upload. Please guide me how how upload

Regards,

Vijay N

[Former Member]

meanwhile you can go through the link

[Former Member]

This message was moderated.

[Former Member]

Hi Anupama and Vijay

Thanks for your inputs.

My query is based on MP, as in the screen shot.Also the Data Flow for Each cube is same for all Cubes

aas b

regards

Regards

[Former Member]

Hi Rajeev,

Looking at the data flow i can say the change suggested by Vijay should work. Please make the change.

Regards,

Anupama

[Former Member]

Hi Rajeev,

As Anupama Suggisted you can change the backed design as mentioned below.

what is your data model weather Means Of Payment data coming from one infocube and Article data is coming from another cube. you combined both in Multiprovider? if yes I guess you need to change your data model design, create one final infocube and add both cubes objects into final infocube and create transformations between final infocube and Article infocube and take the look up (Means Of Payment object) from Means Of Payment Cube and populating final infocube using end routine by taking Receipt Number as key field.Include this final cube in Multiprovider. Then in the report level Means Of Payment showing two records with two articles.

Regards,

Vijay N

[Former Member]

Hi Loed

Apologies for the delay in getting back..

The issue is

Means of Payment	Receipt Number	CAL Day	Article	Sale Qty	Tender Value
----	106	1/12	Coke Can	1	2
----	106	1/12	Fuel	10 Lit	20
EFT	106	1/12	----	1	10

Means of Payment is not linked to Article

The Link between Means of Payment and Article should be coming from Source, but that's not an option [as client wants to be done it in SAP BW level or BEx Level]

What would be the best option?

Any thoughts?

thanks

cheers

Raj

[Loed]

Hi Rajeev,

Did you already try to apply CONSTANT SELECTION in your query?

For example (assuming that SALES QTY and TENDER VALUE are both SELECTIONS in your query designer), your SALES QTY comes from the source same with ARTICLE object and TENDER VALUE comes from the source same with MEANS OF PAYMENT:

1. Add the object MEANS OF PAYMENT to SALES QTY, right click this object, choose CONSTANT SELECTION

2. Add the object ARTICLE to TENDER VALUE, right click this object, choose CONSTANT SELECTION

Just post here if you have any queries..

Regards,

Loed

[Former Member]

Hi Rajeev,

It will be better to link the Means of Payment and Artical at BW backend leavel.

This can be easily done in the backend using a simple logic matching the Receipt number. And the query will become much simpler.

Regards,

Anupama

[Former Member]

Thanks Loed,

I didn't explore that option.

Ill try and get back to you

regards

Raj

[Former Member]

Hi All

I can see the data at PSA Level, its not getting into Cube.

No routines, its straight mapping

cheers

Raj

[Former Member]

Hi Loed

Apologies for the delay

Here is the screen shot

cheers

Raj

[Loed]

Hi Rajeev,

No, I mean the bex query designer layout?

Regards,

Loed

[Former Member]

Hi

I didn't check but I was thinking that it's because the data for Gift Card is maintained at header level and Other Tender Types data is maintained at Item level?

Correct me If I'm wrong

Can it be possible?

regards

Raj

[Loed]

Hi Rajeev,

Please provide the screenshot of your query designer..

Thank you..

Loed

[Former Member]

Hi Loed

Today I tried other way by dragging individual Tender Types, except Gift Cards, all other are giving info as per end user as below

Tender Type	Product	Amount $
Cash	1 Soft Drink 2 Chocolate 1 Juice	1. 1.10 2. 2.70 3. 2.00
Credit Card	1 Chips1 Ice Cream1 News Paper	1. 1.60 2. 1.50 3. 1.10
Debit Card	Chips	1. 1.60

Its only if I drag the Gift Card, as said earlier, I cant see Product Information as below

Tender Type	Amount $
Cash	1. 1.10 2. 2.70 3. 2.00
Credit Card	1. 1.60 2. 1.50 3. 1.10
Debit Card	1. 1.60
Gift Card	1. 20.00 2. 1.60

Any thoughts

cheers

Raj

[Loed]

Hi Rajeev,

Did you do something "special" in your query? Can you post your query designer?

Regards,

Loed

[former_member186053]

Hi Rajeev,

Give us the screen shot of your query in designer(rows & columns). Also try to copy this query as new one, and see you can get the desired result in your new report.

Thanks,

Vengal.

[Former Member]

Hi Loed

As said initially its just Product and Amount in the Query like below

Product	Amount $
1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
1 Chips1 Ice Cream1 News Paper	1.60 1.50 1.10
Chips	1.60
15 Fuel1 Chips	20.00 1.60

But when I drag the Tender Type [Mode of Payment] into to Query,

I cant see Product any more as below

Tender Type	Amount $
Cash	1. 1.10 2. 2.70 3. 2.00
Credit Card	1. 1.60 2. 1.50 3. 1.10
Debit Card	1. 1.60
Gift Card	1. 20.00 2. 1.60

But the user wants to see it as

Tender Type	Product	Amount $
Cash	1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
Credit Card	1 Chips1 Ice Cream1 News Paper	1.60 1.50 1.10
Debit Card	Chips	1.60
Gift Card	15 Fuel1 Chips	20.00 1.60

Apologies if i created any confusion

Thanks for the help

regards

raj

[Loed]

Hi Rajeev,

How did it happen? Are you using analyzer? Maybe you swapped the two (2) characteristics?

Regards,

Loed

[Former Member]

Hi Loed

I tried all combinations, its not working.

Yes Im using Analyzer.

Im clue less

cheers

Raj

[Loed]

Hi Rajeev,

Perhaps it's a bug? Did you already report it to SAP? Is this happening on all queries? If yes, just open a ticket in SAP MARKETPLACE so that SAP will help you..

Regards,

Loed

[Former Member]

Thanks all, but when Tender Type [Mode of Payment ] is dragged to the report it showing as below

Tender Type	Amount $
Cash	1.10 2.70 2.00
Credit Card	1.60 1.50 1.10
Debit Card	1.60
Gift Card	20.00 1.60

but the need user wishes to see as

Tender Type	Product	Amount $
Cash	1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
Credit Card	1 Chips 1 Ice Cream 1 News Paper	1.60 1.50 1.10
Debit Card	Chips	1.60
Gift Card	15 Fuel 1 Chips	20.00 1.60

Any thoughts what can be done

Cheers

Raj

[Loed]

Hi Rajeev,

I think this should really be the output of your query if you drag the product in the report..

Tender Type	Product	Amount $
Cash	1 Soft Drink 2 Chocolate 1 Juice	1.10 2.70 2.00
Credit Card	1 Chips1 Ice Cream1 News Paper	1.60 1.50 1.10
Debit Card	Chips	1.60
Gift Card	15 Fuel1 Chips	20.00 1.60

Did you do something in your query? What is not the output instead of the table above?

Regards,

Loed

[former_member182997]

Hi,
as mentioned

' if the Tender Type is dragged, Amount is getting aggregated '

The table by which you have posted means the amount is displayed per line item -product.
So if you remove line item it will be automatically aggregated.
what is wrong here?

If something i m missing then do let know.

[Loed]

Hi Rajeev,

Can you explain your statement?

If the Tender Type is dragged, Amount is getting aggregated and Products Info is missing..

What will be the final report? Is it the first or second table above?

Regards,

Loed

[former_member186053]

Hi Rajeev,

Can you interchange the places of Tender Type and Product, i think you will get Tender type repeatedly for a product but any how you will have different tender type as per your example. However, just give a try and see if this works.

Product          Tender Type          Amount $

Thanks,

Vengal.