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] Need help with a report request

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] Need help with a report request
 Login/Join
 
Member
posted
Hi All,

I have a report request.
Suppose the source data is:
Name Effective_dt Status
N1 May 2,2009 Failed
N1 April 1,2009 Failed
N1 March 28,2009 Succeed
N1 March 20,2009 Failed
N1 March 10,2009 Succeed
N1 March 8,2009 Failed
N2 May3,2009 Failed
N2 April 2,2009 Failed

The output should be:
N1 May 2,2009 Failed
N1 April 1,2009 Failed
N2 May3 ,2009 Failed
N2 April 2,2009 Failed

Basically, the report should show all the failed records after a most recent succeed for each Name. If none of the record for a name is Succeed, it will include all. I hope my requirement description is clear.

Any suggestions on this?

Thanks and Regards,

Alu

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


WF Server: 5.3.2, 7.1.6 on Unix, ReportCaster, Self-Service, MRE, Java
Output: HTML,PDF,Excel
WF Client: Tomcat, Servlet
 
Posts: 17 | Registered: November 03, 2008Report This Post
Virtuoso
posted Hide Post
Suggested approach:

Using suitable defines and a multiverb request, count successes by name, and Print records (and a Compute discussed below) by name by date.

The compute: assign a sequence number, resetting it with name, and bumping it when the record is a Success.

- If there are no successes for a given Name, then all its records are failures, and all have zero as their sequences number.
- If there are any successes, all failure records are sequenced to indicate the most recent success number, with the last group's sequence number matching the name's Count of successes.

Either way, you want to print all of a Name's Failure records whose seq matches the Name's Success count. An IF TOTAL or WHERE TOTAL will do that.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
<JG>
posted
I took the liberty of reformatting the data to be able to use it

Take a look at this

 FILEDEF STATUS DISK STATUS.FTM (APPEND
FILEDEF MSTATUS DISK STATUS.MAS (APPEND
-RUN
-WRITE STATUS N1 May 2,2009        Failed
-WRITE STATUS N1 April 1,2009      Failed
-WRITE STATUS N1 March 28,2009     Succeed
-WRITE STATUS N1 March 20,2009     Failed
-WRITE STATUS N1 March 10,2009     Succeed
-WRITE STATUS N1 March 8,2009      Failed
-WRITE STATUS N2 May 3,2009        Failed
-WRITE STATUS N2 April 2,2009      Failed
-*
-WRITE MSTATUS FILE=STATUS, SUFFIX=FIX,$
-WRITE MSTATUS  SEGNAME=STATUS, SEGTYPE=S0
-WRITE MSTATUS FIELD=NAME,ALIAS=E01,ACTUAL=A2,USAGE=A2,$
-WRITE MSTATUS FIELD=F1,ALIAS=E02,ACTUAL=A1,USAGE=A1,$
-WRITE MSTATUS FIELD=DATE,ALIAS=E03,ACTUAL=A17,USAGE=A17,$
-WRITE MSTATUS FIELD=F2,ALIAS=E04,ACTUAL=A1,USAGE=A1,$
-WRITE MSTATUS FIELD=STATUS,ALIAS=E05,ACTUAL=A7,USAGE=A7,$
-WRITE MSTATUS DEFINE MONTH/A9=GETTOK(DATE, 17, 1, ' ', 9, 'A9');,$
-WRITE MSTATUS DEFINE IMONTH/I2=DECODE MONTH('January' 1 'February' 2 'March' 3 'April' 4
-WRITE MSTATUS                               'May' 5 'June' 6 'July' 7 'August' 8
-WRITE MSTATUS                               'September' 9 'October' 10 'November' 11 'December' 12 ELSE 99);,$
-WRITE MSTATUS DEFINE DAY/A2=GETTOK((GETTOK(DATE, 17, 1, ',', 13, 'A13')), 13, 2, ' ', 2, 'A2');,$
-WRITE MSTATUS DEFINE YEAR/A4=GETTOK(DATE, 17, -1, ',', 4, 'A4');,$
-*
TABLE FILE STATUS
PRINT DAY MONTH YEAR
BY NAME
BY HIGHEST STATUS
BY  YEAR
BY  IMONTH
BY  DAY
ON TABLE HOLD AS SORTED FORMAT FOCUS
END
-RUN
FILEDEF REQUIRED DISK REQUIRED.FTM (APPEND
-RUN
TABLE FILE SORTED
SUM MAX.STATUS NOPRINT
    COMPUTE ALLFAILED/A1= IF C1 EQ 'Failed' THEN 'Y' ELSE 'N'; NOPRINT
BY NAME NOPRINT
PRINT NAME DAY MONTH IMONTH YEAR STATUS
WHERE TOTAL ALLFAILED EQ 'Y'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS REQUIRED FORMAT ALPHA
END 
-RUN
TABLE FILE REQUIRED
BY NAME
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
TABLE FILE SORTED
SUM LST.DAY LST.IMONTH LST.YEAR 
   COMPUTE KEY1/A8=C3 || EDIT(C2) || C1; 
BY NAME
WHERE STATUS EQ 'Succeed'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WANTED FORMAT FOCUS INDEX NAME
END
-RUN
JOIN NAME IN SORTED TO NAME IN WANTED AS J1
DEFINE FILE SORTED
IDAY/I2=EDIT(DAY);
KEY2/A8=YEAR || EDIT(IMONTH) || EDIT(IDAY);
END
TABLE FILE SORTED
PRINT DAY MONTH IMONTH YEAR STATUS
BY NAME
BY STATUS NOPRINT
WHERE NOT NAME IN  FILE SAVE
WHERE STATUS EQ 'Failed'
WHERE KEY2 GE KEY1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS REQUIRED
END
-RUN
TABLE FILE REQUIRED
PRINT  
    COMPUTE DATE/A17=MONTH | DAY | ',' | YEAR; 
    COMPUTE IDAY/I2=EDIT(DAY); NOPRINT
    STATUS
BY NAME
BY HIGHEST YEAR NOPRINT
BY HIGHEST IMONTH NOPRINT
BY HIGHEST IDAY NOPRINT
END

 
 
Report This Post
Expert
posted Hide Post
You could reduce your processing by converting the DATE field to a "real" date field (if it isn't a smart date already) and then parse the data twice -

Using the begining of JG's code
-WRITE MSTATUS DEFINE STAT_DATE/HYYMDS = HINPUT(17, STRREP(17, DATE, 1, ',', 1, ' ', 17, 'A17'), 8, STAT_DATE);
-RUN
TABLE FILE STATUS
BY NAME
BY HIGHEST 1 STAT_DATE AS MAX_SUCCEED
WHERE STATUS EQ 'Succeed'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WANTED FORMAT FOCUS INDEX NAME
END
-RUN
JOIN CLEAR *
JOIN LEFT_OUTER NAME IN STATUS TO NAME IN WANTED AS J1
TABLE FILE STATUS
PRINT *
BY NAME
WHERE STATUS EQ 'Failed'
WHERE STAT_DATE GT MAX_SUCCEED
   OR MAX_SUCCEED IS MISSING
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
I wasn't going to give away the store -- but what I originally suggested requires just one pass:

-****** setup ********
EX -LINES 7 EDAPUT MASTER,TRIALS,CV,FILE
FILE=TRIALS,SUFFIX=FOC,$
SEGMENT=ONE,SEGTYPE=S1,$
FIELD=NAME ,,A10,$
SEGMENT=TWO,SEGTYPE=S1,PARENT=ONE,$
FIELD=DATE ,,MDYY,$
FIELD=RESULT,,A8,$
-RUN
CREATE FILE TRIALS
MODIFY FILE TRIALS
FREEFORM NAME DATE RESULT
MATCH * KEYS
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA
N1,5/2/2009,Failed,$
N1,4/1/2009,Failed,$
N1,3/28/2009,Succeed,$
N1,3/20/2009,Failed,$
N1,3/10/2009,Succeed,$
N1,3/8/2009,Failed,$
N2,5/3/2009,Failed,$
N2,4/2/2009,Failed,$
END
-RUN

-******** report *********
DEFINE FILE TRIALS
  SUCCESS/I3S= RESULT EQ 'Succeed';
  FAILURE/I3S=NOT SUCCESS;
END
TABLE FILE TRIALS
  WRITE SUM.SUCCESS 
          NOPRINT
    BY NAME
  PRINT RESULT
        SUCCESS
          NOPRINT
      AND COMPUTE SEQ  /I3= SUCCESS + (NAME EQ LAST NAME)*(LAST SEQ);
          NOPRINT
      AND COMPUTE TAKE/I3S= FAILURE AND ( SEQ EQ C1 );
          NOPRINT
    BY NAME BY DATE
  IF TOTAL TAKE IS TRUE
END

Comment out the NOPRINTs and the IF TOTAL to see how it operates.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Thank you all.
I am eager to try JG's apporach. When I copied the code to my Webfocus studio, I got error message "(FOC205) The Description cannot be found for file named:status". I did a search, it may be because I didn't use the FILEDEF command properly.

For this report request, I have SQL query can do it(see following code, I simply it a litter bit for easy to post).

[CODE]
SELECT name, effective_dt,status
FROM TABLE_ITEM item,
where STATUS LIKE '%Failure'
AND effective_dt BETWEEN TO_DATE('2007-11-27 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2007-11-27 23:59:59','YYYY-MM-DD HH24:MI:SS')
AND (TO_DATE(effective_dt,'DD-MM-YYYY HH24:MI:SS') >
(
SELECT TO_DATE(MAX(item_inner.oi_effective_dt),'DD-MM-YYYY HH24:MI:SS')
FROM table_item item_inner,
AND item_inner.STATUS LIKE '%Success'
AND item_inner.name = item.name
)
OR NOT EXISTS
(
SELECT 1
FROM table_item item_inner1
item_inner1.STATUS LIKE '%Success'
AND item_inner1.name = item.name
)
)

[CODE]

But it is so so so slow even when I only have a date range of one day and run it in TOAD. Actually, not just slow, it make the TOAD dead there.

I am hopeing if I do it in WebFOCUS like your guys's suggestion, it will be better. I will try and let you know.

Thanks and Regards

alu


WF Server: 5.3.2, 7.1.6 on Unix, ReportCaster, Self-Service, MRE, Java
Output: HTML,PDF,Excel
WF Client: Tomcat, Servlet
 
Posts: 17 | Registered: November 03, 2008Report This Post
Expert
posted Hide Post
quote:
but what I originally suggested requires just one pass
Nice solution Jack and just goes to show that there's more than one way to skin a cat (no animals were injured in that statement Wink).

Alu,

Perhaps if you do not grasp how to run JG's or the other code samples then perhaps a visit to first base and some training might be in order? Try running as is to start and then try and understand what it is achieving then take what you learn and apply it to your situation.

BTW, if your code is killing toad then, again, go back to basics.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Hi All,

WebFOCUS is terrific and you guys are genius.
I can't believe my eyes when I get my expecting
results with the few line of codes from j.gross.

Thank you very very much. It seems like I can have a good sleep tonight. Yesterday I didn't.


WF Server: 5.3.2, 7.1.6 on Unix, ReportCaster, Self-Service, MRE, Java
Output: HTML,PDF,Excel
WF Client: Tomcat, Servlet
 
Posts: 17 | Registered: November 03, 2008Report This Post
Virtuoso
posted Hide Post
Or even more succinctly,
-******** report *********
DEFINE FILE TRIALS
  SUCCESS/I3S= RESULT EQ 'Succeed';
  FAILURE/I3S=NOT SUCCESS;
END
TABLE FILE TRIALS
  WRITE SUM.SUCCESS      NOPRINT
    BY NAME
  PRINT RESULT
        SUCCESS          NOPRINT
        FAILURE          NOPRINT
      AND COMPUTE SEQ  /I3= SUCCESS + (NAME EQ LAST NAME)*(LAST SEQ);          NOPRINT
    BY NAME 
    BY TOTAL HIGHEST 1 SEQ
    BY DATE
  IF TOTAL FAILURE IS TRUE
END


("If Total Failure..." -- I couldn't resist.)

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
quote:
TOTAL FAILURE

ROFL Good One

Very topical Confused

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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] Need help with a report request

Copyright © 1996-2020 Information Builders