I'm intending to use Crystal from a VB.NET 2 WinForms program. The program will have several users connecting to a central SQL Server database, and each will wish to produce the same report, but on different data subsets. Rather like producing order line-item lists for different orders. Of course, this'll happen at the same time (Murphy's law !).

It seems that I can either create a data source (a View, for example) and a corresponding report for each user, or else a temporary table (#results or res_<username>) and a report for each user.

Am I missing something fundamental ? I would have thought that I should be able to run a report and, at the same time, tell it what table to get its info from (I promise to always have the same table structure <grin>).

Can I do that ?

Mike Irwin

(PS. If this qualifies me for a free copy of your book: I already have it - just haven't read it all yet - so please keep my copy for someone who wouldn't be able to afford it - like in India - and I'll pay packing/postage for the long-distance mailing.)

Merry Christmas

WOW ! An answer from The Master Himself 

Seriously, thanks very much for the info. What I had thought of doing was having the code direct the data into #results, which is "different" for every user, albeit having the same name, as it's in tempdb. However, I couldn't work out how to persuade the Wizard to connect to a table that didn't (yet) exist ! I think you've shown me how, so I'll just have to specify it in code, rather than the wizard, but it'll always be the same generation code and the same name.

Thanks very much

Mike

PS Merry Christmas and a Happy and Prosperous New Year to you too.

The neat thing about temporary tables in Sybase and MS databases is that it doesn't matter what you call them, they're always "yours". That means that if your instance of the program (runs in Windows as your id) and my instance both run the same query at exactly the same time, both outputting to the same temporary table - something like

select * into #MyRes from sourcetbl where fieldname = criterion

then you'll get a table in the tempdb database with a name something like

#MyRes________________________________________________D0E7

and I'll get one named something like

#MyRes________________________________________________011F

(I may well have missed out quite a few "_" characters ! )

But the clever thing is that they're both referred to as #MyRes - your instance gets your copy and my instance gets its copy. How cool is that ?

The problem is that they vanish as soon as the process owning them does. That's ok if your process is a program, but if you create them with a query using Visual Studio then they vanish as soon as you say "OK" to the dialog box reporting that the query worked. Creating one in another program - like the Management Console - doesn't work - different instance of the connection to the SQL Server engine :(

So, I think that I can do it by making a table at design time and linking the report to that and then changing the data member of the report to #MyRes at run time, but that's an experiment for tomorrow.

It also strikes me as a kludge. I suppose that you'll now tell me that I should be using an XML source file, that doesn't have any data until the code fills it. I didn't do that 'cos I haven't done that part of VB.NET yet (see my tag line below :)

Mike