Set Analysis using variable

[matancha]

Hello all.

I'm develop an OOR (Open Order Report), and I have a problem....

My Background table (that loaded on script):

Open Orders

Warhouses Balance

I want to show a table with all the open orders details, and the corresponding current delivery warhouse balance.
On Open Orders table I have a fields that contain the delivery wrhs:

OpenOrders.DeliveryWarehouseName

On Warhouses Balance, field that contain the name of the warehouse.

Warehouse#

My idea was to define a variables that contain the OpenOrders.DeliveryWarhouseName:

vDeliveryWrhsName = OpenOrders.DeliveryWarehouseName

and by Set Analysis to wrote somthing like:

Sum( {< Warehouse# = {'$(vDeliveryWrhsName)' >} WarehousesBalance.Balance ).

and it's not working for me...

My problem is how to declare the variable (maybe more than 1?), and how to use them in the chart (straight or pivot tables).

sopry for my poor english,

hope that is clear.

Thanks in advance -

Matan.

[er_mohit]

Try like this

Sum( {< WarehouseField = {'=$(=vDeliveryWrhsName)'} >} WarehousesBalance.Balance ).

[matancha]

Mohit -

Thanks for your quick response.

Unfortunately the solution you gave does not work for me...

Thanks again -

Matan.

[matancha]

Something more:

I think -  the fact that every open order row is linked (through the part) to many warehouses -  causing the problem.

I need an expression to sum only the delivery warehouse.

Matan.

[er_mohit]

Ok

then try this if u indeed it to hard code then

Sum( {< WarehouseField = {'DeliveryWrhsName'} >} WarehousesBalance.Balance ).

[matancha]

I think that I'm missing something -

maybe the problem is with the variable?

How to set the variable?

With the '=' sign? Without?

[er_mohit]

with = sign

[vipin_mishra479]

Hi,

use this

create variable like

vVariable = Concat(DISTINCT {<WarehouseField = {'OpenOrders.DeliveryWarehouseName'}>} chr(39)&WarehouseField &chr(39))

and put this in set analysis expression

Sum( {< WarehouseField = {'$(vVariable)'} >} WarehousesBalance.Balance )

vipin

[matancha]

OK pepole,

Now I'm have an expression that work for me, but - it's without Set Analysis:

Sum(If( (Warehouses.Db &'-'& Warehouse#) = (OpenOrders.DB &'-'& OpenOrders.DeliveryWarehouseName), (WarehousesBalance.Balance) ))

How can i write it with Set Analysis??

Thanks in advance -

Matan.

[vipin_mishra479]

Hi Mantan,

would u please attached any example.

vipin