RE: [sqr-users] Dynamic Programming and Output Generation

["Alexander, Steve" <Steve.Alexander@sanjoseca.gov>]

Matt,

A lot of the body of many SQR commands has to be literals.  The variable
that is to contain the values you input has to be a variable, not a variable
containing the name of a variable.  That still leaves you some options - I
can think of three.

First possibility: write a program to read the database table and construct
an SQR command line like this:

sqr myprog.sqr [connectivity] [flags] $first_parm 5 "Enter zipcode" char

Then execute that command line.  Within the program (myprog), use the ASK
command to read the command line.

Program:

begin-setup
  ask variable
  ask length
  ask prompt
  ask type
end-setup

begin-program
  input {variable} maxlen={length} '{prompt}' type={type}
...
end-program


Drawbacks: Your program has to be hardcoded for the number of parameters,
with possibly a lot of ask commands (variable1, variable2, variable3).  If
the command line gets too long, there is a provision to use a text file
instead.

Second possibility: write a program to read the data table and construct an
SQR include file that contains the input commands.  Then compile and execute
the main program, which contains an include statement like so:

begin-program
#include 'input_statements.sqc'
...
end-program

Drawbacks: If you're running more than one program at once, you need a
different include file name for each.  If you're running more than one copy
of the same program at once, you may need to differentiate the include file
names and pass the different name through the command line / ask statement
technique.  Even if every copy of the program has the same input statements,
you don't want to be deleting/rewriting an include file when the SQR
compiler is looking for it.

Third possibility: Instead of put the input statement parameters in a
database table, put the input values in a database table.

Now that I given you the techniques, I'm wondering why you want to do this.
The input parameters to a program don't change that often, so why not
hardcode them in that program?  It's easier to maintain a program when
everything is in one place, rather than hiding information about the
parameters in a database.

If you expect to change the inputs frequently, you'll have to change the
programs anyway.  If many of your programs use the same parameters, use
include files.  If the parameters you use vary widely from program to
program, what use is this change?  Since you have so many programs, and this
approach forces you to change them all, is it worth it.

-----Original Message-----
From: Steve Cavill [mailto:steve.cavill@infoclarity.com.au]
Sent: Wednesday, October 01, 2003 11:08 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Dynamic Programming and Output Generation


Hi Matt,
Rajib's method for dynamically selecting run time parms is fairly common -
don't use input command at all, rather store the parms in a table, and
retrieve them with select.

Regarding the email, you can email the output to a distribution list, would
that help?  Another method is to subscribe multiple users to the job output,
then Portal will send multiple emails, one for each subscriber.  You can use
the Portal API to maintain the subscription list.  I know the above works in
V7, I'm pretty sure that will work in V6 also.

Cheers, Steve.

-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Sengupta, Rajib (CAG-CC-Contractor)
Sent: Thursday, 2 October 2003 6:06 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Dynamic Programming and Output Generation


I am not sure exactly what you want here , but I guess we do almost the same
thing in our Peoplesoft environment while creating report from online.
We have hundreds of reports which have different parameters , also depending
on the value entered or not entered on the panel the sqr query become
dynamic -
It happens this way :
When a user enters some value on online and run the report the values are
stored in a DBMS table .Then at start of the SQR we do a select on the table
as you have mentioned below .

There are two parts in the whole thing:
A. store the data/values on the table. In our peoplesoft system we use the
usual peopletools and peoplecode to save and edit data entered online.

B. then run the SQR report in the server


Your SQR structure will be sumthing like this :

BEGIN-REPORT
  DO INIT-REPORT
  DO GET-AGING-CATEGORY
  DO MAIN-PROCESS            -- here is the actual report
  DO GRAND-TOTAL
  DO END-PROCESS
END-REPORT

in the init-report you can query the above table to get your data :

BEGIN-PROCEDURE INIT-REPORT
  DO INIT-DATETIME
  .....
  DO SELECT-PARAMETERS     -- this will have your select statement as below


  ...........

END-PROCEDURE !INIT-REPORT

you will have your data in the $variables -
now depending on the value in the $variables you will create your dynamic
where clause of the sql
which you can use in your main-process SELECT.

============================

about email it depends on which system you are -
if you are in unix box- you can use the unix email shell commands to send
your sqr outputs-  .lis or  .spf files to multiple users.
I guess there should be similar batch commands for windows.
for mainframe(MVS) environment you can use MQseries - lotus notes to send
multiple emails. Infact there is some API by which lotus notes can send
emails to microsoft exchange. so you create your file in MVS (OS/390) -
write it to MQ- lotus notes will pick it up from MQ and will send it to
exchange server.

But for all these you have to have a good understanding of each of the
technology mentioned.

Thanks,
Rajib

Rajib Sengupta
Systems Analyst and Team Lead
Peoplesoft Financial Consultant
Conagra Business Systems
Phone: 402-595-5749




-----Original Message-----
From: Matt Rogish [mailto:rogishmn@muohio.edu]
Sent: Wednesday, October 01, 2003 2:16 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Dynamic Programming and Output Generation


Hi all,

We're using SQR 4.3.4 (on AIX??) to write many, many SQR reports.  The
problem we've found is that over the years (and hundreds of programs later)
we've got a mix of different styles etc. and it's becoming increasingly
difficult.  Oftentimes we have reports that take the same input parameters
but, of course, they are written differently so that some have bounds
checking and others don't etc.

What I'd like to do is store the input parameters in a DBMS table and then
run it at the beginning of every report to build the input list, e.g.
something like this:
(pseudocode)
begin-select
name            &name
prompt  &prompt
length          &len
type            &type

   !this way
   input &name MAXLEN=&len &text TYPE=&type

   !or with an array to be gone over later
   Put &name &prompt &len &type Into paramArray( #something )

  FROM params
WHERE program_name = '{program_name}'
end-select

But I can't get SQR to accept any sort of input like that.

Secondly, we use BRIO Portal 6 (whatever is one or two version(s) behind
the current one) which doesn't support emailing the output from a job (SQR
Program) to more than one person.  We're not entirely sure the latest
version of BRIO.Portal supports this, so if it doesn't we'd like a way to
be able to email output to more than one person using SQR.

I was thinking storing the distribution lists in another DBMS table and
then using the Unix mail command to mail it to someone else.  I see from
this old thread (http://www.sqrug.org/sqr-users/sqrusers_97/msg00150.html)
that I can close the output of the current program, but how do I find out
the file name of the output file (usually a .pdf via -printer:pd) so that I
can use it as an attachment.  Anyone tried something like this and have a
better way?  Or is Brio Portal 7 (the version we're looking at upgrading
to) more advanced than the version we're using now?


Thanks,

--
Matt Rogish - rogishmn@muohio.edu
Programmer
Advancement Services
Miami University
513.529.1420

Do not meddle in the affairs of SQR,
for it is subtle and quick to anger!

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users