Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Data Connectivity
Message Icon Topic: Dynamic data sources ? Post Reply Post New Topic
Author Message
LeMike
Newbie
Newbie
Avatar

Joined: 18 Dec 2006
Online Status: Offline
Posts: 5
Quote LeMike Replybullet Topic: Dynamic data sources ?
    Posted: 23 Dec 2006 at 9:01am
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 ChristmasBig%20smile
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 23 Dec 2006 at 3:31pm
Hi Mike,

Thanks for your good will of offering your copy of a book to those less fortunate.  I'll be having a random drawing for everyone who posts a question once we get a hundred people. I'll keep this in mind if you get chosen.

Re your question, an easy way to do it is to use a DataSet object and populate it with a custom SQL statement. You can change the SQL statement based upon the user.

I don't recommend this for people using CR.NET 2003 because datasets are very slow. But they rebuilt the ADO.NET DLLs for Crystal Reports.NET 2005 and they are much faster now.

As you said, just make sure the resulting table structure is EXACT for each SQL statement. The smallest change will cause Crystal Reports to not recognize the dataset and you'll get a dialog box asking you to login (a sign that the data doesn't match what your report is expecting).

Merry Christmas!  
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
LeMike
Newbie
Newbie
Avatar

Joined: 18 Dec 2006
Online Status: Offline
Posts: 5
Quote LeMike Replybullet Posted: 23 Dec 2006 at 5:09pm
WOW ! An answer from The Master Himself Big%20smile

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.
It's not because things are hard that we don't try them ... it's because we don't try them that they are hard. (Lucius Seneca)
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 23 Dec 2006 at 8:13pm
Well, flattery will get you everywhere. 

Seriously, if you want to use the #results method, then just create a SP that builds a generic temp table using the table structure you want and build the crystal report using this SP. After you get the report working, customize the SP for each user by passing the user key to the SP as a parameter and let the SP build the custom temp table based upon the value of that parameter.

Oh yeah, when you create the first draft of the SP, make sure it has the parameter specified so that when crystal reports analyzes it then it sees the parameter and accounts for it in the report definition.

Brian

Hey - wouldn't it be cool if there was an emoticon with holiday symbols like a christamas tree? 
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
LeMike
Newbie
Newbie
Avatar

Joined: 18 Dec 2006
Online Status: Offline
Posts: 5
Quote LeMike Replybullet Posted: 23 Dec 2006 at 9:18pm
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


It's not because things are hard that we don't try them ... it's because we don't try them that they are hard. (Lucius Seneca)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.