Base a report on a temporary table

[Tbrondolo]

I want to create a temporary table and run a report on that table. Is there a method to dynamically change the name of the table a report is based on?

The table structure would be the same across the temporary tables. The need for this is to unencrypt sensitive data, then store in a temporary table that can be deleted after the report has run.

[mikeallenbrown]

I don't think so. I'd recommend running your report off of a stored procedure in your case. Then you can handle all of the temp table stuff there.

[lgrupido]

Instead of a temp table, create a real table with the columns you need plus one more column: Username.

Then you can remove all the rows for the current user, repopulate it with the data you want, and then run your report over that same table filtered by username.

Other users can run the report at the same time because they will only affect records with their username.

Note: This method requires that everyone have a unique username.

I generally like this method because if something is wrong, you can actually inspect the data that has been created.

And if you like stored procedures you can still use one to populate the table - or you can use Xbasic or any other method you like.

[Pat Bremkamp]

I've used temp tables for reports for years and there are benefits that you may not have thought of. The first is, for a long report, the temp table doesn't stop workflow by having the report tie up an important table while you wait for it to finish. Second, if your report draws fields from several tables and uses calculations and concatinations, building a single table with just the fields you need in the format and order you need for your report is very fast, so creating the report is faster and easier and trouble shooting doesn't require you to print out the table, just look at the table, Third, temp tables are by definition not needed forever, so if there are dead periods in your use of the database, you can truncate the table with one very fast sql command. It never grows too big.
I usually use a combination of the suggestions above. I create the table with a stored procedure. In my experience that's about 10 times faster than using Alpha if Alpha and your database are not on the same server. I also make it multiuser so I never have to stop it in periods of high use. If you are concerned about sensitive data, you may be able to give each user a different encription key and decript the fields in calc fields in the report itself. Never tried it, but it might work..

[Tbrondolo]

Thanks for all the ideas. I built a view of the data and based the report on the view. That helped with the speed of the report. The task here is that key field data is encrypted and when I run the report I decrypt the data and store it in another field in the table. I then mark all the records I need to report on and run the report output to HTML. The problem is that I have to then clear the data in the decrypted field and unmark the records at the end of the report procedure. When I do this I then can't use the built in features to export the report to PDF as it tries to run the query on now unmarked records. There is also a problem if two or more people run the same report at the same time all on marked records. So I was looking for a way to make a unique results table that can then feed a report and then after say 60 seconds I can then trash the table. To do this I would need to get the name of a temp table into the report query.

[lgrupido]

Still lots of ways to do this:

You could still use one of the methods above, where the data is in a set table. Open the report into a window, and then in the onclose of the window, you can run a procedure to remove the data.

Or put something in a timeout for whatever time period you think is appropriate. Start the timer at the end of whatever script runs the report and the javascript will fire. This isn't quite as helpful because if they close the application when they close the report it might not run.

Or setup a process that runs in the background all of the time. (Maybe every 10 minutes.)

Include a timestamp in the data. Your background process can check the table for any data that is older than 10 minutes and then delete it.

Or some variation of the above...