Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Adding calculated values to a value field??
 Login/Join
 
Silver 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 8204
Windows, All Outputs
 
Posts: 47 | Registered: March 21, 2014Report 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  


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Silver 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 8204
Windows, All Outputs
 
Posts: 47 | Registered: March 21, 2014Report 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 versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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





Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Silver 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 8204
Windows, All Outputs
 
Posts: 47 | Registered: March 21, 2014Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders