[RESOLVED] Request help with CR9 report

Printable View

Feb 27th, 2013, 03:34 PM
sgarv

[RESOLVED] Request help with CR9 report
Hi all. Thank you for taking a look at this. I apologize for the sheer size of the post. I tried to keep it as short as I could. I inherited a legacy report built using CR9. An application written in VB6 calls it. Our back end is SQL Server 2005. I should note that I have no experience with Crystal Reports.

The report uses a subreport. Both reports have server and database information hardcoded in them.

Both reports have a database, tables and fields defined from within. Additionally, the VB6 code passes an ADO record set to the main report. Since the subreport displays the details, I assume that the record set is passed from the app to the main report and then to the subreport.

The subreport is explicitly referenced within the RPT file for the main report. The code below shows a line that explicitly sets the subreport, but I don’t know how necessary it is to do this since the subreport name is referenced within the RPT file.

The code that is being used to call the report is the following:

Code:

Dim sSQL As String Dim crApp As New CRAXDRT.Application Dim crReport As New CRAXDRT.Report Dim crReportSub As New CRAXDRT.Report Dim rsRpt As New ADODB.Recordset Set crReport = crApp.OpenReport(strRPTPath) ‘Full path where RPT file is located 'Create the recordset that will be passed to the report. sSQL = "select * from RepTemptable" ‘Call function to create the record set given the query. Set rsRpt = fnGetADORS(sSQL) crReport.Database.Tables(1).SetDataSource rsRpt, 3 ‘Full path of the sub report Set crReportSub = crReport.OpenSubreport("SubReportDet.rpt") 'Send to screen. ‘ ‘ fCR9 is form where CRViewer 9 object oCRV9 is located. fCR9.Visible = False fCR9.oCRV9.ReportSource = crReport fCR9.oCRV9.ViewReport fCR9.oCRV9.EnableGroupTree = False 'The report stays onscreen until the user exits, fCR9.Show vbModal Set fCR9 = Nothing

This report was broken when the database was moved to another server (actually two servers, same database, different data). So far I’ve done the following:
1. With the original report I tried to call the report it gave me the error “database not found”. This was due to the change in severs. I managed to define the new server name and updated the preexisting server name. I ran the report within the CR (standalone) Designer and it and the subreport looked ok (did not verify data, but that is in its own time).
2. I ran the app and called the modified report, but now I am getting Logon failed for user ‘AppDBUsr’. It seems that the password is required. I added the following code, but this did not fix anything, remains the same:
  
  Code:
  
  crReport.Database.Tables(1).ConnectionProperties.Add "Connection String", _ "Driver=SQLOLEDB;Data Source=CORPDATASQL1;” _ “Initial Catalog=MainDB;” _ “User ID=AppDBUsr;” _ “Password=****;” _ “Trusted_Connection=yes"
3. I also attempted to remove the hardcoded database references within the RPT report file, but I get a message indicating that certain fields within that data source are being used.
My goal is to eliminate these completely so that the calling app can set these since the server and database names might vary (although the tables and fields within the DB remain the same)

The fields that the report references within itself are used as Group Headers (4 of them).

What I can’t seem to figure out:
1. How is the ADO record set that is passed to the report referenced within the report or subreport if that is the case?
2. How can I eliminate the 4 fields that are referenced within the report as Group Header and instead use data from a record set that I pass to the report?
I’ve done research and found lots of Crystal Reports information, but most is for versions other than 9. Some videos on You Tube look useful, but from a general point of view. Since I lack the skills at this time I am willing to do my homework and follow any orientation or suggestions given here.

Thank you.
Salvador
Feb 28th, 2013, 04:33 PM
sgarv

Re: Request help with CR9 report
Follow up. One small step...

I did more research and found various options. One item where I had doubt was the connection information for the report. To see what I had in there I added this code

Code:

Dim ocp As CRAXDRT.ConnectionProperty For Each ocp In crReport.Database.Tables(1).ConnectionProperties Debug.Print ocp.LocalizedName & " = " & ocp.Value Next

As I stepped through the code I noticed that the connection properties were correctly set already. This exercise also helped to see what is initially in that collection. I need to be careful and use the same value names (LocalizedName) just in case CR is only compatible with these (for example "Initial Catalog" works, but "Database" does not - I will eventuaqlly test this too). Good enough for testing. One interesting item is that when I tried to "print" the Password item in the collection VB gave me an error indicating that it was a write only property. I then added the following line before the SetDataSource command line:

Code:

'Set password. crReport.Database.Tables(1).ConnectionProperties.Item("Password") = "****" crReport.Database.Tables(1).SetDataSource rsRpt, 3

This got me a successful logon and displayed the report as expected (yay!)

My next steps are
- Understand well how the passed recordset interacts with the report
- Test with both data servers and databases to verify that dynamically changing database connect information is working
- Migrate temporary tables to true # temp tables.
This last item might be a bit tricky, since from the research I've done the data needs to be passed to the report since the # temp table is only valid within the session that created it, so I can't depend on the RPT file doing its own connection.

Thanks to all that have read my long winded initial post. Salvador
Mar 14th, 2013, 02:41 PM
sgarv

Re: Request help with CR9 report

Again, thanks to all that took the time to read the original post. After much research and experimentation I can say that I resolved all the problems I had. The solutions are documented here for posterity.

Problem

I inherited a report made using CR6 that is called from a VB6 app. The report worked fine until the database was moved to two different SQL Servers. Also, the report logic in VB6 used two temporary tables in such a way that only two users could run the report at a time. Even worse, each temp table was tied to a specific printer. Note: When I say “temporary” I refer to a normal SQL Server table that is used as a temp worksheet, not at all a #temp table.

The report employed a subreport to print out the details of the receipt and printed out two distinct copies, one occupying the top half of an 8x11 sheet, the other the bottom half.

Research

I found out the following:

The main RPT report used data to format the overall report. The data came from the VB6 app. The sub report also used data to print out the detailed information, but it was querying the db table directly, not getting the data from the VB6 app.

The report was attempting to connect to the old (now non-existent) SQL Server. It was having a really bad day.

I could not get the report to connect to the new SQL Server(s) through code. I found some sample VB6 code, but it did not work.

I could not fully understand the following statement:

Code:

crReport.Database.Tables(1).SetDataSource rsRpt, 3

Why Tables(1)? How was this collection numbered? What was that 3 option? It is called DataTag, but this did not tell me anything. Did in indicate an instance of the record set being passed? How was it related to the objects in the RPT file?

Solutions

The first task was understanding the Tables collection. It turns out that the RPT file is really a “database” of information pertaining to the report. Once the RPT file is opened the report object that is created contains many of the report’s attributes. The Tables collection lists all the tables that have been defined in the RPT using the Designer (or equivalent). When designing the report and adding data fields, one must first tell the designer which fields will be used within the report. This is an easy task. With this, we also tell the Designer the table, database and server name. Finally, so that the designer can go and fetch the data that we’ll use in our report, it connects to the DB. For this we need to provide logon credentials. If the fields that will be used within the report come from more than one table then we need to repeat the above process for each table. In the end we have a list of tables that the report will use to print (or display) the data that we want.

Once we have defined the fields that we need I speculate that the Designer builds a query that it uses to fetch the information every time the report is called and it takes the needed data from this default data source.

The Tables collection contains the names of these tables. If we want to get a peek to what is in this collection we can use a loop like the following:

Code:

'crReport is object that opened the CR9 RPT file. For iTblCnt = 1 To crReport.Database.Tables.Count Debug.Print crReport.Database.Tables(iTblCnt).Name Next iTblCnt

This will run through the Tables collection printing the table name in the Immediate Window. The Tables collection element contains the data set that will be used to populate the report, but that data can come from numerous sources, one of them being a record set object that is passed to the report using the SetDataSource method.

This brought me to the “3” parameter. After getting creative with my search phrases I finally found a page that described each parameter. The 3 indicates that the data object that is passed is an ADO object… and guess what, as of CR9 this was the only option that was supported (also assuming that the info I found pertained to CR9). So basically this meant that the “3” parameter should always be 3, end of story.

Using the SetDataSource method is not as straightforward as it seems. If the report has various tables defined then we need to determine which element of the Tables collection corresponds to the table or better said data source that we want to replace. To find this out was easy. I used the loop above checking for a specific name. When I defined the table in the CR9 Designer it allowed me an alias. For example, if my table name was SalesTrans I would select that from the tables list, but I could internally name it SalesTrans01. The internal name is what is in the Name property of the Tables collection as shown in the code snippet above.

Since the table name, as defined within the report is known to me, all I had to do was check each table name element looking for the name. When I found it I knew that I was referencing the correct Tables element that corresponded to the data source that I was about to replace. Then I used the SetDataSource method for that Table element passing the ADO record set object.

Another obstacle was passing the data to the sub report. The previous code did not do this. Since the sub report uses more than one table, I used the loop structure shown before to loop through the Tables collection looking for the right table. Once found I passed the record set containing all the detail data for the sub report.

This half worked. I noticed that the bottom half of the report was blank! Somehow, I was only passing the data to the sub report in charge of displaying the upper half of the receipt. A colleague took a look at my code and the RPT file and after just a little while figured out what was gong on. To print the bottom half of the receipt a second instance of the sub report needed to be opened and the date set passed to it. This second instance is just the report name with an “ – 01” appended to the end.

I needed to open a second sub report variable specifying the sub report name plus the suffix as the report name:

Code:

Dim crReport As New CRAXDRT.Report Dim crReportSub As New CRAXDRT.Report Dim crReportSub01 As New CRAXDRT.Report 'strRptFile – full file path of the RPT file 'strSubRptName – Name of the sub report as defined within the main RPT file ' This is not the file spec although the sub report name will usually be ' the name of the rpt file. 'Main report. Set crReport = crApp.OpenReport(strRptFile) 'Sub report, top half of sheet. Set crReportSub = crReport.OpenSubreport(strSubRptName) 'Sub report, bottom half of sheet. Set crReportSub01 = crReport.OpenSubreport(strSubRptName & " - 01")

After the second instance was opened I just did the loop again looking for the table name that I was going to replace and used SetDataSource against the object variable of the second sub report to pass the same record set I passed to the first instance of the sub report.

Now I understood how the Tables collection was related to the report file and to the record set data. Next was connecting to the db of my choice. After some research and failed attempts I found the following code that worked:

Code:

crReport.Database.Tables(1).ConnectionProperties.DeleteAll 'I need to pass DB logon credentials to the report. crReport.Database.Tables(1).ConnectionProperties.Add "Provider", "SQLOLEDB" crReport.Database.Tables(1).ConnectionProperties.Add "Data Source", strSrv crReport.Database.Tables(1).ConnectionProperties.Add "Initial Catalog", strDB crReport.Database.Tables(1).ConnectionProperties.Add "User ID", strDBUsr crReport.Database.Tables(1).ConnectionProperties.Add "Password", strDBPwd

To determine the names that I needed to add (“Provider”, etc.) I also looped through the original ConnectionProperties collection just before doing the Delete All:

Code:

Dim ocp As CRAXDRT.ConnectionProperty For Each ocp In crReport.Database.Tables(1).ConnectionProperties If ocp.LocalizedName = "Password" Then Debug.Print "Password = *****" Else Debug.Print ocp.LocalizedName & " = " & ocp.Value End If Next

Note that I explicitly used Tables(1). This is because the main report only had one table defined, so I knew it would always be Tables(1). The exception for the password is needed because this property is write-only and will generate an error if I try to print out its value (or access it in any way). The above code yielded 15 elements (in my particular case) of which I added the 5 that interested me as shown above in the Add statements.

After I added the items to the ConnectionProperties collection I was able to successfully connect to the server of my choice as indicated by the calling VB6 app.

Removing the dual user limitation was as easy as removing the old temporary tables and replacing them by real #temp tables. Now each user has his (or her) own temp tables that won’t interfere with anyone else's as the previous incarnation of temp tables did.

Once I populated the #temp I opened a record set using a query to get the pertinent data from the #temp table. I then passed the record set to the report as described above.

Once I was able to point the RPT file to the new server, know how to specify connection data to the report, pass record sets correctly and eliminate the physical temp tables everything worked as expected.

I hope this information is useful to some one at some future time. SGarV