Crystal Report

[Beast777]

I am doing a report and have tried it two different ways. One using the crystal report gen in VS and the second using Crystal Reports V10. Both are extremly slow to populate the data on the report. I think part of the problem is that the report queries 2 different databases. Crystal generates the queries like this:

SELECT "PersData"."Employee_Number", "PersData"."Last_Name", "PersData"."Initials", "PersData"."Squadron"
FROM "TrainingManagementDB"."dbo"."PersData" "PersData"
ORDER BY "PersData"."Squadron"


SELECT "CP"."DateIssue", "CP"."Deficiencies", "CP"."CPID", "CP"."SN"
FROM "Discipline"."dbo"."CP" "CP"

Is this a design problem? Should the tables be in the same database? How do I speed this up? Can I use a join statement between 2 different databases when they have a common column? Employee_Number is the primary key in the persdata table and SN is the same number in the CP table. Help please.

[JanZuk]

I join tables in different databases often in SQLServer.

SELECT PersData.Employee_Number, PersData.Last_Name, PersData.Initials, PersData.Squadron,
CP.DateIssue, CP.Deficiencies, CP.CPID, CP.SN


FROM TrainingManagementDB..PersData PersData

JOIN Discipline..CP CP
ON PersData.Employee_Number=CP.SN

ORDER BY PersData.Squadron

Are you passing this data to your report document's datasource (Push method) or are you binding this data directly in Crystal Reports and then just displaying the report (Pull Method)?

[Beast777]

Well now you got me there. I do not know which for sure, but the report is without data as it changes and pretty sure i use the bind method.

[Beast777]

It would seem that Crystal Reports will not let me paste in that SQL query. I tried generating a new report with no success either. Is the best thing to make a Stored Procedure and reference that?

[JanZuk]

That's what I do. First I create an xml Schema of the data coming back from the sProc. In my crystal report, I then set that xml Schema as the data source.

When pushing data to the report, it's as easy as initializing a report document, loading the report, setting the source to be the sProc data and then setting the report source to be the report.

example:

Dim oDoc As New ReportDocument
 
        oDoc.Load("c:\crystal reports\AccountsPayable\APCheck.rpt")
        oDoc.SetDataSource(New Check().GetChecksForPayment(dtCashReq.Text))
 
crv1.ReportSource = oDoc

[Beast777]

Does that make it faster? I created a SP and then created a report and just used the SP as the datasource. Everything worked fine and it does seem faster now. Will adding a XML schema make it even faster or is the method I am using fine?

[JanZuk]

It does make sense that the SProc as a datasource is faster because the data that you are getting back based on your query is much more condensed; you're only seeing what you want to see.

It's all a matter of what works for you. If this is all you need then I say go for it.

For reusability, I like to push the data to the report document instead of having the report document do all the work for me.

Say I design a crystal report for sales reps and management detailing individual sales by product. Management would like to have one report detailing every rep's sales but the sales reps would like to only see their own data. Since the schema is the same for each, I can write an sProc that will grab either all sales reps or an individual sales rep and pass that to my report doc.