I am only using whatever works in the GUI portion of the tool...not manipulting anything with SQL behind the scenes....

I set up a report. I amperstanded things, set across fields etc etc. I am now trying to turn on subtotals. I clicked the one column then clicked the subtotal icon. I then ran the report. It worked. I proceeded to turn subtotals on for other colums but they do not always appear in the design and sometimes even when they do the do not appear on the report.

Its becoming frustrating because I am either doing something wrong or this tool is very inconsistent..I am hoping the former!

Ideally t a subtotal will appear for ANY sort field the user adds to the report...

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

I'll stand behind "the tool", especially for something as simple as this. PLease post your code, between the LT and GT brackets from the above tool bar.

This is the source code...it works fine if I choose Orig Fsc..it subtotals. If I click any other columns then click the subtotal icon, sometimes I'll see the subtotal pop up on the gui design screen...sometime I will not. Either way I never get additional subtotals when I run the report.

TABLE FILE TBLTEMP_BUDGACTUALDETAILS
SUM
&RP_FN01S_Sum11.(AND(,,,)).Please select sum field(s).
BY LOWEST &RP_FN01B_Sort1.(BY(,,,,,,,,,,,,)).Please select sort field(s).
ACROSS LOWEST &RP_FN01A_SortAcross1.(ACROSS(,,)).Please select across field(s).

ON TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.ORIGFSCNM SUBTOTAL AS '*TOTAL'
HEADING
"&TITLE"
WHERE ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR EQ &FISCALYEAR.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR IN tbltemp_budgactualdetails)).Select Fiscal Year or Years. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY IN tbltemp_budgactualdetails)).Enter Specialty or Specialties. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME EQ &LASTOFREPORTING_NAME.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME IN tbltemp_budgactualdetails)).Enter Provider or Providers. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP EQ &GROUP.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP IN tbltemp_budgactualdetails)).GROUP. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PERIOD EQ &PERIOD.(OR(FIND PERIOD IN TBLTEMP_BUDGACTUALDETAILS)).PERIOD. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.RPTBANAME EQ &RPTBANAME.(OR(FIND RPTBANAME IN TBLTEMP_BUDGACTUALDETAILS)).RPTBANAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.NEWVISITINDICATOR EQ &NEWVISITINDICATOR.(OR(FIND NEWVISITINDICATOR IN TBLTEMP_BUDGACTUALDETAILS)).NEWVISITINDICATOR. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PROC_CAT_NAME EQ &PROC_CAT_NAME.(OR(FIND PROC_CAT_NAME IN TBLTEMP_BUDGACTUALDETAILS)).PROC_CAT_NAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LOCATIONNM EQ &LOCATIONNM.(OR(FIND LOCATIONNM IN TBLTEMP_BUDGACTUALDETAILS)).LOCATIONNM. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.HOSPITALNM EQ &HOSPITALNM.(OR(FIND HOSPITALNM IN TBLTEMP_BUDGACTUALDETAILS)).HOSPITALNM. );
ON TABLE SET ONFIELD IGNORE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
INCLUDE = IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/en/combine_templates/ENblue_theme.sty,
$
TYPE=REPORT,
COLUMN=N0,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,
WRAP=6.000000,
$
ENDSTYLE
END

7702

OOOOOOOHHH MY !!

I can see why you want to use "the tool" ....

Have you taken any of the courses fro creating reports? If not, you should consider it.

You won't find many folks on here who can give step-by-step help on using "the tool", because most of us are old fogeys who learned to code by hand. I really only use it for styling once I've written the report proper.

Anyway, the code to create a subtotal is:

ON fieldname SUBTOTAL

and you can put it pretty near anywhere in your report, although somewhere just below the WHERE statement is fairly typical. If you just type it in the text view it will appear by magic in the report painter view. You repeat that for each BY field you want to subtotal.

If you only want to subtotal some fields and not others then it's

ON fieldname SUBTOTAL field3 field5 field6 fieldx etc.

The fieldname after the ON has to be the name of a sort field.

The trickiest part in your case is that WF now likes you to use fully-qualified field names, but if they are unique you can get away without all the FILENAME.SEGNAM.FIELDNAME stuff - and if you subsequently open the report in report painter it will mostly figure out all that stuff for you anyway. You have immensely long qualified fieldnames - eg: TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.NEWVISITINDICATOR - and a very complicated WHERE clause.

Personally, I'd elect to make the code a lot easier to read and maybe just slightly less efficient to run by using a DEFINE to create a short name for each of those long field names and use that short name in the report. It would be a whole lot easier to read.

Just my 2 cents ....

This message has been edited. Last edited by: George Patton,

Well thanks. I have had some reporting courses. The frustrating thing is they show you all this fancy stuff..but, as you mentioned, when you ask how specifice cool things were done...it always with extra hidden code. The GUI is sort of a let down in that respect. Plus there is no manual I know of for coding. I'll try to absorb what you wrote and give it a try....THANKS!

George,
this i sthe crazy crap that drives us nuts. After the WHERE I added: the SUBTOTAL you descibed for TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.RPTBANAME

I ran the report. The selection area opened but all the across fields were gone. I picked a few things and ran the report...it immediately ended in an error. The below code is all thats left in the report source....should have back the sucker up I guess.

Sorry but this thing seam filled with bugs...where did my orifinal code go?


TABLE FILE TBLTEMP_BUDGACTUALDETAILS
WHERE ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR EQ &FISCALYEAR.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR IN tbltemp_budgactualdetails)).Select Fiscal Year or Years. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY IN tbltemp_budgactualdetails)).Enter Specialty or Specialties. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME EQ &LASTOFREPORTING_NAME.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME IN tbltemp_budgactualdetails)).Enter Provider or Providers. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP EQ &GROUP.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP IN tbltemp_budgactualdetails)).GROUP. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PERIOD EQ &PERIOD.(OR(FIND PERIOD IN TBLTEMP_BUDGACTUALDETAILS)).PERIOD. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.RPTBANAME EQ &RPTBANAME.(OR(FIND RPTBANAME IN TBLTEMP_BUDGACTUALDETAILS)).RPTBANAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.NEWVISITINDICATOR EQ &NEWVISITINDICATOR.(OR(FIND NEWVISITINDICATOR IN TBLTEMP_BUDGACTUALDETAILS)).NEWVISITINDICATOR. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PROC_CAT_NAME EQ &PROC_CAT_NAME.(OR(FIND PROC_CAT_NAME IN TBLTEMP_BUDGACTUALDETAILS)).PROC_CAT_NAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LOCATIONNM EQ &LOCATIONNM.(OR(FIND LOCATIONNM IN TBLTEMP_BUDGACTUALDETAILS)).LOCATIONNM. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.HOSPITALNM EQ &HOSPITALNM.(OR(FIND HOSPITALNM IN TBLTEMP_BUDGACTUALDETAILS)).HOSPITALNM. );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
INCLUDE = IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/en/combine_templates/ENblue_theme.sty,
$
TYPE=REPORT,
COLUMN=N0,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,
WRAP=6.000000,
$
ENDSTYLE
END

7702

I should ay where did my GUI go...it shows NOTHING on design page anymore.....and I do not see the SUBTOTAL I added.

ok, I shut the thing down and reopened it. Now this is the only code in there....
TABLE FILE TBLTEMP_BUDGACTUALDETAILS
WHERE ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR EQ &FISCALYEAR.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.FISCALYEAR IN tbltemp_budgactualdetails)).Select Fiscal Year or Years. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.IDXSPECIALTY IN tbltemp_budgactualdetails)).Enter Specialty or Specialties. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME EQ &LASTOFREPORTING_NAME.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME IN tbltemp_budgactualdetails)).Enter Provider or Providers. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP EQ &GROUP.(OR(FIND TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.GROUP IN tbltemp_budgactualdetails)).GROUP. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PERIOD EQ &PERIOD.(OR(FIND PERIOD IN TBLTEMP_BUDGACTUALDETAILS)).PERIOD. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.RPTBANAME EQ &RPTBANAME.(OR(FIND RPTBANAME IN TBLTEMP_BUDGACTUALDETAILS)).RPTBANAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.NEWVISITINDICATOR EQ &NEWVISITINDICATOR.(OR(FIND NEWVISITINDICATOR IN TBLTEMP_BUDGACTUALDETAILS)).NEWVISITINDICATOR. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.PROC_CAT_NAME EQ &PROC_CAT_NAME.(OR(FIND PROC_CAT_NAME IN TBLTEMP_BUDGACTUALDETAILS)).PROC_CAT_NAME. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LOCATIONNM EQ &LOCATIONNM.(OR(FIND LOCATIONNM IN TBLTEMP_BUDGACTUALDETAILS)).LOCATIONNM. ) AND ( TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.HOSPITALNM EQ &HOSPITALNM.(OR(FIND HOSPITALNM IN TBLTEMP_BUDGACTUALDETAILS)).HOSPITALNM. );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,,,).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
INCLUDE = IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/en/combine_templates/ENblue_theme.sty,
$
TYPE=REPORT,
COLUMN=N0,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.LASTOFREPORTING_NAME,
WRAP=6.000000,
$
ENDSTYLE
END

I guess I will start over.

7702

I wish I could do a print screen to show you....the code is there but the whole design is gone....empty pallet!

Your are missing a verb and object - all that's left is your WHERE statement. You need to add this back in:

SUM
&RP_FN01S_Sum11.(AND(,,,)).Please select sum field(s).
BY LOWEST &RP_FN01B_Sort1.(BY(,,,,,,,,,,,,)).Please select sort field(s).
ACROSS LOWEST &RP_FN01A_SortAcross1.(ACROSS(,,)).Please select across field(s).

ON TBLTEMP_BUDGACTUALDETAILS.TBLTEMP_BUDGACTUALDETAILS.ORIGFSCNM SUBTOTAL AS '*TOTAL'
HEADING
"&TITLE"

Your SUBTOTAL field has to be the same as your BY field. And I would move the SUBTOTAL down below the WHERE statement personally.

Finally - if this is your first AD-HOC report I'd say it is much too complicated. The fieldnames you have here would drive me nuts for starters - way too long to make any sense of the code. And your WHERE statement could be broken into several separate ones for clarity also. And having users select sort fields on your very first report ... you need to walk before you run..

And don't forget you can insert blank lines in the code anywhere for clarity. The ReportPainter hates them and will take them out again, but as I said, I only use it for styling, so it doesn't matter.

As for books on coding, see if you can find something for mainframe FOCUS. The language is substantially the same. (I use my old PC FOCUS manuals).

This message has been edited. Last edited by: George Patton,

I have created some master files, some ad hoc reports ect etc...enough to get myself in trouble...hahaha! Eventually things fall apart because the GUI interface is sort of crappy...it appears you really need to be working at the code level. The guided adhoc was easy to put together. A few amperstands an it build the basics. Formatting and totaling is where the GUI again falls apart. My biggest issue is I have learned certain ways of doing things in the GUI...that now fall apart in the GAH....ala most of my code disappearing for some reason!

I appreciate your help, I'll look for the book too. If I could get a free week at work to do nothing but focus on focus...that would help too. I hope they are trying to improve the GUI though..coding is nice but point and click is nicer and opens creation and maintenance to a wider audience.

I still haven't pinned down why my code dissappears sometimes, but I have a strong suspicion (at least in InfoAssist). It seems that when WebFocus parses the code and finds an error, it gives an Unexpected Behavior Notification popup with a message. Then I click OK and a second popup appears asking if I want to Save Changes. If I click YES, it saves truncated code that is missing everything after the error. I have taken to saving "solid" versions more often before making furher changes and replying "No" to the Save Changes popup after a Behavior popup.

Do two things to make your code more readable. This will help you in the future.

1. Turn off fully qualified field names in Dev Studio. IBI should change this to be off by default whenever Dev Studio gets installed.
In the File Menu of Dev Studio go to Window -> Options -> Reporting.



2. Break up your Big Super WHERE into individual WHEREs. The filtering will be the same because you are using AND logic.