selection formula to subreport: Crystal Reports

msmeth
04-20-2004, 06:35 AM
I've never used the subreport feature of Crystal report and do not know how to call up a report that has them from VB. I have a report with 3 subreports. I need to have the reports only display values for a specific date. How do I pass the selection formulas to the subreports? Is it just the normal way? I use this to select the records of the primary report, but the subreports are showing all dates....


With CrysReport
.ReportFileName = App.Path & ReportName
.DataFiles(0) = App.Path & "\DB.mdb"
.SelectionFormula = Selection 'defined my selection formula elsewhere
.DiscardSavedData = True
.WindowState = crptMaximized
.Destination = crptToWindow
.Action = 1
End With

MKoslof
04-20-2004, 06:50 AM
You should use the CRAXDRT object library and the RDC for this. You will get much better results. Here is an example I posted:

http://www.visualbasicforum.com/showthread.php?t=139098

msmeth
04-20-2004, 07:01 AM
You should use the CRAXDRT object library and the RDC for this. You will get much better results. Here is an example I posted:

http://www.xtremevbtalk.com/showthread.php?t=139098

Holy moley :huh: . That looks interesting...never used that before so I'll try it out and let you know how it goes ;) . Thanks.

MKoslof
04-20-2004, 07:02 AM
That is the best way to code Crystal Reports for VB (in my opinion of course). Make sure when you design your reports that you use the Active Data Driver (or ADO) as the database driver.

msmeth
04-20-2004, 07:42 AM
That is the best way to code Crystal Reports for VB (in my opinion of course). Make sure when you design your reports that you use the Active Data Driver (or ADO) as the database driver.

I didn't even know you could do that. I've just always used the default and never thought about it. Do you do it thru Database->Convert Database Driver?

MKoslof
04-20-2004, 07:45 AM
Yes..if using a version prior to 9 choose "more Data Source" then ADO. If 9 or higher, simply select the ADO driver.

msmeth
04-21-2004, 05:44 AM
Yes..if using a version prior to 9 choose "more Data Source" then ADO. If 9 or higher, simply select the ADO driver.

I finally got around to trying this crxdrt stuff and I have a view questions. That code you posted in that link you sent, how do I make the report visible? Instead of selection formulas, is the report populated based on the recordset created and opened?

Also, you said 'for MS Access or flat datasource, use the .location method...I'm not sure what you mean by that. Where in that line do I use this?

msmeth
04-21-2004, 06:15 AM
I got it visible thru the CRViewer control, but the subreports are overlapping the primary. How do I set it up in crystal reports to fix this?

MKoslof
04-21-2004, 06:19 AM
How have you developed your reports in design view? Try putting the sub-report in the Report Footer (the Main report footer). You need to make sure that you place your subReport after the main detail section of the main report

msmeth
04-21-2004, 06:22 AM
How have you developed your reports in design view? Try putting the sub-report in the Report Footer (the Main report footer). You need to make sure that you place your subReport after the main detail section of the main report

Hmm....I have two subreports in the Report footer (main is in detail section). That's the problem there. I'm going to end up having at least 3 subreports so I guess I'll need separate sections for each?

MKoslof
04-21-2004, 06:38 AM
Yes...each report should have its own section. However, if add a report, Crystal should just allot the space for you. Do you have any formulas or bound fields to the main report in the report footer? Try placing your reports directly after the last detail section of the main report...they should be able to stack on top of each other. You might want to consider a cross tab report style too.

msmeth
04-21-2004, 06:44 AM
Yes...each report should have its own section. However, if add a report, Crystal should just allot the space for you. Do you have any formulas or bound fields to the main report in the report footer? Try placing your reports directly after the last detail section of the main report...they should be able to stack on top of each other. You might want to consider a cross tab report style too.

When I insert a subreport, Crystal doesn't add a section for me. It just gives me the outline of the box and I have to put it where I want it.

I had placed them directly after the last detail section, which is the report footer but then they overlapped each other. I have now created separate sections for them and they're fine.

However, the queries passed to the subreport from code isn't working. The query itself returns the correct values, but it's not showing in the report.

Also, how do I pass a different query to each subreport? I know this section of code loops thru and finds all the subreports, but isn't it setting the same recordset to each one? My subreports are all generated from the same table.


If newObject.Kind = crSubreportObject Then
'Found a subreport, create an new instance
Set crxSubObj = newObject
'Open the subreport and now treat it like the main report
'if different tables, use the for loop structure of tables again
Set crxSub = crxSubObj.OpenSubreport
'assign the second recordset to the subreport
crxSub.Database.SetDataSource rs2
End If

MKoslof
04-21-2004, 10:00 AM
OK, sorry, work is busy today, I am not as assessible as usual. Basically, you want to create several object's OR using the same object and always set it to nothing. Then, when you loop, if you find a sub report do a select case on its name (each report should have an unique name). Then, based on the name of the report, you assign the proper datasource. Then continue your loop.

msmeth
04-21-2004, 10:53 AM
OK, sorry, work is busy today, I am not as assessible as usual. Basically, you want to create several object's OR using the same object and always set it to nothing. Then, when you loop, if you find a sub report do a select case on its name (each report should have an unique name). Then, based on the name of the report, you assign the proper datasource. Then continue your loop.

Understood, but how do you check the name of the subreport it found? We have crxSub as a Report object and crxSubObj as a subreportObject. The only method I can find referring to a name is the subreportObject method Name, but it's returning "Subreport1" but both of my subreports are properly named.

EDIT: K, I found the method *LOL* I Didn't think to look for .SubreportName.

Anyway, I use this:
crxSubObj.SubreportName = "FilterReport.rpt" Then crxSub.Database.SetDataSource rs2 but it's still not returning the proper records. :mad:

msmeth
04-22-2004, 04:16 AM
What am I doing wrong? :mad: The subreports aren't taking the recordsets...it matches the criteria for the If but the records showing in the subreport aren't right.


If newObject.Kind = crSubreportObject Then
'Found a subreport, create an new instance
Set crxSubObj = newObject
'Open the subreport and now treat it like the main report
Set crxSub = crxSubObj.OpenSubreport
'assign the second recordset to the subreport
if crxSub.Subreportname = "FilterReport.rpt" Then crxSub.Database.SetDataSource rs2
End If

MKoslof
04-22-2004, 07:13 AM
Do these sub reports have the same data source location as the main report? Also, make sure you use .discardsavedData for all objects so any saved data is removed.

msmeth
04-22-2004, 08:22 AM
Do these sub reports have the same data source location as the main report? Also, make sure you use .discardsavedData for all objects so any saved data is removed.

Sorry abt the cross-post...I don't usually do that...this is getting kinda long.

Anyway, yes the subreports have the same datasource location and table and I have used Discardsaveddata.

I'm not sure I did the Location part right though. I didn't understand what you meant...I did this:


For Each crxTable In crxReport.Database.Tables
crxTable.SetLogOnInfo crxTable.Location, "dbname.mdb", "Admin", ""
Next


The primary report is giving the correct records, so something must be right. Would I need to do that for the subreports too maybe?

MKoslof
04-22-2004, 08:31 AM
Yes, you are a little confused, SetLogonInfo is used for SQL Server and Oracle, etc. For Access, you CAN use it, but you don't have to, instead use .Location exclusively, like so:



For Each crxTable In crxReport.Database.Tables
crxTable.Location = App.Path & "\myDatabase.mdb"
Next



and yes, do this for all report objects and remember to discardsavedData for each too..

msmeth
04-22-2004, 08:45 AM
Yes, you are a little confused, SetLogonInfo is used for SQL Server and Oracle, etc. For Access, you CAN use it, but you don't have to, instead use .Location exclusively, like so:



For Each crxTable In crxReport.Database.Tables
crxTable.Location = App.Path & "\myDatabase.mdb"
Next



and yes, do this for all report objects and remember to discardsavedData for each too..

K, that makes more sense, but still no go.....what about in the Viewer control where you set the report source? I've got it set to the main report name...do I maybe need to include the subreports somehow or am I just clutching at straws now? I can't see why this isn't working....


Set crSections = crxReport.Sections

'Go through each section in the main report...
For Each crSection In crSections
'get all the objects in this section...
Set crObjects = crSection.ReportObjects
For Each newObject In crObjects
'find any subreport that is embedded

If newObject.Kind = crSubreportObject Then
'Found a subreport, create a new instance
Set crxSubObj = newObject
'Open the subreport; treat it like the main report
Set crxSub = crxSubObj.OpenSubreport

For Each crxTable In crxSub.Database.Tables
crxTable.Location = cnPath & "\dbName.mdb"
Next

crxSub.DiscardSavedData
'assign the second recordset to the subreport
If crxSubObj.SubreportName = "FilterReport.rpt" Then crxSub.Database.SetDataSource rs2
End If
Next newObject
Next crSection

CRViewer1.ReportSource = crxReport 'maybe this needs to be to crxSub too?
CRViewer1.ViewReport

MKoslof
04-22-2004, 01:19 PM
No, I assume all subreports are housed within the Parent Report. So, if you view the main report, all the subReports will appear to. Well, I see one area where there could be a problem. How many subreports do you have? Didn't you say earlier you had more than one? If so, you only assign the DataSource to one name. Your For loop will continue to pass, only checking for ONE name "FilterReport.rpt". So, if you have three reports, the other two are never modified.

And you never actually open the subReport if you want to use the .OpenSubReport Method, somewhere in your code (after assigning the object) you need to do this:



Set crxSub = mainReport.OpenSubReport("FilterReport.rpt")



But "FilterReport.rpt" would be the value passed with a select case statement. So, if you find that the NewObject.Kind is a subReport, THEN do a select case on its name. If you find the name you want, set the subReport object equal to the mainReport.OpenSubReport(name) and then proceed with your code.

msmeth
04-22-2004, 01:50 PM
No, I assume all subreports are housed within the Parent Report. So, if you view the main report, all the subReports will appear to. Well, I see one area where there could be a problem. How many subreports do you have? Didn't you say earlier you had more than one? If so, you only assign the DataSource to one name. Your For loop will continue to pass, only checking for ONE name "FilterReport.rpt". So, if you have three reports, the other two are never modified.

And you never actually open the subReport if you want to use the .OpenSubReport Method, somewhere in your code (after assigning the object) you need to do this:



Set crxSub = mainReport.OpenSubReport("FilterReport.rpt")



But "FilterReport.rpt" would be the value passed with a select case statement. So, if you find that the NewObject.Kind is a subReport, THEN do a select case on its name. If you find the name you want, set the subReport object equal to the mainReport.OpenSubReport(name) and then proceed with your code.

Yes, I have more than one, but I'm just trying to get one to work for now. I did what you just told me, with the same results...wrong records returned :confused:


If newObject.Kind = crSubreportObject Then
'Found a subreport, create a new instance
Set crxSubObj = newObject
Set crxSub = crxSubObj.OpenSubreport

Select Case crxSubObj.SubreportName
Case "FilterReport.rpt"
Set crxSub = crxReport.OpenSubreport(crxSubObj.SubreportName)
crxSub.DiscardSavedData
crxSub.Database.SetDataSource rs2
End Select
End If

MKoslof
04-22-2004, 02:06 PM
OK, step through this code..see if it fails anywhere. I have added some declarations that you don't have (and that apply to this code piece):



Dim CrDatabaseTable As CRAXDRT.DatabaseTable
Dim CrSections As CRAXDRT.Sections
Dim CrSection As CRAXDRT.Section
Dim CrReportObjs As CRAXDRT.ReportObjects
Dim CrSubreportObj As CRAXDRT.SubreportObject
Dim CrSubreport As CRAXDRT.Report

'assume crRep is our opened, Main report
'first get a variable representation of all sections

Set CrSections = CrRep.Sections

'loop all sections, they start at one

For x = 1 To CrSections.Count

'get the individual section
'get the individual report objects
Set CrSection = CrSections.Item(x)
Set CrReportObjs = CrSection.ReportObjects

'now loop all objects found
For y = 1 To CrReportObjs.Count

'if we find a subReport, act accordingly

If CrReportObjs.Item(y).Kind = crSubreportObject Then

'set our first DEFINED subReport Object variable

Set CrSubreportObj = CrReportObjs.Item(y)

' a test, what is being passed back?

MsgBox CrSubreportObj.Name

'now assign the second object instance to the OpenSubReport method

Set CrSubreport = CrSubreportObj.OpenSubreport

'set the location and then pass the recordset
For Each CrDatabaseTable In CrSubreport.Database.Tables
CrDatabaseTable.Location = App.Path & "myMdb.mdb"
Next

CrSubreport.DiscardSavedData

'just in case, use the default data tag after the rs object, 3
CrSubreport.Database.SetDataSource rs1, 3
End If
Next
Next

msmeth
04-22-2004, 02:08 PM
I used these lines for my main and subreports respectively:
Debug.Print crxReport.Database.Tables(1).DecriptiveName
Debug.Print crxSub.Database.Tables(1).DecriptiveName

and received:
Active Data (Field Definitions Only)
Microsoft DAO Database DLL

respectively....Could that be the problem? Why is the subreport not ADO...I've set up the driver the exact same way I did for the main....

MKoslof
04-22-2004, 02:14 PM
So your first report is producing Active Data (Field Definitions Only) this is OK. Your second report should not be DAO..it needs to be using the Active Data Driver as well.

msmeth
04-22-2004, 02:20 PM
So your first report is producing Active Data (Field Definitions Only) this is OK. Your second report should not be DAO..it needs to be using the Active Data Driver as well.

I thought so and that's why I did that Database->Convert Database Driver thing the exact same way on all the subreports as I did on the main. I've redone them and it still keeps telling me DAO when I use debug.print!

MKoslof
04-22-2004, 02:38 PM
Interesting. Never seen that problem before...let me try to reproduce it. Create a new subReport and choose More Data Source...Active Data Driver..what happens.

msmeth
04-22-2004, 02:58 PM
Interesting. Never seen that problem before...let me try to reproduce it. Create a new subReport and choose More Data Source...Active Data Driver..what happens.

I'm home from work and gone for the weekend so I'll have another go at it on Monday....it's quite frustrating. I'll post on Monday and let u know what happened...

MKoslof
04-22-2004, 03:11 PM
OK, let me know. The code sample I posted should work. The problem seems to be your subReport declarations. I created a test sub report in version 9 (what I currently have loaded at work) and I couldnt reproduce that error..I get all ADO based reports. I don't remember when I used 8.5 last having that issue either.

msmeth
04-26-2004, 05:23 AM
Interesting. Never seen that problem before...let me try to reproduce it. Create a new subReport and choose More Data Source...Active Data Driver..what happens.

I did that and it worked. I always love when it's little things like that that cause me stress for days :mad: Thanks for all your help!

MKoslof
04-26-2004, 07:35 AM
Congrats! Glad you got it work. Good luck

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum