• Following the example provided on Physical table columns formulas from <http://wiki.pentaho.com/display/ServerDoc2x/02.+Pentaho+Metadata+Formulas>, I provided a Formula for a Dimension type field as isNull(ColName, 'Unknown'). This gets incorrectly translated to SQL. Please provide suggestions how to use a column as a Dimension field with a formula attached as well as in a GROUP BY clause without being replaced by the formula text




  • Queries:


  1. How do I add a business column with a formula that adds default values if the column value is null. I read that when formula value is overridden, the check box Is formula Exact must be checked.
  2. Do I provide the table name as well?



Expected:
Code:
SELECT isnull(ColName,'Unknown') AS ColumnA
		,COUNT(ColumnX) AS ColumnB
		FROM TableA
		GROUP BY ColName
Actual:
Code:
SELECT 
          isnull(ColumnA, 'Unknown') AS COL0
         ,COUNT(BT_TABLE_A."ColumnX") AS COL1
FROM 
          ActualAction BT_TABLE_A
GROUP BY 
          isnull(ColumnA, 'Unknown')