Greetings very helpful folks out there!

I've been trying to create a report that just shows totals and I've had absolutely no luck with it! I'm using WebFOCUS 7.6.4 Report Painter. Here's how the FILE looks and how the REPORT is supposed to look:


FILE:
.
.
Type (Adult Burial or Baby Burial) - USED IN PART TO DETERMINE COL2 and COL3
BurialDate - USED FOR COL1; ALSO USED IN PART TO DETERMINE COL2 and COL3
CaseOpenedDate - USED IN PART TO DETERMINE COL5 and COL7
CaseClosedDate - USED IN PART TO DETERMINE COL6 and COL7
.
.

Note: I put '/' between each column below because my beautiful spacing went away the first time I posted this! Hopefully, you can read it now...

COL1 / COL2 / COL3 / COL4 / COL5 / COL6 / COL7
REPORT:
Month / Total Adult Burials / Total Baby Burials / Total Adult+Baby / # New Cases / # Cases Closed / Open Cases
January / 5 / 2 / 7 / 20 / 35 / 18
.
.
.
December / 8 / 4 / 12 / 7 / 23 / 9


I've tried using Defined variables and Across but it only works with Type. As soon as I added New Cases, it started looking very wrong!

I hope I've asked this in the right way. My apologies in advance if I have not.

Sincerely,
Sam

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

Are you summing the 'burials' by the month?

Posting your code might help in the helping.

Leah, thanks so much for the quick reply!!

Yes, the totals being displayed are by month. I have the Month column marked as 'By'.

I will include my code but please make note of 2 things:
1. It only includes the Adult and Baby Burials (plus Adult and Baby Cremations which I left off of the example since it's all derived from the same TYPE field and I have this part working.) I don't have any of my feeble attempts to add the Case information still included in the code.
2. The code was automatically generated because I am using the Report Painter.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
BEGIN CODE


-* File newfex.fex
SET EMPTYREPORT = ON

DEFINE FILE TBLBEREAVEMENTBURIAL
DIVIDEDTYPE/A25 WITH TYPE=
IF TYPE EQ 'Adult Burial' THEN 'Adult Burial HC Cemetery' ELSE
IF TYPE EQ 'Baby Burial' THEN 'Baby Burial HC Cemetery' ELSE
IF TYPE EQ 'Adult Cremation' THEN 'Adult Cremation' ELSE
IF TYPE EQ 'Baby Cremation' THEN 'Baby Cremation' ELSE
IF CEMETERY EQ 'VA Cemetery' THEN 'VA Cemetery' ELSE
IF CEMETERY EQ 'HC Cemetery' THEN 'HC Cemetery';
EXTR_DT_FR_DTTIME/YYMD=HDATE(DATEBURIAL, 'YYMD');
CNVRT_DT_TO_ALPHA/A30=
HCNVRT(DATEBURIAL,
'HYYMDm', 30, CNVRT_DT_TO_ALPHA);
EXTR_MNTH_FR_PREV_EXTR/TRM=DATECVT(EXTR_DT_FR_DTTIME, 'YYMD', 'M');
EXTR_YEAR_FR_DT/YY=DATECVT(EXTR_DT_FR_DTTIME, 'YYMD', 'YY');
DATETRAN_DISPLAY_DATE/A30=
DATETRAN(EXTR_DT_FR_DTTIME, '(YYMD)',
'(TRYY)', 'EN', 25, CNVRT_DT_TO_ALPHA);
DISPLAY_DATE/A30=
DATETRAN(EXTR_DT_FR_DTTIME, '(YYMD)',
'(TRYY)', 'EN', 25, DISPLAY_DATE);
EXTR_MMYY_FR_DT/MYY=DATECVT(EXTR_DT_FR_DTTIME, 'YYMD', 'MYY');
EXTR_DISPLAY_DATE/A30=
DATETRAN(EXTR_MMYY_FR_DT, '(MYY)',
'(TRc)', 'EN', 25, EXTR_DISPLAY_DATE);
VACOUNT/A20=IF CEMETERY EQ 'VA Cemetery' AND TYPE EQ 'Adult Burial' THEN 'VA Cemetery';
HCCOUNT/A12=IF CEMETERY EQ 'HC Cemetery' THEN 'HC Cemetery';
DIVIDEDCEM/A15=
IF CEMETERY EQ 'VA Cemetery' THEN 'VA Cemetery' ELSE
IF CEMETERY EQ 'HC Cemetery' THEN 'HC Cemetery';
END
TABLE FILE TBLBEREAVEMENTBURIAL
SUM
EXTR_DISPLAY_DATE AS 'Month'
BY EXTR_MMYY_FR_DT NOPRINT
SUM
CNT.TYPE AS ''
BY EXTR_MMYY_FR_DT NOPRINT
ACROSS DIVIDEDTYPE
HEADING
""
FOOTING
""
ON TABLE SUBFOOT
""
WHERE ( TBLBEREAVEMENTBURIAL.TBLBEREAVEMENTBURIAL.DATEBURIAL GE DT(&FROMDATEBURIAL.From (YYYYMMDD).) ) AND ( TBLBEREAVEMENTBURIAL.TBLBEREAVEMENTBURIAL.DATEBURIAL LE DT(&TODATEBURIAL.To (YYYYMMDD).) );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=DATA,
ACROSSCOLUMN=N1,
COLOR='BLACK',
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TITLE,
ACROSSCOLUMN=N1,
COLOR='BLACK',
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=ACROSSTITLE,
ACROSS=1,
COLOR='WHITE',
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

END CODE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thanks very much!!
Sam

So you are counting type by date across the cemetary. As type is defined on a record level and you really want to know how many different burials for each type, you don't count type. You count records across the cemetary and across type. Another way is to define a series of fields holding the value of 1 if it is that type otherwise zero and then sum these fields across the cemetary in the order you want them to appear. In the midst of a production support issue, so sorry this is so short an answer, hope it makes sense.

Hey Leah,

This is a little tricky because it's displaying unrelated information totals on the same MONTH line. It's showing:
1. TYPE totals and it's showing
2. CASE totals and it's showing
3. CEMETERY totals.

All three of these fields are on the same record in the file. Maybe I should create SYNONYM files for each of the different fields and then just drag/drop them from the synonyms to the report and make them CNT fields.

Thanks and good luck on your issue!
Sam

Synonyms might help, but I think if you can get your information defined related to the displayed date it might help. Have you tried changing the sort to the defined display date field to see what happens?

Hi Leah,

No, I'm sorry. I have not tried changing the sort because I don't know exactly what you mean. I think that I'm doing the following:

'by' Burial Date

'across' totalling based on Type, Cemetery, Case Open Date and Case Close Date

Sincerely,
Sam

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

Sam, sometimes talking makes it easier. If I understand you are trying to do a number 'burials' opened in time frame and closed in time frame by the cemetary and type of burial.

If I had to do this, I'd create a counter for opened and closed based on the type and date, Sum the counters by date cemetary and type hold the data and then from the hold file create the report printing the summed fields by and across what I needed.

Of course the same can be done with defines and computes in one report, but I think your issue is trying to separate 'open' and 'closed'

Am I wrong or right on the last comment?

Leah, thanks for your patience in this!!

Actually, I'm not doing the number of burials opened and closed in time frame by the cemetery.
It's more like:

total burials in a time frame
total opened in a time frame
total closed in a time frame
total cemetery A in a time frame
total cemetery B in a time frame
etc.


Does that make sense?

Thanks again for helping me!!!
Sam

Yes it does, and I think doing a field for each that contains a 1 OR 0 FOR burials and a 1 OR 0 for opened and a 1 OR 0 for closed and 1 OR 0 one for cemetary and then sort by your month might be what you need.
DEFINE ...
CEMETARYA / I5 = IF CEMETARY EQ 'VA....' then 1 ELSE 0;
...
END
TABLE FILE ...
SUM BURIALS OPENED CLOSED CEMETARYA CEMETARYB
BY MONTH

Sort of a sample

Am I the only one who got the shivers reading this post? :-)

Probably, although it does seem to have reached a dead end, so perhaps we should bury it now?

lol

T

good one!

For the sake of not putting us all to shame I agree with Tony. Sam, every once in a while a post seems to cause the rest of us to go into strange mode of comments, nothing against the originator, so don't take it personally.

Hi Leah,

I tried adding the following virtual columns in the Master File:

NAME........FORMAT........EXPRESSION
..............................................
TYPEAB........I1.....TYPE EQ 'Adult Burial'
TYPEBB........I1.....TYPE EQ 'Baby Burial'
CEMETERYHC....I1.....CEMETERY EQ 'HC Cemetery'
CEMETERYVA....I1.....CEMEBERY EQ 'VA Cemetery'

Then I opened my Report Painter and dragged/dropped TYPEAB, TYPEBB, CEMETERYHC and CEMETERYVA onto the report a SUM's. The report printed as follows. FEBRUARY was correct but JANUARY was only half correct.


.....Month.....TYPEAB....TYPEBB....CEMETERYVA....CEMETERYHC...
JANUARY, 2008.....*.........4...........0..............*.......
FEBRUARY, 2009....1.........0...........0..............1.......


Here's what it should have been:

.....Month.....TYPEAB....TYPEBB....CEMETERYVA....CEMETERYHC...
JANUARY, 2008....17.........4...........0..............*.......
FEBRUARY, 2009....1.........0...........0..............1.......

It seems I'm getting closer maybe!

Thanks,
Sam

No offense taken at all! Sorry about the delayed reaction. I was called off on something in the midst of my reply and didn't get in on the rest of the comments until after I had finished posting.

I completely understand and share your feelings about this topic! The good news is, this service is being provided for folks who could otherwise not afford anything for their deceased loved ones. I started to change it all to CAR references but I'm such a novice I thought I'd get too confused.

Thanks again to you all!
Sam

If January is 'wrong' is there a way you can examine your actual data, maybe the date in it is 'wrong'. Sound as if you are getting there.

Hey Leah,

I ran a SQL query to check the data and to get the actual numbers so I think the data is ok.

Thanks,
Sam

Leah, one more thing...

When I run the 'Sample Data' for the Virtual Column Calculator on each of the new fields, the results show up properly with all the 1's and 0's.

Thanks,
Sam

If the data is okay perhaps it is how you are coming up with the 'date to sort by'. Is the date field in the database a true date field, a 'smart date'. You are doing a lot of manipulating of it you may or may not need to do if it is.

Leah,

It's a DATETIME field. The only thing I did to it was to extract just the date for comparison.

The fact that for some months the AdultBurial total is correct is REALLY confusing!

Thanks,
Sam

Okay, date time experts, I don't work with that kind of date in my area. Any help for Sam. Or hint as to why a date wouldn't covert correctly?

I'm not sure it's the date.

I noticed that in the report:

CEMETERYVA is always right
TYPEBC is always right
TYPEAC is always right
TYPEBB is sometimes right
TYPEAB is rarely right
CEMETERYHC is right only once


Also, I reran the exact same report with all the columns as 'Detail' instead of 'Sum' and everything showed up as expected:

Example lines from the report:
Month / TYPEAB / TYPEAC / TYPEBB / TYPEBC / CEMETERYVA / CEMETERYHC
MAY, 2004 / 4 / 0 / 6 / 0 / 0 / *
.
.
.
JANUARY, 2008 / * / 1 / 4 / 0 / 4 / *
(the first * should have been 17)

Associated data (‘Detail’ RUN of the same report)
MONTH TYPEAB TYPE DATEBURIAL
MAY, 2004 1 Adult Burial 2004/05/07 00:00:00.000
MAY, 2004 1 Adult Burial 2004/05/07 00:00:00.000
MAY, 2004 1 Adult Burial 2004/05/07 00:00:00.000
MAY, 2004 1 Adult Burial 2004/05/28 00:00:00.000
...
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/04 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/11 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/09 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/09 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/09 00:00:00.000
JANUARY, 2008 1 Adult Burial 2008/01/09 00:00:00.000

Thanks,
Sam

Then, I'd look at your virtual field definitions closely to ensure you are getting them defined properly.

quote:

NAME........FORMAT........EXPRESSION
..............................................
TYPEAB........I1.....TYPE EQ 'Adult Burial'
TYPEBB........I1.....TYPE EQ 'Baby Burial'
CEMETERYHC....I1.....CEMETERY EQ 'HC Cemetery'
CEMETERYVA....I1.....CEMEBERY EQ 'VA Cemetery'

Try making your filed formats larger. I1 will only display from 0-9. Try an I3 or I4.

BTW... You might want to use the Forum FOCUS/WebFOCUS instead of Rules, Regulations, and FAQs.

Sam,

Apologies for my above comments, just a warped sense of humour!

Karen (the Piipster) is absolutely spot on as usual, but with you being a novice I'll try and expand and explain.

When you table a data source using the SUM verb, the output data will only be displayed using the originating data formats unless you code it otherwise. Therefore all your count fields with a format of I1 (one character integer) will only ever be displayed as a value of 0 - 9. Anything above these digits will be display as an overflow symbol - the asterisk - to show that the data cannot be displayed in the given output format.

Your way forward is to either change the master file description for this data source by increasing the I1 to something like I3 or I4 (as Karen says) - let's hope you never overflow that range in one year , or if that is not an option, then the alternative is to change it on the fly.

I'll knock up an example, if I get time, to demonstrate it.

Also, as Karen mentions, this particular area of the Forum is not the ideal one in which to post questions regarding WF syntax etc. Try the WebFOCUS forum instead. This one is for Rules and Regs on using the Forums.

T

Sam,

Below is an sample of built using some of the data you listed - I had to add / delete some to get the values in your report.

Ignore the first portion as all this does is allow me to replicate your data into a temporary table for reporting.

The important piece is in the actual DEFINE and TABLE. You will see that the DEFINEs for the TYPEAB etc. has been declared as an I9 (always allow for expansion) whereas those for the Cemeteries are still shown as I1.

Within the the TABLE, you will see that the Cemetery countfields have /I9 appended to them. This is how your "change format on the fly" and your solution lies, either in the change to the master file or the report code. If you have more than one report from this data then I would suggest the master file change would be more benefiical.

FILEDEF BURYMAS DISK BURYDATA.mas
-RUN
-WRITE BURYMAS FILE=BURYDATA,SUFFIX=FOC
-WRITE BURYMAS SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE BURYMAS FIELD=FOCLIST     ,FOCLIST     ,I2     ,I2     ,$
-WRITE BURYMAS FIELD=MONTH       ,MONTH       ,A15    ,A15    ,$
-WRITE BURYMAS FIELD=CNT         ,CNT         ,I1     ,I1     ,$
-WRITE BURYMAS FIELD=TYPE        ,TYPE        ,A15    ,A15    ,$
-WRITE BURYMAS FIELD=CEMETERY    ,CEMETERY    ,A15    ,A15    ,$
-WRITE BURYMAS FIELD=BURIAL_DATE ,BURIAL_DATE ,HYYMDs ,HYYMDs ,$
-RUN
CREATE FILE BURYDATA
MODIFY FILE BURYDATA
FIXFORM FOCLIST/A3 MONTH/A14 CNT/A2 TYPE/A17 CEMETERY/A16 BURIAL_DATE/A23
DATA
 1 JANUARY, 2008 1 Adult Cremation  VA Cemetery     2008/01/04 00:00:00.000
 2 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
 3 JANUARY, 2008 1 Adult Burial     VA Cemetery     2008/01/04 00:00:00.000
 4 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
 5 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
 6 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
 7 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
 8 JANUARY, 2008 1 Adult Burial     VA Cemetery     2008/01/04 00:00:00.000
 9 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
10 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/04 00:00:00.000
11 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/11 00:00:00.000
12 JANUARY, 2008 1 Adult Burial     VA Cemetery     2008/01/11 00:00:00.000
13 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/11 00:00:00.000
14 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/11 00:00:00.000
15 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/11 00:00:00.000
16 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/11 00:00:00.000
17 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/09 00:00:00.000
18 JANUARY, 2008 1 Adult Burial     HC Cemetery     2008/01/09 00:00:00.000
19 JANUARY, 2008 1 Baby Burial      HC Cemetery     2008/01/09 00:00:00.000
20 JANUARY, 2008 1 Baby Burial      HC Cemetery     2008/01/09 00:00:00.000
21 JANUARY, 2008 1 Baby Burial      HC Cemetery     2008/01/09 00:00:00.000
22 JANUARY, 2008 1 Baby Burial      HC Cemetery     2008/01/09 00:00:00.000
END
-RUN
DEFINE FILE BURYDATA
  TYPEAB/I9     = (TYPE EQ 'Adult Burial') * CNT;
  TYPEAC/I9     = (TYPE EQ 'Adult Cremation') * CNT;
  TYPEBB/I9     = (TYPE EQ 'Baby Burial') * CNT;
  TYPEBC/I9     = (TYPE EQ 'Baby Cremation') * CNT;
  CEMETERYHC/I1 = (CEMETERY EQ 'HC Cemetery') * CNT;
  CEMETERYVA/I1 = (CEMETERY EQ 'VA Cemetery') * CNT;
END
TABLE FILE BURYDATA
SUM TYPEAB
    TYPEAC
    TYPEBB
    TYPEBC
    CEMETERYVA/I9
    CEMETERYHC/I9
 BY MONTH
END

T

Piipster, Tony, yall are GENIUSES!!! Things are working like a charm now!!! Tony, I'm keeping your detailed explanations in my notes as they were quite helpful in my understanding of how things work.

And, Tony, as for your apologies for above comments - - none needed! Those jokes killed! Besides, from where I was sitting, things were looking pretty grave...

I was actually aiming for the FOCUS/WebFOCUS Forum and I have no idea how I missed and ended up in Rules, Regulations and FAQS... LOL! My sincere apologies!

Leah, Tony and Piipster, I'm so glad I 'e-ran' in to all of yall - - I've learned so much and I deeply appreciate you taking time out of your busy schedules for me. I'm sure I'll be stumped again soon and I hope to get guidance from you again (in the proper Forum of course!)

Very sincerely,
Sam

Sam,

You are most welcome.

From the "Y'all" I take it that you work in the dead centre of Texas? lol

T