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 How to change value displayed in results of guided adhoc report.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SOLVED How to change value displayed in results of guided adhoc report.
 Login/Join
 
ElizabethN
Member
posted
I have a guided adhoc report with few sections for sorted fields and detail fields.
Some of the details fields can have possible values like 'Y', 'N' or null.
When these fields are displayed on the report I would like to display Yes for Y, No for N and nothing for nulls. How can this be done?

Here is an example of the fex file:
TABLE FILE VF_APP_SEQ_EVENT_PROD
SUM
&RP_FN13S_Detail9.(AND()).Please select detail field(s).
&RP_FN44S_Detail40.(AND(,,)).Please select detail field(s).
&RP_FN65S_Detail61.(AND(,)).Please select detail field(s).
BY LOWEST &RP_FN09B_Sort9.(BY(,,,)).Please select sort field(s).

Value for Closed is stored in the database as Y, N or null, but it should display Yes, No or stay empty.

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Something like this should work:
DEFINE FILE CAR
DSPFLD/A20V = IF '&MULTIPRT.EVAL' CONTAINS 'SEATS'    THEN (DECODE SEATS    (2       '2 Doors' 4          '4 Doors' ELSE 'HatchBack'))
         ELSE IF '&MULTIPRT.EVAL' CONTAINS 'BODYTYPE' THEN (DECODE BODYTYPE ('COUPE' 'Sports'  'ROADSTER' 'Sports'  ELSE 'Berline'))
         ELSE '';
END
TABLE FILE CAR
PRINT &MULTIPRT.(AND(<Seats,SEATS>,<Retail Cost,RETAIL_COST>,<Dealer Cost,DEALER_COST>,<Body Type,BODYTYPE>)).Please select detail field(s).
      DSPFLD
BY  &MULTIBY.(BY(<Country,COUNTRY>,<Car,CAR>,<Model,MODEL>)).Please select sort field(s).
END


But selected field probably don't have all a "Y/N/empty" values.
So you may have to put some conditions around the DEFINE to apply correct test depending on the value of &MULTIPRT. Also I haven't managed the fact that you can select both SEATS and BODYTYPE. The way I made it will react with the first true condition. Still have to play with it to consider all scenarios. Enjoy !

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


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
ElizabethN
Member
posted Hide Post
Thank you Martin, we are getting in good direction.
I modified your example to use with my data, but now I am getting both columns displayed. First one is displayed as 'Y' - it is selected from Multipart list, and the second - define is displayed as 'Yes'.
Is there a way to have specified column as hidden in Multipart?

That syntax did not work:
&RP_FN44S_Detail40.(AND( NOPRINT,...


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Just add NOPRINT beside the field
DEFINE FILE CAR
DSPFLD/A20V = IF '&MULTIPRT.EVAL' CONTAINS 'SEATS'    THEN (DECODE SEATS    (2       '2 Doors' 4          '4 Doors' ELSE 'HatchBack'))
         ELSE IF '&MULTIPRT.EVAL' CONTAINS 'BODYTYPE' THEN (DECODE BODYTYPE ('COUPE' 'Sports'  'ROADSTER' 'Sports'  ELSE 'Berline'))
         ELSE '';
END
TABLE FILE CAR
PRINT &MULTIPRT.(AND(<Seats,SEATS>,<Retail Cost,RETAIL_COST>,<Dealer Cost,DEALER_COST>,<Body Type,BODYTYPE>)).Please select detail field(s).  NOPRINT
      DSPFLD
BY  &MULTIBY.(BY(<Country,COUNTRY>,<Car,CAR>,<Model,MODEL>)).Please select sort field(s).
END


Surprisingly it does the trick !

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


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
ElizabethN
Member
posted Hide Post
If multipart section contains several columns which can be selected. Some of the fields should be display values from database and some of the fields need to have their values converted. Using technique described above, can we only hide the fields which are displayed using defines?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
To perform what I think you want to do, here is my suggestion:

1- Perform the DEFINEs for the fields you which to.
2- HOLD the new DEFINEd values with those that you don't need any DEFINEs.
3- Create you Ad Hoc on this new HOLD file

DEFINE FILE CAR
DSPSEATS/A20V = DECODE SEATS    (2       '2 Doors' 4          '4 Doors' ELSE 'HatchBack');
DSPBODY /A20V = DECODE BODYTYPE ('COUPE' 'Sports'  'ROADSTER' 'Sports'  ELSE 'Berline');
END

TABLE FILE CAR
PRINT DSPBODY     AS 'BODYTYPE'
      DSPSEATS    AS 'SEATS'
      RETAIL_COST
      DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS TMP
END
-RUN

TABLE FILE TMP
PRINT &MULTIPRT.(AND(<Body Type,BODYTYPE>,<Seats,SEATS>,<Retail Cost,RETAIL_COST>,<Dealer Cost,DEALER_COST>)).Please select detail field(s).
BY  &MULTIBY.(BY(<Country,COUNTRY>,<Car,CAR>,<Model,MODEL>)).Please select sort field(s).
END

You'll have to create an HOLD file with all possible fields that may exist in the Ad Hoc.

Hope this help


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
ElizabethN
Member
posted Hide Post
It was one of the earlier approaches to have all possible fields in a hold file, but because we have lots of fields from multiple views the performance was so bad, that this solution was discarded.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
The next solution that I think of is to add the DEFINEs in the master file and hide the original fields (those with DEFINEs) in the master. Then create your Ad Hoc based on this new master file.


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
ElizabethN
Member
posted Hide Post
Creating defines in master file and then using them in guided adhoc solved the problem.

Thank you


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 13 | Registered: September 15, 2014Report This Post
MartinY
Virtuoso
posted Hide Post
Was a pleasure to help.

Edit the first post of this discussion and add [SOLVED] at the beginning of the subject.


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
  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 How to change value displayed in results of guided adhoc report.

Copyright © 1996-2020 Information Builders