How to setup a crosstab crystal report?

[smartcanix]

Hi there..

I have a problem here while setting up a crosstab report using crystal report. I'm currently using: VB6, ADO, MSSQL 2000, Crystal Reports v8.5 with TTX file as data source. I had using this method recently (this was quote from RobDog888's post )

Option Explicit
'Add reference to Crystal Reports x.x ActiveX Designer RunTime Library
'Add reference to Crystal Reports Viewer Control
'Add reference to Microsoft ActiveX Data Objects 2.x Library
'oCnn = current open ADO connection object
Private Sub Command1_Click()
 
    Dim oApp As CRAXDRT.Application
    Dim oReport As CRAXDRT.Report
    Dim oRs As ADODB.Recordset
    Dim sSQL As String
 
    sSQL = "SELECT * FROM Table1"
    Set oRs = New ADODB.Recordset
    Set oRs = oCnn.Execute(sSQL)
    Set oApp = New CRAXDRT.Application
    Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1)
    oReport.Database.SetDataSource oRs, 3, 1
    crvMyCRViewer.ReportSource = oReport
    crvMyCRViewer.ViewReport
 
End Sub

Then I came into problem when trying to setup a crosstab report. How do I setup a crosstab report using method as described above? Actually I didn't want to go for too much change for my code if possible.

Is it possible using oReport.Database.SetDataSource for two or more recordset? Because as we know that we need at least 3 tables as data source to create a crosstab report, doesn't it? CMIIW

Any help would be greatly appreciated. Thank you.

[Hack]

Moved to Reporting

[smartcanix]

well... any idea?

[VBFnewcomer]

If Iam not misleading you, why can't you simply make the rs in such a way to link more than one table and pass it to .SetDatasource. Or still better make a view/query in db itself and pass a simple * query to the view/query and extract the records.

[smartcanix]

Thanks VBFnewcomer for the respond.

Can we just passing 1 rs using .SetDataSource for creating a crosstab report in Crystal Report v8.5? Because as I know from the CR's crosstab example, we need at least 3 tables for setting up a crosstab report, in which first table for row data, second table for column data, and the third for the crosstab value itself. I need more info about this, thanks.

Beside that, actually when I call a crosstab report using .ViewReport, it may crash my application, what's wrong with that?

Any help would be appreciated.

[VBFnewcomer]

Pls see my post #4

Because as I know from the CR's crosstab example, we need at least 3 tables for setting up a crosstab report

I don't think so. You can create a CT even from one table if you desire. CT helps in comparing & analysing the data. More info of CT should be available in the Help of CR.

[smartcanix]

Well, so we can just linked the field we needed into just one recordset to set up a CT. I got it and going to try it, but the only problem now is:

Beside that, actually when I call a crosstab report using .ViewReport, it may crash my application, what's wrong with that?

Please help me with this one simple problem. Thanks.

[VBFnewcomer]

Beside that, actually when I call a crosstab report using .ViewReport, it may crash my application, what's wrong with that?

Try posting your relevant codes, so that somebody might help you.....Also post the Error message you get before the so called Crash!

[smartcanix]

Option Explicit
'Add reference to Crystal Reports x.x ActiveX Designer RunTime Library
'Add reference to Crystal Reports Viewer Control
'Add reference to Microsoft ActiveX Data Objects 2.x Library
'oCnn = current open ADO connection object
Private Sub Command1_Click()
 
    Dim oApp As CRAXDRT.Application
    Dim oReport As CRAXDRT.Report
    Dim oRs As ADODB.Recordset
    Dim sSQL As String
 
    sSQL = "SELECT * FROM Table1"
    Set oRs = New ADODB.Recordset
    Set oRs = oCnn.Execute(sSQL)
    Set oApp = New CRAXDRT.Application
    Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1)
    oReport.Database.SetDataSource oRs, 3, 1
    crvMyCRViewer.ReportSource = oReport
    crvMyCRViewer.ViewReport
 
End Sub

Nothing special from my code, that sample show my method. The crash occurs exactly at Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1) statement if MyReport.rpt was a crosstab report file. But it opened successfully if it was a standard report without crosstab.

And no error message shows up, my application just crash with the standard dialog from microsoft to send or don't send report to microsoft. no useful information about what's wrong with the report file. I've try this with several report file, everytime I've added a crosstab into a report, it wouldn't show and just crash my app.

Any help would be appreciated. Thanks.

[VBFnewcomer]

standard dialog from microsoft to send or don't send report to microsoft

just make sure this is the error msgbox which comes out. It might be file not found (have break point at Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1) and run in break mode using F8) Iam not able to duplicate your problem
The query is also wrong. To get the correct query string, in the report click Database->Show SQL Query...

[smartcanix]

no, I'm sure the file exists there. too bad that you can't reproduce my problem. even i'm using F8 to step through line by line, whenever I execute that line of code "Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1)", my application will crash. For more information, I've been trying this on both Vista and XP.

BTW, if you don't mind, can you please give me a link which show me how to open CR8.5 crosstab report using VB6 ?

Thank you.

[VBFnewcomer]

I'm sure the file exists there

just for my sake place

  If Len(Dir(App.Path & "\MyReport.rpt")) > 0 Then
    MsgBox "Got the file"
  Else
    MsgBox "No luck dude. See there is no file"
  End If

before

    Set oReport = oApp.OpenReport(App.Path & "\MyReport.rpt", 1)

Just one more thing (actually two)
1. Are you sure you added (using Projects->components..) the Crystal Reports Viewer Control (very important)
2.oApp.OpenReport(App.Path & "\MyReport.rpt", 1) is the 1 necessary? can't you just leave it out

if you don't mind, can you please give me a link which show me how to open CR8.5 crosstab report using VB6

are we not on this sub all along?

[smartcanix]

If Len(Dir(App.Path & "\MyReport.rpt")) > 0 Then
MsgBox "Got the file"
Else
MsgBox "No luck dude. See there is no file"
End If

Added, Got the file.

Are you sure you added (using Projects->components..) the Crystal Reports Viewer Control (very important)

Yes, added already. if not, I think I will stuck on crvMyCRViewer.ReportSource = oReport

oApp.OpenReport(App.Path & "\MyReport.rpt", 1) is the 1 necessary? can't you just leave it out

Sure, but it give me the same result, application crash.

if you don't mind, can you please give me a link which show me how to open CR8.5 crosstab report using VB6

are we not on this sub all along?

I mean a link with sample code. maybe we can work it out using another method for this problem.


I think there's nothing wrong with file not exist, broken system files, or anything else, beside I can't call .OpenReport a file with crosstab structure inside the report file.

[VBFnewcomer]

try to PM shakthi5385 he's a kinda good at CRs. He might join us here to solve the problem

[smartcanix]

Okay will try to PM him. Hope he will join us.

BTW, it's nice to have you to help me these days. Thanks.

[smartcanix]

try to PM shakthi5385 he's a kinda good at CRs. He might join us here to solve the problem

PM'ed shakti5385 instead of shakthi5385, just because user with ID shakthi5385 can't be found.

Hoping that I didn't PM wrong person.

[VBFnewcomer]

yeah he is the one sorry my mistake

[smartcanix]

no prob. let's see if he'll come to join us here.

anyone else also welcome here.