Can I have a report based on variables or table in memory only?

[irwincohen]

I have a process which extracts data from several tables and fills in a new table. The number of records changes with each run. A report is based on this new table. This table is only needed for the report and is zapped before each use. Is has no other use. The most time consuming part of the process is writing the new table (not getting the data). I would like to avoid the disk activity in filling in the new table. I have two ideas but do not know how to do either.
1) Create the table in memory with out disk writes. Can I create a virtual table which only exists in memory? I do not want to use a ram disk.
2) Base the report on dot variables instead of a table. It is easy to get all the data I need for the report into dot variables and use those in a report. The dot variable will be in an array with a different size each run (like the number of records in the table). But the report has a detail section and groups based on one of the elements in the dot variables. How can I use a detail section and groups in a report that uses variables to hold the data?
You suggestions and any other ideas are welcome.
Thanks
Irwin

[Al Buchholz]

Re: Can I have a report based on variables or table in memory only?

The most time consuming part of the process is writing the new table (not getting the data).

How much data and how much time does the writing take?

If the data is cleansed coming in, then there shouldn't be a need to field rules - which might cause issues.

What does the code look like that writes the table?

Operation, action scripting, or xbasic "use tbl.batch_begin() and tbl.batch_end() " - requires exclusive access but is much faster....

Are there indices on the target table?

[Tom Cone Jr]

Re: Can I have a report based on variables or table in memory only?

1) Create the table in memory with out disk writes. Can I create a virtual table which only exists in memory? I do not want to use a ram disk.
2) Base the report on dot variables instead of a table. It is easy to get all the data I need for the report into dot variables and use those in a report. The dot variable will be in an array with a different size each run (like the number of records in the table). But the report has a detail section and groups based on one of the elements in the dot variables. How can I use a detail section and groups in a report that uses variables to hold the data?

1) No. You can't create a virtual table that the report engine could use to print or preview a report.

2) No. Your report layouts must be based on tables or sets of tables, and the report engine features that create the different sectioins of the report, and group the records, are black boxes to us. We can't change how they work at runtime. Xbasic includes a fairly robust set of "file" statements that could be used to create a text file that resembles a report, but you'd be responsible for scripting how each line of the text file is formed. I suppose the same sort of line by line construction could be written to a memo field, which is then the only object in a report layout. Haven't tried that, but you're looking at a huge amount of work, roughly equivalent to building your own report engine.

[irwincohen]

Re: Can I have a report based on variables or table in memory only?

Hi Al and thanks for your reply. Your idea of tbl.batch_begin() and tbl.batch_end() worked quite well. The routine updates one field in each record in the table in least 100 records and as much as 500 records depending on the user. With out the batch_begin and end it takes about 12 seconds for 400 updates and with the batch_begin and end it only takes 3 seconds. So the improvement is really worth it. The need for exclusive access is not a problem so I will install your suggestion. FYI I am testing on a Pentium 4 at 2.6 gHz under windows 7. There are no indices on the table.

I am still curious about how to create a report using a detail section and groups based on the data in variables and not in the associated table. Then I would not have to write the table at all but could go directly from the data to the report. The table would be empty and all the data in the variables. But with your suggestion this is not urgent, it just would be fun to do.
Thanks for you help
This forum is great.
Irwin

Here is the relevant code

Table was zapped and is now open.
3 Arrays are filled.
Arrsize is between 100 and 500 depending on user.

trace.WriteLn("start "+now()) 'for testing
tbf.batch_begin() 'this saves time on the table writes

tbf.enter_begin() 'tbf was zapped so enter first record
tbf.Thedate = arrThedate[1]
tbf.Dow = arrDow[1]
tbf.Hour = arrHour[1]
tbf.enter_end()
arrpos = 1
while arrpos <= arrsize 'loops between 100 and 500 times
tbf.fetch_first() 'must find first - just made it so no test, now scan for the date, hour
while .NOT.tbf.fetch_eof() .and. (tbf.Thedate<>arrThedate[arrpos] .or. tbf.Hour<>arrHour[arrpos])
tbf.fetch_next()
end while 'now either eof or date and hour were found
if tbf.fetch_eof() 'eof = not found so must make one
tbf.enter_begin()
tbf.Thedate = arrThedate[arrpos]
tbf.Dow = arrDow[arrpos]
tbf.Hour = arrHour[arrpos]
tbf.enter_end()
end if 'now pointed at the right tbf either we found it or made it
tbf.change_begin()
SELECT
CASE arrRoom[arrpos] = "201"
tbf.R201 = arrLoginid[arrpos]
CASE arrRoom[arrpos] = "202"
tbf.R202 = arrLoginid[arrpos]
CASE arrRoom[arrpos] = "203"
tbf.R203 = arrLoginid[arrpos]
'another 10 case statements
CASE else
end SELECT
tbf.change_end()
arrpos = arrpos + 1
end while
tbf.batch_end()
trace.WriteLn("end "+now()) 'for testing

'close the table

[irwincohen]

Re: Can I have a report based on variables or table in memory only?

Hi Tom, Thanks for your answer. You are right. Creating a report generator is not for me. So using these temporary tables to hold the data for the report seems the only way to go. Thanks to Al's suggestion the time required is now tolerable.
Thanks,
Irwin

[Tom Cone Jr]

Re: Can I have a report based on variables or table in memory only?

Irwin,

As you've discovered, when you post xbasic code here the message board left justifies each line. Makes it very difficult for readers to parse. Please preserve your indents by using the "Go Advanced" button to open a better editor. Once there, you can select your code and then click pound sign (#) tool to surround the selected code statements with CODE tags. Thanks.

[Al Buchholz]

Re: Can I have a report based on variables or table in memory only?

FYI I am testing on a Pentium 4 at 2.6 gHz under windows 7.

Wow I didn't think win 7 would work in that old of a processor.....

My guess from experience I have with win 7 on an I5 processor with 4 gb is this volume would run in the blink (or 2) of an eye... The I7 I have is ultra fast... and well worth the money..

Never send a boy to do a man's job... ( ie a big computer solves many problems...)

[Tom Cone Jr]

Re: Can I have a report based on variables or table in memory only?

end while	 'now either eof or date and hour were found

If your table had an index you could avoid stepping through all the records with this while ... end while loop. Just do an indexed find by key. If the table is large this will save considerable time.

[irwincohen]

Re: Can I have a report based on variables or table in memory only?

As per Toms suggestion here is the code again. I hope this is better
Thanks Tom
Irwin

Here is the table structure
FLATAPP4DESK->Table Structure
------------------------------
THEDATE D 8 0
DOW C 9 0
HOUR C 4 0
R201 C 6 0
R202 C 6 0
R203 C 6 0
R204 C 6 0
R205 C 6 0
R206 C 6 0
R207 C 6 0
R208 C 6 0
R209 C 6 0
R210 C 6 0
R211 C 6 0
R212 C 6 0
R213 C 6 0
R214 C 6 0
R215 C 6 0
-----------------------------------------------

Table was zapped and is now open.
4 Arrays are filled.
Arrsize is between 100 and 500 depending on user.

trace.WriteLn("start "+now())
tbf.batch_begin()

tbf.enter_begin()			'tbf was zapped so enter first record 
	tbf.Thedate = arrThedate[1]
	tbf.Dow = arrDow[1]
	tbf.Hour = arrHour[1]
tbf.enter_end()
arrpos = 1
while arrpos <= arrsize	'loops between 100 and 500 times
	tbf.fetch_first()	'must find first - just made it so no test, now 											scan for the date, hour
	while .NOT.tbf.fetch_eof() .and. (tbf.Thedate<>arrThedate[arrpos] 									.or. tbf.Hour<>arrHour[arrpos])
		tbf.fetch_next()
	end while		'now either eof or date and hour were found
	if tbf.fetch_eof()			 'eof = not found so must make one
		tbf.enter_begin()
			tbf.Thedate = arrThedate[arrpos]
			tbf.Dow = arrDow[arrpos]
			tbf.Hour = arrHour[arrpos]
		tbf.enter_end()
	end if	'now pointed at the right tbf either we found it or made it
	tbf.change_begin()
	SELECT
		CASE arrRoom[arrpos] = "201"
			tbf.R201 = arrLoginid[arrpos]
		CASE arrRoom[arrpos] = "202"
			tbf.R202 = arrLoginid[arrpos]
		CASE arrRoom[arrpos] = "203"
			tbf.R203 = arrLoginid[arrpos]
		'another 10 case statements
		CASE else
	end SELECT
	tbf.change_end()
	arrpos = arrpos + 1	
end while	
tbf.batch_end()
trace.WriteLn("end "+now())

'close the table

[MoGrace]

Re: Can I have a report based on variables or table in memory only?

If you don't care much about how your report may look, you could always write the variables to a text file and print that...

[Tom Cone Jr]

Re: Can I have a report based on variables or table in memory only?

Hey, Robin,

Thanks for filling in some details omitted in my similar suggestion at post #3, above.

tom

[MoGrace]

Re: Can I have a report based on variables or table in memory only?

Sorry Tom - I was doing a bit of selective reading

I feel like I have been in here all day looking for answers...

[Peter.Greulich]

Re: Can I have a report based on variables or table in memory only?

...
2) Base the report on dot variables instead of a table. It is easy to get all the data I need for the report into dot variables and use those in a report. The dot variable will be in an array with a different size each run (like the number of records in the table). But the report has a detail section and groups based on one of the elements in the dot variables. How can I use a detail section and groups in a report that uses variables to hold the data?
You suggestions and any other ideas are welcome.

I would say yes, you can do this - sort of, but you can't use groups and so forth. Here is how I have done this:

You can write all your code, including formatting, to output into a global variable. In your report, put an RTF object in the detail section. Add the global variable to your report and drop it into the RTF. From your form or where ever you are going to run your report from, set the report to print the current record only (your report won't need any fields) - or conversely in the report filter you could put recno()=1, or some such. Make sure the RTF properties has "allow growth" checked. As far as grouping, do this in the code that outputs to the global. This does work as I have several reports like this. I think in my reports I write the data to a text object and then read the text object into the global as the last step before printing. Not sure why I did it that way, maybe it was for testing purposes, but you should be able to skip the text file and go straight to the global. Also, I use Courier New as the font for the variable so that the character spacing works. If you need to have bold and underlining, etc then you will have to embed HTML formatting into your output and use the printHTML() function.

[G Gabriel]

Re: Can I have a report based on variables or table in memory only?

I only read the title to this thread..so this answer might be redundant.
Yes you can create a report purely on variables only.

Few months back, I embarked on such a project since it involved very complex calculations. I, naively, thought it would be better for timing sake to do this purely in RAM as opposed to physical tables.
Well, after a major effort utilizing primarily arrays, I came to the realization that one of the best if not the best ways to do this is not via arrays, but rather csv files. They are very simple very quick and efficient.
But if you wish, you could do this, including grouping of course, via arrays only.

[DaveM]

Re: Can I have a report based on variables or table in memory only?

and why not a ram disk? it does reside in memory only. You do have to copy a complete dbf to memory or make it with code on command. you still have to sort/manipulate that data. A ram disk is like lightning compared to disk reads.

I really prefer Gabe and Peters ideas along with others saying the same thing,

You still have to get(in most cases) by reading table(s) to get the starting data. I do the same by reading this data to variables and then make extesive use of variables in a report. Been doing it for years. My largest report has never been timed, but it is almost instantaneous. I do it with UDFs and a script here or there.