how to reduce if conditions in expression side.

[#1]

HI,

I am going to develope Inventory report in that I want to show data for 3 months data for 4 subcolumns in pivot table . and for that if I will write expression like this :

if(subcolumn='BUM QTY' and ValueType='m1', $(varBumQty),

if(subcolumn='RUM QTY' and ValueType='m1', $(varRumQty),

if(subcolumn='VALUE' and ValueType='m1',num($(varValueBUM) / $(varConversionRate)/ $(varUnits), $(varDecimals)),

if(subcolumn='ACTUAL NO OF DAYS' and ValueType='m1',

  round(Floor(if($(varAvg3monthsConsumption) > 0, $(varBumQty)/($(varAvg3monthsConsumption)), 0))),

if(subcolumn='BUM QTY' and ValueType= 'm2', $(varBumQtyM1),

if(subcolumn='RUM QTY' and ValueType= 'm2', $(varRumQty1),

if(subcolumn='VALUE' and ValueType='m2', num($(varValue1)/ $(varConversionRate)/ $(varUnits), $(varDecimals)),

if(subcolumn='ACTUAL NO OF DAYS' and ValueType='m2',

  round(Floor(if($(varAvgmonthsConsumptionM1) > 0, $(varBumQtyM1)/($(varAvgmonthsConsumptionM1)), 0))),

if(subcolumn='BUM QTY' and ValueType= 'm3', $(varBumQtyM2),

if(subcolumn='RUM QTY' and ValueType= 'm3', $(varRumQty2),

if(subcolumn='VALUE' and ValueType='m3', num($(varValue2)/ $(varConversionRate)/ $(varUnits), $(varDecimals)),

if(subcolumn='ACTUAL NO OF DAYS' and ValueType='m3',

  round(Floor(if($(varAvgmonthsConsumptionM2) > 0, $(varBumQtyM2)/($(varAvgmonthsConsumptionM2)), 0)))

))))))))))))

then my report slows down so how to reduce tis conditions or how to reduce time span for that report.Administrator

[Nicole-Smith]

Try:

pick(match(subcolumn,'BUM QTY','RUM QTY','VALUE','ACTUAL NO OF DAYS'),

     pick(match(ValueType,'m1','m2','m3'),$(varBumQty),$(varBumQtyM1),$(varBumQtyM2)),

     pick(match(ValueType,'m1','m2','m3'),$(varRumQty),$(varRumQty1),$(varRumQty2)),

     pick(match(ValueType,'m1','m2','m3'),num($(varValueBUM) / $(varConversionRate)/ $(varUnits), $(varDecimals)),num($(varValue1)/ $(varConversionRate)/ $(varUnits), $(varDecimals)),num($(varValue2)/ $(varConversionRate)/ $(varUnits), $(varDecimals))),

     pick(match(ValueType,'m1','m2','m3'),round(Floor(if($(varAvg3monthsConsumption) > 0, $(varBumQty)/($(varAvg3monthsConsumption)), 0))),round(Floor(if($(varAvgmonthsConsumptionM1) > 0, $(varBumQtyM1)/($(varAvgmonthsConsumptionM1)), 0))),round(Floor(if($(varAvgmonthsConsumptionM2) > 0, $(varBumQtyM2)/($(varAvgmonthsConsumptionM2)), 0))))

)