WebFOCUS Blogs

Dear friends, While creating the Excel report, the name of the excel file comes randomly with normal scenario if we give "PCHOLD FORMAT EXL07". I recently came to a scenario where the business user wanted the excel report name to come as per the report name. Please note that user wanted to rename Excel file name not the tab names in the file. In general we right our code as below: TABLE FILE CAR SUM      CAR.BODY.SEATS      CAR.BODY.DEALER_COST      CAR.BODY.RETAIL_COST      CAR.BODY.SALES BY  CAR.COMP.CAR BY  CAR.CARREC.MODEL ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT EXL07 ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE *      INCLUDE = endeflt, $ ENDSTYLE END Output: However if we need to name the Excel file, we can use below code: TABLE FILE CAR SUM   ...

Hi Friends, After along time, I got something interesting to share with all of you. Sometimes back I have worked on a report where user wanted option to select the columns he wants to display in the report. In other words we can say user needed self-service kind of option same as we have with InfoAssist where user can select the columns which he wantsto print in the report. Similar requirement we had to achieve using double-list box in HTML launch page. I have taken a simple example using CAR file and few columns (COUNTRY, CAR, MODEL, SALES, DEALER_COST). This can be exyended to n number of columns. So first of all, I created asimple launch page as below screen shot: The double list box is defaulted to COUNTRY column. Which means if we run the report for default, we will get only COUNTRY columns printed in the report. However in the next screen shot we have selected 4 columns and the output is displayed in the below screen shot. Selection Screen:   Output Screen: ...

Dear Developers,  Recently I was working on a simple report using across. We had to justify values and data to right. It worked fine in HTML but when I generate the report in PDF, the issue I was facing is with the alignment. The data doesn't seems to appear properly aligned.  Please look at the screenshot: Initially it looked very simple but when I started looking for the alignment, it took pretty much of my time. So I thought it is worth sharing with all of you. Below is the sample code using CAR file: TABLE FILE CAR SUM SALES/D12.1 AS '' BY COUNTRY AS '' ACROSS CAR AS '' ON TABLE PCHOLD FORMAT PDF ON TABLE SET PAGE-NUM OFF ON TABLE SET STYLE * TYPE=REPORT,GRID=ON,SQUEEZE=ON, SIZE=7,$ TYPE=ACROSSVALUE,BACKCOLOR = GREY,COLOR=WHITE,STYLE=BOLD, JUSTIFY=RIGHT,WRAP=0.60,$ TYPE=REPORT,ACROSSCOLUMN=SALES,JUSTIFY=RIGHT,$ TYPE =ACROSSTITLE, BACKCOLOR = GREY,$ ENDSTYLE END And Screen shot of the output: Please share your ...

Dear Developers,  Recently we received a requirement where on the launch page, we wanted to restrict the user to select the dates in a specified date range. The requirement basically came up because in calendar, user can select 10 years past and future dates from current year. There is no point in allowing user to select a date for which data is not available or it is not relevant for the report. We can simply do this if the range is static and not going to change in future however that is not the scenario. We would need to do this from WebFOCUS procedure so that if the range changes in future, it should automatically reflect in the launch page. First for all, we would need to create a .fex file which will have similar content (here I am 4 years past and 3 years future dates) :  -* File: IBFS:/localhost/EDA/EDASERVE/APPPATH/pravin/purge_date.fex Created by WebFOCUS AppStudio DEFINE FILE CENTURYCUSTOMERS StDt1/MDYY=&MDYY; StDt2/MDYY=DATEADD(StDt1,'...

Dear Professionals, I started writing this blog more than a year ago. The purpose of this blog was to help the people with day to day coding in WebFOCUS. I also use the codes for my reference as well. Whenever I put some effort to resolve a problem or find some solution, and I feel it could be used in future also either by myself or it can benefit to others, I share it on this blog. As we all know feedback is always a good motivation. I request all of you to please share your feedback/ suggestions on the same if you have any. If you want me to share on some specific topic or some common issue. Is it really helping you? If not? What else we can do to make it helpful? If you have something to share, send it to my mail id er.pravin007@gmail.com. I'll publish it over there with your name as presenter. I long run, this can become a place where you can find code for most of your day to day solutions. In last 6-8 months, I started liking WebFOCUS 8 version (initially I found it h...

Almost all reports (including WebFOCUS) usually need to show Report Run Date and Time. WebFOCUS provides us the variables &DATE and &TOD(system variables) to get system date and time and use it in our report without calculating anything in the procedure. However in some of the report we need to show time stamp in a.m./p.m. format. I have created the logic and I feel that sharing it might save some hours of development time for developers looking for such requirement. Below is the piece of code you can put in your procedure and you can use &TOD2 in your report header. -SET &M=''; -SET &H1 = EDIT(&TOD,'99'); -SET &H2 = IF &H1 GT 12 THEN &H1 - 12 ELSE &H1; -SET &M = IF &H1 GT 12 THEN ' PM' ELSE ' AM'; -SET &TOD2 = &H2 | EDIT(&TOD,'$$$:99$') | EDIT(&TOD,'$$$:$$$99') | &M; -TYPE &TOD -TYPE &TOD2 Please share your thoughts/ feedback's.  Happy coding ...

I have come across the situation many a times where we need to filter report data for: 1) 6 months prior to selected/system date. 2) Report should pull data for date greater than last 30 days. 3) Any other situation where you need 30 days prior, 1 year future date, 3 months after date from system date etc. I feel it is worth sharing the below piece of code: -SET &DATEINPUT = EDIT('&DATEYYMD.EVAL','9999$$$$$$')||EDIT('&DATEYYMD.EVAL','$$$$$99$$$')||EDIT('&DATEYYMD.EVAL','$$$$$$$$99'); -TYPE &DATEINPUT -SET &DATE_30 = DATECVT((DATEADD((DATECVT(&DATEINPUT, 'I8YYMD', 'YYMD')), 'D', -30 )), 'YYMD', 'I8YYMD'); -TYPE IN: &DATEINPUT / OUT: &DATE_30 In above example, I calculated date which is 30 days prior to currect date. In place of 'D', we can have 'M', 'Y' and 'BD' for Months, Year and Business days. If you need to calcula...

WebFOCUS has lots of flexibility because it has option to write code in text editor. That is very unique feature in WebFOCUS which separates it from other reporting tools. There are certain occasions where we put lots of effort to achieve certain requirement in our report. But if you know the keywords, it can be done in minutes. Recently one of my colleague came to me asking for the solution to display leading zeroes in the report. He spent almost 3-4 hours and still continued. The requirement was to display 0.00 instead of .00. The solution was pretty simple. You need to write below code at start of your fex file: SET CENT-ZERO = ON It was very easy to do it but very difficult to find out the keyword. If you have to suppress leading zeroes it should be turned off. SET CENT-ZERO = OFF I thought it is worth sharing as it can save some time for your during coding. Please share your thoughts/ feedback's. Happy coding in WebFOCUS!!!!

Many times while working on the projects, we face some small issues which takes a long time to fix it or achieve the requirement even if it looks very small. For example concatenation of 2 columns seperated by a '-' and it should contain 1 space between '-' and second column. It looks very small but when I tried to do that, it took me a long time to find out a very simple solution. There are many example like that. I want to share such things with you which I faced in my day to day coding. Concatenation with space:   You can either do it in define or compute as given below: COUNTRY_CAR/A50=CAR||(' - ' |COUNTRY); User defined function in WebFOCUS:  If we need to perform repeative set of statements again and again, user defined funtion is a very good approach. Below is a simple example of function ADDNO to add 2 numbers and return the output: DEFINE FUNCTION ADDNO(NO1/D20.2, NO2/D20.2) ADDNO/D20.2 = NO1+NO2; END -RUN TABLE FILE CAR PRINT COUNTRY ...

We do create lot many report and many of them do have Subtotals and Grand total. Subtotals are mainly required in Sales, finance & accounting and in most of the reports where there are more factual data in the report. Surprisingly we always gets confused about the difference between SUB-TOTAL and SUBTOTAL & SUMMARIZE and RECOMPUTE. Now lets see what is the differences in between these: SUB-TOTAL and SUBTOTAL: 1) You can use the SUBTOTAL and SUB-TOTAL commands to sum individual values, such as columns of numbers, each time a named sort field changes value.  2) SUB-TOTAL displays a subtotal for all numeric values when the sort field changes value, and for any higher-level sort fields when their values change.  3) SUBTOTAL displays a subtotal only when the specified sort field changes value. It does not give subtotals for higher-level fields.  4) Both SUB-TOTAL and SUBTOTAL produce grand totals. You can suppress grand totals using the NOTOTAL command.   ...

Some times while creating a graph using web focus code we face problems in formatting the graph. For example – graph title, Font size, font name, Y-axis title, legend position, label rotation etc. To save the time in searching these things the below codes will be very useful. We just need to set the values as we want in the graph in set_values.fex and we can include the  set_values.fex  file to the fex file in our code before GRAPH FILE FILENAME command. And second fex reuse.fex we need to include after ON GRAPH SET GRAPHSTYLE * command (you can also put the reuse.fex codes to reuse.sty and include it at the same position). Code for set_values.fex: -*Set the value for the graph Title -DEFAULT &GRAPH_NAME='XYZ'; -*Set the value for X-axis Title -DEFAULT &O1_TITLE='ABC'; -*Set the value for Y1-Axis title -DEFAULT &Y1_TITLE='ABC'; -*Set the font Name for X-axis -DEFAULT &O1_label_n='Arial'; -*Set the font Size for X-...

In my Recent project, I had a requirement where I had to show subtotal based on the selection from launch page. And the Sub Total had both total as well as %'s to display. The percentage was nothing but for example. 100*Sum(Column A)/Sum(Column B). I thought to share this with all of you since it can be reused for similar requirement and can help others to save lot of time.  This situation could have handled by using RECOMPUTE (ON TABLE RECOMPUTE "OR" ON SORT_COLUMN  RECOMPUTE) concept. However there were some challenges with that.  1) First issue was when we do RECOMPUTE all the numeric columns will get RECOMPUTEd. So if RECOMPUTE is needed to only selected columns, all other should be changed to alpha format. That is not a feasible approach according to me.  2) If all alpha columns in the report are not sorted, Sub Total row will be coming in 2 rows. That is another issue. So to avoid RECOMPUTE and use SUBFOOT (which I prefer most because of it's flexibili...

For most of the reports in our WebFOCUS reporting world, we usually pull data from multiple tables and and generate the report. We all know that we can either Join Data Sources(JOIN) or Merge Data Sources(MATCH) to create report from multiple tables. Which one to choose from JOIN and MATCH? Now the question is what is the diffrence between the two and which one to choose from the 2 option. To explain this let's understand how does they process. JOIN: Basically JOIN is temporary, virtual connection between 2 or more physical or logical sources. Data in the first data source (host file) determines which records are retrieved from cross-reference file. When a record from the host file of a one-to-many join has no corresponding record(s) in the cross-reference file, the record is automatically excluded from the report output.  To control this, you can SET ALL to ON, in which case records from the host file, without corresponding records in the cross-reference file, will be disp...

Have you worked on creating graphical reports in WebFOCUS? Do you want to use google high charts in your WebFOCUS reports? The reason I like WebFOCUS over other reporting tool is it's adaptability and flexibility to integrate with other codes. You can write HTML, javascript, XML, Unix and lot more codes into your webFOCUS procedure. I was working on a POC with my colleague to implement google charts with webFOCUS. The Reason I chose that is because of look and feel on how chart is drawn on step by step basis. Just try it running once. It looks awesome.  Below are the code for data creation file(chart_data.fex) and highchart_test.fex: Fex File for Data -* File chart_data.fex TABLE FILE CAR SUM  SALES DEALER_COST BY COUNTRY -*WHERE FLG EQ 1 ON TABLE PCHOLD FORMAT XML END -****************************************************** Fex file for highchart -* File highchart_test.fex -HTMLFORM BEGIN <html xmlns="http://www.w3.org/1999/xhtml">   <hea...