Multiprovider based Query displaying inappropriate output

[Former Member]

Hello All,

I am facing a challenge in displaying the desired output via multiprovider based query..

I have a multi provider based out of two DSOs

DSO 1 - Service Order Header - CRM

Key field - VIN

VIN     Dealer code     RO Number     Amount

ABC     INXXXX          123456               100

DSO 2

Key field - Item GUID  - Service Order Item - CRM

Ref GUID of Item     VIN     Part details     Quantity

XXXXXXXXXXX      ABC     Brake                    1

XXXXXXXXXXX      ABC     Battery                  1

XXXXXXXXXXX      ABC     Tyre                      1

Now my multiprovider output is as shown below

VIN     Dealer code     RO Number     Part details

ABC     INXXXX          123456             #

ABC     #                         #                    Brake

ABC     #                         #                    Battery

ABC     #                         #                    Tyre

As the dealer code & Ro number isn't available in the Item DSO, the output has value # for all the records except the first one..

Now my challenge is

1. There are only 3 part details in the Item table for VIN 'ABC'. Hence, Ideally the output should contain only 3 records with Part details as shown in the below table

2. The dealer code & Ro number values should be available for every part record as shown in the below screenshot..

VIN          Dealer code       RO Number     Part details

ABC          INXXXX               123456          Brake

ABC          INXXXX               123456          Battery

ABC          INXXXX               123456          Tyre

Appreciate if you could provide some suggestions

[Former Member]

Thanks for the response..

I understand that the constant selection at BEx level might not work for the below scenario..

I believe that the constant selection will work in cases where the query output generates two records for the same VIN.. Refer the below illustration

Dealer code    VIN            RO number

INXXXX          ABCD        123456

#                     ABCD         #

The above output is because, only VIN is available in both DSOs & the dealer code & RO number is available only in DSO 1.

Hence, the above output..

Now, for such cases, constant selection can be used by creating a selection on a key figure and using constant selection on dealer code..

After the constant selection at dealer code level, below is the output..

Dealer code     VIN               RO number

INXXXX            ABCD          123456

But my requirement is different.. I need to get the data for VIN, Ro number & dealer code (fields not available in DSO 2) also for the different parts in DSO 2.. Please look at the illustration in my first post for reference..

[Former Member]

Yes , as you might already be knowing that multiprovider is based on Union so the data should be in both the system else it would display non-assigned from the structure where you won't have the values.

If you have huge volume then info set might create a performance issue but we don't have an idea you are on which BW version , if it is latest it should be good else you can also go for constant selection as per the document description provided by James.

Thanks & Regards,

Geeta

[Loed]

Hi,

Aside from using CONSTANT SELECTION in query designer, you can also use INFOSET or COMPOSITE PROVIDER using JOIN condition instead of MULTIPROVIDER..

Regards,

Loed

[Former Member]

Hi Shreeja,

There is a option in BEX Query designer Constant selection.   Use the option to resolve the issue.

Regards

Vikas

[Former Member]

Hi Shreeja,

You could try creating a selection key figure and using Constant Selection.

Below is a great link on how to properly do this.

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

Thanks,

James

[Former Member]

Hi James,

Thanks for the link...

But my requirement is, the sales document number should be repeated in every row for its item..

Example:

As per the doc,

Sales doc           Item

1500003436       200

                           300

But the desired output as per my requirement should be

Sales doc          Item

1500003436     200

1500003436     300

This works if we use an infoset, but considering the data volume, I feel its not a good option to use infoset

Please suggest...

[Former Member]

Shreeja,

Did you turn on repeating characters?  In the example you provide it would appear that you just need to adjust the output of the report to have repeating values.

Thanks

James

[Former Member]

James,

I already tried doing that, but it didn't work...

I unchecked 'Hide Repeated Key values' at Query properties level, but no luck..