I feel like I'm missing something simple but I checked the forum and the documentation and can't find what I'm looking for. I have data where measures and values for those measures are provided. There are some new measures that are derived from calculations of existing measures. I need to be able to create a report where all of the original and new measures are in the same column and all of the values (original and defined) are in the same column.

Here is a VERY simple start using the car file:

TABLE FILE CAR
SUM 
     CAR.BODY.DEALER_COST AS 'MEASURE_VALUE'
     CAR.BODY.OTHER_NEW_MEASURE_VALUE
BY  CAR.COMP.CAR AS 'ORIGINAL MEASURES'
BY  CAR.COMP.NEW_MEASURE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

  

What I end up needing is 'NEW MEASURE' and 'NEW MEASURE 2' to be listed in the same column as 'Original_Measures' and all the values to be in the MEASURE_VALUE column. The user wants to be able to filter by all original measures and new measures.

ANY advice is appreciated!!
Thanks!

This message has been edited. Last edited by: cldiaz,

Not sure if this meets your needs but I hope it helps a little anyway.

DEFINE FILE CAR
BLANK/A20 = '';
MULT/I4 = IF CAR CONTAINS 'DATSUN' OR 'TOYOTA' THEN 2 ELSE 5;
END
TABLE FILE CAR
SUM 
     DEALER_COST AS 'Dealer Cost'
	 OVER
     COMPUTE DEALER_COST_2/D10 = DEALER_COST * MULT; AS 'Retail Price'
	 OVER
BY CAR AS 'Product'
ACROSS BLANK AS 'Cost'


ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END  

Thanks Don! I need to be able to have new products added to filter on them, but this has given me some ideas to try. I'm going to play with this. Thanks again!

quote:

I have data where measures and values for those measures are provided. There are some new measures that are derived from calculations of existing measures.
...
I need to be able to have new products added to filter on them

Hi cldiaz,

If by "I need to be able to have new products added to filter on them" you mean : "There are some new measures that are derived from calculations of existing measures" to be able to perform such thing you may need to perform in two steps.

1- Create the new measures/values and Hold them
2- Merge new measures/value with original ones and create the filter data list (XML file)

TABLE FILE CAR
SUM DEALER_COST
BY COMPUTE CAR /A16 = 'FRANTALY';
WHERE COUNTRY EQ 'FRANCE' OR 'ITALY';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE CAR
SUM DEALER_COST AS 'VALUE'
BY CAR          AS 'MEAS'
ON TABLE PCHOLD FORMAT XML
MORE
FILE TMP1
END
-RUN

You can then use the entire above code as the source code for a list box.

But many solution can be performed depending on the place you need to use the "filter" and the way to access it.

To have different column weither they are real or calculated (defined) show up in the same column you will have to to multiple passes of the data.

TABLE FILE CAR
SUM 
     CAR.BODY.DEALER_COST
     CAR.BODY.OTHER_NEW_MEASURE_VALUE
BY  CAR.COMP.CAR
BY  CAR.COMP.NEW_MEASURE
ON TABLE HOLD AS H1
END
TABLE FILE H1
PRINT/SUM
     CAR.BODY.DEALER_COST AS 'MEASURE'
ON TABLE HOLD AS H2 FORMAT FOCUS
ON TABLE SET ASNAMES ON
END

TABLE FILE H1
PRINT/SUM
     CAR.BODY.OTHER_NEW_MEASURE_VALUE  AS 'MEASURE'
ON TABLE HOLD AS H3 FORMAT FOCUS
ON TABLE SET ASNAMES ON
END

USE
H2 AS H2
H3 AS H2
END

TABLE FILE H2
PRINT MEASURE
END

THANK YOU ALL! I was able to get both MartinY and TexasStingray responses to work for my need. So now I will incorporate these ideas into my larger report and see which one fits better. I really, really appreciate the guidance