How to create a report joining data in excel and a Bex query

[chanda]

Hi,

I have an excel containing monthly budget figures based on sales group:Below is the format of the excel. these values are planned once a year for each sales group.

Sales group          May2014 Budget     June2014 Budget  ... April2015 Budget

S01                       100000                       150000                 150000

I have a Bex query which gets sales figures  like net sales and credit sales etc .It is working fine with out budget figures.

Sales Group	May 2014	June 2014
Actual sales
credit sales

My requirement is to join these budge figures in my final report and calculate Actual vs budget.

My final report should look like below:

Sales Group

sales group	May 2014	June2014	April 2015
Budget
actual sales
credit sales
Budget - Actual
(Budget-Actual ) %

Can any one suggest best possible solution for my requirement?

( I tried to create a DSO and load the budget figures alone and followed by a Multi provider on sales cube and DSO . The new query data is working

Thanks & regards, Sudhir

[RafkeMagic]

Chandra Janardhansudhir Kunar wrote:


( I tried to create a DSO and load the budget figures alone and followed by a Multi provider on sales cube and DSO . The new query data is working

Thanks & regards, Sudhir

I guess you solved this one yourself already, no?

[chanda]

Hi, Raf Boudewijns,

Thanks for your response.

No i have not resolved yet.

My DSO did not work. Also is there any other way to acheive it.

I think its because the sales query has many other fields influencing the results like Sorg/billing type etc. These are not maintained in DSO.

Hence always the Budget row is blank.

Thanks & regards,

Sudhir

[RafkeMagic]

Then you should have written "the new query data is NOT working"...

Anyway, the MultiProvider solution is the way to go. How to deal with the "fields" not in your budget file (DSO) has been explained plenty of times on this forum (search on "constant selection"). You could try and solve this in a workbook using visual basic, but I don't advice going there (it's a lot more complicated than the MultiProvider solution).

[former_member209606]

Hi Chandra,

Actual figures are stored in cube or DSO??

If actual figures stored in cubes means create a copy cube for budget figures and introduce one dimension in both the cube as Type.

Actual Cube type value as - A

Budget cube type value as - B

Now create the multiprovider and do the below setting in your query

up	May 2014	June2014	April 2015
Budget (set filter type = B)
actual sales (set filter type = A)
credit sales
Budget - Actual (Create CKF = Budget - Actual)
(Budget-Actual ) %

Regards,

Sureshkumar.

[Former Member]

Dear,

1) Create a Cube with Value type field which is set to constant 'PLAN'

2) Introduce the same Field in Sales Cube and set to constant 'ACTUAL'

Please note that fields should be present in both the cubes according to the data which you would like to see in the report (In This case Sales Group should be present in both the cubes)

3) Create Multi provider on top of these cubes and then the Query.

Keyfigures should be restricted with Value type. For ex: Budget should be restrcited with Value tpe 'PLAN ' and Actuals should be restricted with VALUE TYPE 'ACTUAL'

Sales Grp values should be present in both Cubes otherwise you will see data in 2 Different lines

Regards,

Kishanlal Kumawat

[chanda]

Hi Raf Boudewijns,

Yes, i should habe put as not working - typo error,

I think constant selection does not work when there are restrictions .

My case my sales figure calculations are restricted based on billing type.

Any other way to acheive it?

Thanks & regards,

Sudhir.

[RafkeMagic]

have you actually looked up constant selection?