Hi Friends,

Anyone please help me.

I have an already existing excel report that goes to the accounting team on first business day of every month.

The excel has various tabs and each tab holds data for each year. Eg: 1st tab holds data for the Financial year 2010, 2nd holds 2011's data, 3rd holds 2012's data and 4th tab holds 2013's data.

Now, considering 4th tab, which holds 2013's data, its has fields with values till the month of June. In the month of August,the procedure should be run for the prior month ie., July and whatever would be the values for the below mentioned fields, should be appended to the values below as 7th row. And the other year tabs should still exist with modification done only to the FY 2013 tab.

Month Actual Estimated Adjusted Customer
01 1596 131 288 7330
02 1296 117 252 6483
03 1425 123 295 5712
04 1542 145 315 8594
05 1200 883 262 5441
06 1372 100 302 6124

Since, the report pulls out 4 years data, the report is getting timed out. Is there any way that I can pre-run the data for the other year tabs and run only the current month's data?

I am not seriously aware of how to go about such huge data.

Can anybody help me out with this?

Thanks
Teju
WebFOCUS 7.7.03
IDMS

Sound like you need a collection table of the summary data, that can be added to for the new stuff.

If I understand your requirements, I would suggest creating FOCUS DB and add the new stuff to it with MODIFY

Alternatively perform the extracts for all years up until the current year and save the output as an EXL2K output. Then add a few more worksheets to the output and save as an EXCEL 2003 MHT file (single page web archive).

Then use this as an Excel template, pushing your current year output to the correct SHEETNUMBER.

T

You can create a summary/aggregate table at DB which refresh data monthly once. Do reporting against this table.

Thanks,
Ram

OMG Waz ... They don't even teach MODIFY any more. You and I, of course, use it all the time (just yesterday in fact ...)

Presumably this spreadsheet doesn't hold every record for 4 years worth of data - there would be little point in that. So it's a summary.

So you can run the previous years report one time and then HOLD FORMAT FOCUS and specify your application directory. That way you'll preserve both the data and the master file.

Then create the report for the current year and HOLD that.

Then JOIN the current year with the FOCUS file (previous years) and create the spreadsheet.

Sheesh ... the same solution 3 times ..

Along the lines of what George said - we had a similar requirement. Our process was as follows:

1) We created an application directory to store our "summary" data files e.g. DATA_DIR
2) We ran a request each month, to create a data file per month (to be safe, we always ran the current month, and the previous month)
3) Then we dynamically "concatenated" the data files when we needed to read all the summary data

For step 2 above, you would append the CCYYMM to the file name when you hold it e.g.

  
ON TABLE HOLD AS DATA_DIR/SUMMDATA_&THIS_MONTH FORMAT ALPHA
or
ON TABLE HOLD AS DATA_DIR/SUMMDATA_&THIS_MONTH FORMAT FOCUS

The variable &THIS_MONTH would obviously be created in Dialogue Manager e.g.
-SET &THIS_MONTH = EDIT(&YYMD,'999999') ;

For step 3 above - contrary to what George said - you wouldn't "JOIN" the data files, but rather "concatenate" them.
How you do this depends on whether your output format is ALPHA or FOCUS.
For ALPHA, you would need one master file that has the same format as all the summary data files.
e.g. create a copy of your current month's master file with APP COPYFILE

 
APP COPYFILE DATA_DIR SUMMDATA_&THIS_MONTH MASTER DATA_DIR SUMMDATA_INPUT MASTER

Then issue a FILEDEF (or use TABLE FILE/MORE to splice the files together)
I'm just coding in Internet Explorer here - but I think the syntax looks something like this:

 
FILEDEF SUMMDATA_INPUT DISK DATA_DIR/SUMMDATA_*.FTM

For FOCUS format, you would use the USE command to concatenate the data (there are various ways to dynamically build the list) e.g.

 
USE
DATA_DIR/SUMMDATA_201307.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201306.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201305.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201304.foc AS SUMMDATA_INPUT

After all that:
TABLE FILE SUMMDATA_INPUT
PRINT *
END

Something along those lines!

Hopefully that gives you a few ideas to try.

Alternatively you could FILEDEF ... APPEND to an app folder, then HOLD it.

Twanette, you have some really neat tricks!

quote:

USE
DATA_DIR/SUMMDATA_201307.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201306.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201305.foc AS SUMMDATA_INPUT
DATA_DIR/SUMMDATA_201304.foc AS SUMMDATA_INPUT



After all that:
TABLE FILE SUMMDATA_INPUT
PRINT *
END

I never would have thought to USE multiple real files as a single source like that. Very cool, and much better than joining.

Yes very cool, and been around for many years too.

It is a great way to manage large amounts of data, especially if you don't need all of it.