Straight Table - How to hide rows using an expression derived from another?

[#1]

I have a straight table as follows

- variables

        vRule1 = aggr1   (complex aggregation) 

        vRule2 = aggr2   (complex aggregation)

        ...

        vRuleN = aggrN

- Dimensions - rule, Plan_ID, PERIOD_YEAR, ZIP_CODE, ZIP_LEVELS

- Expressions

      Value = calculated based on rule.

      Status = status calculated based on Value - RED, BLACK and GREEN.

No problems so far. 

I need to hide all the rows that result in BLACK for Status. Since the rows are hidden only if all the expressions are null or 0, and Value and Status depend on each other, I am stuck.

I cannot move the calculation of Value or Status to the load script

The file attached is a mock up. Please note that the actual report has tables created from complex sql queries.

[john_duffy]

I believe you will have to get both expressions set to null in order to suppress the row.

I applied the < 1 and > 10 conditions to the Value expression to determine whether or not to calculate a value or return null().

I replaced 'NORMAL' in the Status expression with null().

See the attached application.

This works with your mock up.  It may get too complicated in your actual application.

Hope this helps.

John.

[#3]

John,

Thanks for the response. You are right that this won't work for my app. The reason is that Value is calculated within an if statement and the status expression is too. i range to check for high, low and normal varies by rule and TYPE dimensions.

I was wondering if there was some we could refer to these calculations in another straight table?

Regards

Jairam

[john_duffy]

Hi Jairam.

I not sure if it is possible to reference calculations from another object.

Could you post an application with the actual logic?

John.

[#5]

John,

I have attached the sample report with 2 tables one for calculating the data and the other for filtering out the ones that are not required.

Jairam

[john_duffy]

Hi Jairam.

I don't know if or how to bring an expression value from one chart to another.  I think we may have to solve this on the one chart.

Would the Report chart display all values for Value and Status from the Calculated Data chart that are in green or red?  The Report chart includes the field TYPE.  Does this mean you expect 2 rows for every Value/Status combination (FFS and STD)?

Could you attach a spreadsheet showing what you expect the Report chart to look like based on a couple of selections.

Thanks,

John.

[#7]

John,

I made a mistake.. The two chart have identical dimensions. So the number of rows should be the same for each status except that none of the black rows should be visible.

rule PLAN_ID PERIOD_YEAR ZIP CODE ZIP LEVEL Value Status

1     1            2008                  100           Z1              xxxxxx     NORMAL

2     1            2008                  100           Z1              yyyyy    HIGH

3     1            2008                  100           Z1              zzzzzz     LOW

2     1            2009                  100           Z1              xxx         NORMAL

4     1            2009                  100           Z1              yy          HIGH

3     1            2009                  100           Z1              zzz         LOW

should result in the same number of rows minus the NORMAL normal ones

1     1            2008                  100           Z1              xxxxxx     NORMAL

2     1            2008                  100           Z1              yyyyy     HIGH

3     1            2008                  100           Z1              zzzzzz    LOW

2     1            2009                  100           Z1              xxx         NORMAL

4     1            2009                  100           Z1              yy          HIGH

3     1            2009                  100           Z1              zzz         LOW

i.e the 6 rows are filtered down to only 4.

[john_duffy]

Hi Jairam.

Unfortunaltely, the only solution I can think of is the one in the application I attached above (May 2 3:35).

I'm not sure why this solution won't work.  Maybe you can explain in a bit more detail your statement that it won't work because the values are calculated within if statemtents.

John.