Reg:Query on SET analysis with Macro

[#1]

Hi All,

I am developing BOM report which used to find out the variance between the BOM ids interms of Cost.For that I am selecting

Area ID,Production ID And Bom ID.Then I will click button will be known as SET first. Again I will select Area ID,Production ID And Bom ID and will click second button.It will known as SET Second.

I am storing the values which is selected in ListBox in variable using Macro.So in pivot table,I am summing up the first value and second value in two diffrent Expression

The first expression is subracted with Second Exp to find out Variance.Now thing is when I click second button all the values is geeting equal so final value coming as Zero.

Note:

Here I have atatched My Macro.Pls find the attachment and do the needful

EXP1:BM1

EXP2:BM2

Varince BM1-BM2

[mike_garcia]

Are you using the Expressions as you posted them at the end? or are you using them with set analysis?

If the Bookmarks were created correctly, the expression should be:

sum({BM1} Cost) - sum({BM2} Cost)

Regards,

Mike.

[#3]

Hi Mike,

I am using the same type of expression in my pivot table.

Column 1-Sum({BM1} Cost)

Column2-Sum({BM2} Cost)

Column 3-Column1-Column2

In Macro am Creating BookMark like This:

sub BM1
ActiveDocument.RemoveDocBookmark "BM1"
ActiveDocument.CreateDocBookmark false, "BM1"
ActiveDocument.RecallDocBookmark "BM1"


set val=ActiveDocument.Fields("DATAAREAID").GetSelectedValues
ActiveDocument.Variables("DATAAREAID1").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_ITEM_ID_PROD").GetSelectedValues
ActiveDocument.Variables("BM1_Prod_item").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_PROD_COST_CALC_ID").GetSelectedValues
ActiveDocument.Variables("BM1_Calc_ID").SetContent val.item(0).text, true



end sub

sub BM2
ActiveDocument.RemoveDocBookmark "BM2"
ActiveDocument.CreateDocBookmark false, "BM2"
ActiveDocument.RecallDocBookmark "BM2"


set val=ActiveDocument.Fields("DATAAREAID").GetSelectedValues
ActiveDocument.Variables("DATAAREAID2").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_ITEM_ID_PROD").GetSelectedValues
ActiveDocument.Variables("BM1_Prod_item").SetContent val.item(0).text, true


set val=ActiveDocument.Fields("dim_PROD_COST_CALC_ID").GetSelectedValues
ActiveDocument.Variables("BM1_Calc_ID").SetContent val.item(0).text,true

end sub

So when I click Button in front end after selecting AREA ID,PROD ID,BOM ID I want to know diffrence between these BOM ID

in terms of Cost.But now when I click second button all values become zero.So kindly help me out in this issue...