Inventory and sales data from flat files

[Former Member]

Hello Friends,

I am getting sales data and inventory data in two flat files and wanted to see both inventory and sales data in the report.

So I have created a MultiProvider on both sales and inventory cubes.

In the report, I am restricting inventory data for actual and forecast quantity via transaction data coming within the inventory flat file.(Ex:Transaction 10)

In the same query, I wanted to see the sales quantity by restricting transaction data coming from sales file (Ex: Transaction 11).

That means object name is same for both Sales and Inventory cubes,but data is different.

I wanted my report to look like this.

Ex: Material Actual Qty Planned Qty Sales Qty

Currently I get Actual and Planned quantity but not the Sales quantity. Since transaction 11 data doesn’t exist in Inventory flat file, I couldn’t get sales quantity in the report.

Can someone shed some light ?

Thanks

Priya

[Former Member]

Create a structure and have two selections for inventory and sales. in the first selection just go ahead with the way you planned to do ... quantity and restriction on trans type and put the infoprovider too in the selection and restrict it by inventory cube. And the second one should also be the same structure, transtype being 11, and infoprovider being ur sales cube and KF from ur cube.

[Former Member]

Hi,

Does it mean, I don't have to create the query on MultiProvider?

And I tried to use below solution, but I don't get the InfoProvider in the laft hand side to get into the selection screen.

Thanks

priya

[Former Member]

If you connected the Multi-provider correctly, you shouldn't need to filter by "Transaction". I assume there are three key figures in your Multi-provider:

Sales Qty (from sales cube)

Actual Qty (from inventory cube)

Planned Qty (from inventory cube)

Or are there only two key figures and Sales Qty and Actual Qty using the same key figure? If this is the case then in your query, you need to use the same key figure twice, one filtered by Transaction 10 (Actual Inventory), and one filtered by Transaction 11 (Sales).

Does this help?

[Former Member]

Hi Geo

I have only one KF in my MultiProvider for both Inventory and sales data.

Transaction type distinguishes sales vs Inventory quantity.

Right now, I have created 3 RKF restriced to 10 for Inventory data and resticted to 11 for sales qty.

With this, I can get only Inventory data. but not the sales quantity.

Thanks

Priya

[Former Member]

NO, you would need to create a query on the multi provider and one multi in enough.

it doesnt matter how many KF's you have. there should be a condition to differentiate the KF, just like the way you have Transaction type. If u restrict the transtype by 11, the KF values should picked from ur sales cube. not sure what ur result is looking like

[Former Member]

When you linked the two cubes together, did you link by just Material? or Material and Transaction code? If you linked by only Material, there is probably another Transaction code field available (the one from the Sales cube), and you need to use this one for your RKF for Sales Qty.

[Former Member]

Thanks to both of you for your suggestions.

Voodi - I have followed your suggestion by drag and drop sales and inventory cubes in the selection fileds, but no luck, still sales quantity column is empty.

I am looking to display - in current week, X number are actual inventory, Y number are forecast inventory and Z number is sold in previous week.This information is coming in my flat files from third party and wanted to display this info in my reports.

Geo - In my multiProvider, I have linked with both material and transaction type.

Thanks

Priya

[Former Member]

I have verified the data in the MultiProvider, it has data for inventory and sales for that particular week.

So, the problem is within the query itself.

Please help me !!

Thanks

Priya

[Former Member]

Try creating a query with Material, Transaction type and Qty for the week and see what values you have for Transaction Type.

[Former Member]

Hi,

I was able to figure out the problem.In my selection option, I am selecting only for transaction type 10, so it displays only inventory data, when I open it for both, it displays both sales and inventory data.

Thanks for your help and points assigned to both of you.

Thanks

Priya