[SOLV] Use Report Parameters to help calculate 2 sets of totals within same procedure

Jeff,

Can you start by giving us a sample of your code?

from first glance it seems that you are looking for a simple prompt that would look like this

  WHERE (end_date FROM &STARTDAT TO &ENDDATE) 
OR (start_date FROM &STARTDAT TO &ENDDATE)

That will give you the prompts your looking for.

Then to do your counts you would want to use a define field where it creates a counter whenever an end date is in the date range and another field when the start date in the date range.

Eric

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

Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7

Posts: 95 | Registered: July 31, 2007

Ignored post by EWoerle posted

BlueZone

Platinum Member

posted

Jeff - Here are the steps you can try -

1) Put out the totals lines to two different Hold files in two different TABLE FILE steps

2) Append the 2 files back in the order you need them to be. (If you also need some data-details lines on the top, then it will make it 3 files - The details Hold-file, The totals-line-1-hold-file & totals-line-2--hold-file.

3) TABLE FILE PRINT the consolidated file.

The little trick here is to make the totals-line hold-files be of the same exact record layout as the details file. You could do that by having some dummy compute fields to fill in the space as needed.

Hope that makes sense.
Sandeep Mamidenna

-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !!

Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006

Ignored post by BlueZone posted

Gold member

posted

Eric,
This was my first thought on how to do this but is it possible to use the report prompt variables in a define statement. I've set up the report prompts but when I go to set up the define(s) the report prompt variables i.e. startdate & enddate do not show up in the available fields for use in the define expression

7.7.02 Windows7
HTML/Excel/PDF

Posts: 66 | Registered: April 16, 2008

Ignored post by Jeff_Rowland posted

Tony A

Expert

posted

Jeff,

Too much eggnog?

Using straight code via edit

DEFINE FILE yourfile
-* Using boolean
CLASS_BEG/I9 = start_date FROM &STARTDAT TO &ENDDATE;
CLASS_END/I9 = end_date FROM &STARTDAT TO &ENDDATE;
END

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, 2004

Ignored post by Tony A posted

PBrightwell

Master

posted

If all you are want are total counts try:

TABLE FILE yourfile
SUM
COMPUTE TOT_STARTS/I5=IF START_DATE FROM &START TO &END THEN 1 ELSE 0;
COMPUTE TOT_END/I5=IF END_DATE FROM &START TO &END THEN 1 ELSE 0;
END

IF you are also wanting to print the list of classes try:

TABLE FILE yourfile
SUM
COMPUTE TOT_STARTS/I5=IF START_DATE FROM &START TO &END THEN 1 ELSE 0; NOPRINT
COMPUTE TOT_END/I5=IF END_DATE FROM &START TO &END THEN 1 ELSE 0; NOPRINT
PRINT
CLASS_NAME START_DATE END_DATE
WHERE START_DATE FROM &START TO &END
OR END_DATE FROM &START TO &END

ON TABLE SUBFOOT
"Totals END

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

j.gross

Virtuoso

posted

quote:
TABLE FILE yourfile
SUM
COMPUTE TOT_STARTS/I5=IF START_DATE FROM &START TO &END THEN 1 ELSE 0;
COMPUTE TOT_END/I5=IF END_DATE FROM &START TO &END THEN 1 ELSE 0;
END

You need to use DEFINE (rather than COMPUTE) to get the count of matching records.

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Gold member

posted

Maybe I'm trying to make it harder than it seems. When trying to set up a define like was mentioned i.e. can't seem to make it accept the &STARTDAT, &ENDATE parameters in the expression.
Get message saying no value found for &STARTDAT or &ENDDATE

DEFINE FILE REG
CLASS_BEG/I9 = start_date FROM &STARTDAT TO &ENDDATE;
CLASS_END/I9 = end_date FROM &STARTDAT TO &ENDDATE;
END

7.7.02 Windows7
HTML/Excel/PDF

Posts: 66 | Registered: April 16, 2008

Ignored post by Jeff_Rowland posted

EWoerle

Gold member

posted

Do you have Prompt for Parameters turned off on the report?

Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7

Posts: 95 | Registered: July 31, 2007

Ignored post by EWoerle posted

Gold member

posted

Not that I'm aware of. I set up a basic report using Developer Studio just to print a list of classes where the class start date falls during the effective period. I clicked on the WHERE/IF button to set up the parameters to prompt for the start_date and end_date of the effective period. I can run this report and I'm prompted to enter a start and end date and the report prints out the appropriate classes. When I go to set up the define this is where I'm encountering the problem. The DEFINE wizard does not show the parameters &start_date and &end_date as being available in the list of fields.

Hope I'm not confusing you more that probably already have.

7.7.02 Windows7
HTML/Excel/PDF

Posts: 66 | Registered: April 16, 2008

Ignored post by Jeff_Rowland posted

Darin Lee

Virtuoso

posted

Your parameters are &STARTDAT and &ENDDATE, not &start_date and &end_date. I know that's probably stating the obvious, but based on your information and the code in your last post, that's what there is to go on. Parameters that have been created using -SET or -DEFAULT (or -PROMPT) must match exactly (even in Case) to the ones that are referenced in your DEFINE or TABLE sections. If they are already matching EXACTLY, try setting up a default value using -DEFAULT at the beginning of your fex and see if it runs.

As Eric, mentions, it sounds like the "Prompt for parameters" is turned off or it would be asking you for any parameters that are unresolved. Right click your procedure, click properties, and make sure that the Prompt for parameters box is checked.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Gold member

posted

Thanks for all the input. Due to other work commitments I have to temporary suspend working on this procedure. I hope to readdress in the coming weeks.

7.7.02 Windows7
HTML/Excel/PDF

Posts: 66 | Registered: April 16, 2008

Ignored post by Jeff_Rowland posted

Gold member

posted

Ok I had to take one more look at everyones replies and what I had done and I think I finially solved the problem I was having.

My origional define statement:
DEFINE FILE reg
CLASS_BEG/I9 = start_date FROM &STARTDAT TO &ENDDATE;
CLASS_END/I9 = end_date FROM &STARTDAT TO &ENDDATE;
END

what I discovered was that I needed to put single quotes ' around the parameter variables (to designate parameters to be treated as dates? )Once I did this everything seemed to fall in place.

DEFINE FILE reg
CLASS_BEG/I9 = start_date FROM '&STARTDAT' TO '&ENDDATE';
CLASS_END/I9 = end_date FROM '&STARTDAT' TO '&ENDDATE';
END

7.7.02 Windows7
HTML/Excel/PDF

Posts: 66 | Registered: April 16, 2008

Ignored post by Jeff_Rowland posted

Gold member

posted