Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Adding calculated values to a value field??
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Adding calculated values to a value field??
 Login/Join
 
Member
posted
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,


WebFOCUS 8.1.05 AppStudio
Windows, All Outputs
 
Posts: 29 | Registered: March 21, 2014Reply With QuoteReport This Post
Guru
posted Hide Post
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  


Senior Business Intelligence Analyst (BIA) @ Enclara Pharmacia
Production: WF 8.09, App Studio, BIP, RC, MSSQL / Test: WF 8202M, App Studio, BIP, RC, MSSQL
 
Posts: 230 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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!


WebFOCUS 8.1.05 AppStudio
Windows, All Outputs
 
Posts: 29 | Registered: March 21, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WF 7.7.05M, 8.1.05M, 8.2.0.1M Windows, HTML, Excel
In Focus since 2007
 
Posts: 1489 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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

 
Posts: 831 | Registered: May 24, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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


WebFOCUS 8.1.05 AppStudio
Windows, All Outputs
 
Posts: 29 | Registered: March 21, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Adding calculated values to a value field??

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.