We have a job scheduled monthly and weekly. Right now we are updating the parameters manually. Is there any way we can do that through some jobs? Please let me know your suggestions.

Thanks

yup
here are the botsched files (in their loc on myserver anyway)
USE
D:\IBI\SRV52\WFS\CATALOG\BOTSCHED.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTLOG.FOC

D:\IBI\SRV52\WFS\CATALOG\BOTLOG2.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTADDR.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTDEST.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTGUSER.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTJOURN.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTPACK.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTPARMS.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTTASK.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTTELL.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTTSKEX.FOC
D:\IBI\SRV52\WFS\CATALOG\BOTUPROF.FOC
END
Probably the BOTPARMS would be a good place to start, they're just foc files, so you can update them with a MODIFY. Might work even if Caster is running...might not. Might want to back them up before tinkering.

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

Hi Susannah,
Checked that location and didn't find those files you mentioned. I'm thinking of doing this by Updating BKREPOS table using a fex procedure and schedule that procedure in report caster to run every month. I'm not sure whether this is a feasible solution.

It depends what database you are using for your repository. You will only find Susannah's files if you are using a FOCUS repository.
If you are using ORACLE, for example, they would be in an ORACLE database somewhere. If you look in your Report Caster configuration settings, you can see where the repository is.

touche, N.Selph.

We are using Sqlserver database and I find those tables under BKREPOS datasource. I'm writing my fex procedure against those datasource to update the parameters.

Not able to Update more than one record at execution. Having the update statement inside the Repeat statement to update each row based on the new value. But it's not working, webfocus accepts only one update statement for execution.

Anybody know how to get this work,

Hi Kamesh,

The following piece of code creates a table in MS SQL Server and then populates it using a repeat loop. Not exactly what you want but it will give you some ideas. This code places all the dates for year 2001 through 2008 into a table and then adds other date components such as year, quarter, month, week number (non ISO 8601), day of week and day name. The rest of the code (not given here) also adds the ISO date components.

Note that you have to execute the SQLOUT table request each time to perform the SQL request and this is probably where you are having problems? Note also that the final INSERT statements are placed into a file which is then sandwiched between the SQL and the closing semi colon to make it a single SQL request.

ENGINE SQLMSS SET SERVER adaptername
SET SQLENGINE = SQLMSS

APP FI datefile DISK datefile.txt
-RUN

SQL SET DATEFIRST 1

SQL DROP TABLE Datefile;
TABLE FILE SQLOUT
PRINT *
END
-RUN

SQL CREATE TABLE Datefile(
     "date" DATETIME NOT NULL UNIQUE
   , "dowk" SMALLINT
   , "day" CHAR(12)
   , "year" SMALLINT
   , "quarter" SMALLINT
   , "week" SMALLINT
   , "iso_year" SMALLINT
   , "iso_quarter" SMALLINT
   , "iso_week" SMALLINT
);
TABLE FILE SQLOUT
PRINT *
END
-RUN

-SET &ECHO=OFF;
-SET &Year = 2001;
-REPEAT :Updates WHILE &Year LE 2008;
-SET &Date = '&Year.EVAL0101';
-REPEAT :Loop WHILE &Date LE '&Year.EVAL1231';
-SET &SQLDate = EDIT(&Date,'9999-99-99');
-WRITE datefile   INSERT INTO Datefile VALUES('&SQLDate', 0, '', 0, 0, 0, 0, 0, 0)
-SET &Date = AYMD(&Date, 1, 'I8YYMD');
-:Loop

SQL
-INCLUDE datefile
;
TABLE FILE SQLOUT
PRINT *
END
-RUN

SQL
  UPDATE Datefile
     SET dowk = DATEPART(dw, date)
       , day  = DATENAME(dw, date)
       , year = DATEPART(yy, date)
       , quarter = DATEPART(qq, date)
       , week = DATEPART(wk, date)
   WHERE DATEPART(yy, date) = &Year
;
TABLE FILE SQLOUT
PRINT *
END
-RUN

-* more code here

-SET &Year = &Year + 1;
-:Updates

Enjoy

T

This message has been edited. Last edited by: Tony A,

That worked Tony. Thanks

Kamesh,

As I understand your problem, you have a report that filters it's input data to a single (or range) of month(s) or week(s) and want that filtration constant to advance by one week or month as time passes.

Instead of playing with altering the tables that comprise RCaster's scheduling database, or writing an external file of dates with a comples of SQL commands, have you considered using Dialogue Manager commands to obtain and parse the system's current date (&TODAY, etc.) and manipulating the parsed result into the date you need?

I do this routinely. It's relatively simple to calculate the month# of 'last month' or 'this month', but the determination of 'today's week#' is a bit more complex and requires use of FOCUS' date functions.

In either case, when determining the previous Month# or Week# you must accommodate the year-to-year situation wherein this year's week/month is #1 and subtracting gives week/month #0.

My external control parameter technique is to provide a date parameter that can take either a specific user supplied date or the word "AUTO". The program takes and uses a given date exactly as given, but computes the date when "AUTO" is used. This way, the program computes its date(s) from today's date when run under RCaster, but uses whatever date the user wants when run under MRE or SelfService (for re-run or special alalysis).

If you're interested in the code I use, I can post it to this thread.

Chris Burtt; Westcon Group, NA; Tarrytown, NY; USA.

Hi Chris,
We have a couple of reports and every month we are changing some of the parameters in those. The parameters are in the formats 200506 and 200606 OR 052005 to 052006, ....

Since I have different kind of parameter values, thought of updating it in table using TaskId.

Please post your code and I will take a look on it and compare with my current req.

Thanks

Kamesh,

Sorry for the delay, but here's the code I've used to compute the ID of a prior time period based on the system's date. Doing it on months is simple because the system's date (&TODAY) contains a MONTH_NUMBER and can be extracted easily by Dialogue Manager alphabetic methods, but WEEK_NUMBER is harder because you have to use a function that works only on the (binary?) formatted date-time-group system date.

-* COLUMN "CREATEDATE" RECORDS IN 'DATE-TIME-GROUP' FORMAT THE DATE AND TIME
-* THE ROW WAS CREATED.

-* SELECT ROWS FROM THE TABLE THAT WERE CREATED EITHER "LAST WEEK" OR "LAST MONTH"
-* AS REQUESTED BY THE USER THROUGH THE PROMPT-PARAMETER "&Period".

-* NOTE:
-* THE DETERMINATION OF A ROW'S CREATION WEEK/MONTH MUST BE DONE IN 'DEFINED'
-* VIRTUAL TABLE COLUMNS BECAUSE THE CREATION DATE VARIES FOR EACH ROW.
-* HOWEVER, THESE FUNCTIONS ONLY OPERATE ON NON-CHARACTER DATE-TIME-GROUP FORMAT
-* VARIABLES, SO THE DETERMINATION OF THE CURRENT SYSTEM DATE'S WEEK/MONTH NUMBER
-* CANNOT BE DONE WITH CHARACTER-BASEED DIALOGUE MANAGER PROCESSES AT THE
-* BEGINNING OF THE *.FEX; BUT MUST BE DONE, ALSO, IN "DEFINED" COLUMNS AND
-* THEREFORE IS (INEFFICIENTLY) REPEATED IN EACH NEW ROW.
-* [IF FILTERING IS REQUIRED ONLY ON 'MONTH', THEN DIALOGUE MANAGER CAN BE USED TO
-* CONVERT THE SYSTEM DATE (&TODAY) INTO MONTH# AND MAY BE DONE ONCE AT THE START.]

-* [I KNOW THE USE OF "#" IN VARIABLES IS FROWNED UPON, BUT IT DOES WORK AND CLEARLY
-* IDENTIFIES "DEFINED" COLUMNS WHEN THEIR NAMES ARE INTERMINGLED WITH THOSE OF "REAL"
-* COLUMNS, AND THUS HELPS THE MAINTENANCE PROGRAMMER FIND "WHERE DOES THIS COLUMN
-* COME FROM?".]


-* -----------------------------------------------------------------------------

-* Session ("&") Parameters:
-* &Period: For which past period does the report include data?
-* 'month' - report includes data for last Month.
-* 'week' - report includes data for last Week (Sat-Fri).


-* Assign defaults to external control parameters.
-DEFAULT &Period='month';

-* Prompt for control parameters in this sequence:
-SET &X = '' || ' ... ' ;
-SET &X = '' || '&Period.(month,week).';
-SET &X = '' || ' ... ' ;



DEFINE FILE

-* Convert SysDate to DateTimeString and extract current Week#/Month# ...
#TODAY_DTS/H8 = HINPUT(8, '&YYMD', 8, 'H8');
#TODAY_PD_I/I2 = HPART(#TODAY_DTS, '&Period', 'I2');
-* ... then compute # of previous period (week/month) & adjust year roll-over.
#LAST_PD_I/I2 = IF (#TODAY_PD_I EQ 1) AND ('&Period' EQ 'month') THEN 12
ELSE IF (#TODAY_PD_I EQ 1) AND ('&Period' EQ 'week') THEN 52
ELSE #TODAY_PD_I - 1;

-* Format data's creation date in alpha for printing ...
#CREATE_DT_A/MDYY = HDATE(CREATEDATE, 'MDYY');
-* ... and in 2-digit numeric Week#/Month# for row filtering .
#CREATE_PD_I/I2 = HPART(CREATEDATE, '&Period', 'I2');

END
-* --------------------- End Define


TABLE FILE &FILENAME

PRINT
...
#CREATE_DT_A
...
-* Debug printing:
#CREATE_PD_I
#LAST_PD_I

WHERE #CREATE_PD_I EQ #LAST_PD_I

END

WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.

Thanks for your code. I will try this and let you know.