Aggregate qty by plant using Crosstab

[former_member364765]

Hello All,

Could anyone give me more details about using crosstab? I never used crosstab action block in MII workbench.

Here is what I have in my XMLOutput.

Plant     Qty          MaxQTy

1001     1500          15000

1002        500           1000

1002        10000        15000

1004          0               12000

I need the output like this

Plant

Qty

MaxQTy

1001        1500      15000

1002        10500     16000

1004          0          12000

In the Crosstab action block I was mentioning Document output in the Document Source and in the Crosstab columns I mentioned Matnr column. How to get the Aggregate Qty and Max Qty by material? 

Thanks,

Latha

[swaroop_anasane · Accepted solution]

Hi Latha,

In simple terms, cross tab does a addition of columns based on one or more(max 4) specific column's value. So the intended result would be achiveable using crosstab.

Set Plant as your crosstab column.

Thanks,

Swaroop

[former_member364765 · Accepted solution]

Hi Swaroop,

Thank you for the quick response. In the Crosstab action block I used Plant as crosstab column. But in the Crosstab output I don't see the agrregate quantitites. Could you look at the attched file in my previous post and let me know what I am doing wrong?

Thanks,

Latha.

[swaroop_anasane · Accepted solution]

Hi Latha,

Ideally you should get the same column names as those present in your source. Can you share the crosstab link configurations so that i can comment further.

Thanks,m

Swaroop

[swaroop_anasane · Accepted solution]

Hi Latha,

I am able to get the result. PFB the screenshot:

Input:

CrossTab Column 1 = Plant, Output:

Last column in the output gives sum of all plants.

Please note, crosstab considers numeric columns(SQLDataType="6") only for operation. You are not getting it probably because the datatype is different.

Best Regards,
Swaroop

[former_member364765 · Accepted solution]

Thank you Swaroop for your help. I got the cross tab working.

Regards,

Latha.

[swaroop_anasane · Accepted solution]

Hi Latha,

Great it helped you

Regards,

Swaroop