Use "Hidden"/Conditional Expressions in calculation - possible?

[#1]

I have created a User Definable application where they can choose the dimensions and then choose the facts (sales/margin/%) they want to display.

I use a conditional on the facts expressions to show or not show the data

=SubStringCount(Concat(FactID, '|'), 0) => Sales

=SubStringCount(Concat(FactID, '|'), 1) => Margin

=SubStringCount(Concat(FactID, '|'), 2) => Margin Pct

My problem is in this example:

- When Margin or Sales is hidden (conditional) Margin Pct will not calculate.   Is there any way to conditionally hide one of the Expressions, but still use the Expression column in the calculation - Margin Pct in this example

Formulas:

Sales= sum({ $<  SalesType = {'SLS'}, SalesCommissionFlag={'Y'} > } Invoice_SalesAmount*(Percent/100) ) + sum({ $<  SalesType = {'SLS'} > } SM_SalesTotal )

Margin = sum({ $<  SalesType = {'SLS'}, SalesCommissionFlag={'Y'} > } Invoice_Margin*(Percent/100) ) + sum({ $<  SalesType = {'SLS'} > } SM_MarginTotal )

Margin Pct = [Margin]/[Sales]

For margin Pct I do not want to have to always do:

(sum({ $<  SalesType = {'SLS'}, SalesCommissionFlag={'Y'} > } Invoice_Margin*(Percent/100) ) + sum({ $<  SalesType = {'SLS'} > } SM_MarginTotal ) ) / ( sum({ $<  SalesType = {'SLS'}, SalesCommissionFlag={'Y'} > } Invoice_SalesAmount*(Percent/100) ) + sum({ $<  SalesType = {'SLS'} > } SM_SalesTotal ))

[swuehl · Accepted solution]

If you are using the conditional expression on expression tab, no. It will prevent the expression from even being calculated, hence you can't use the result.

In a straight table, you can however use a conditional hide on presentation tab. Expression will be calculated and can be referenced, but not be shown.