SOLVED How to change value displayed in results of guided adhoc report.
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
April 10, 2015, 12:01 PM
MartinY
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
April 10, 2015, 02:03 PM
ElizabethN
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
April 10, 2015, 02:23 PM
MartinY
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
April 13, 2015, 08:52 AM
ElizabethN
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
April 13, 2015, 09:43 AM
MartinY
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
April 13, 2015, 09:59 AM
ElizabethN
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
April 13, 2015, 10:06 AM
MartinY
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
April 13, 2015, 12:02 PM
ElizabethN
Creating defines in master file and then using them in guided adhoc solved the problem.
Thank you
WebFOCUS 7.6 Windows, All Outputs
April 13, 2015, 12:49 PM
MartinY
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