setDataSource in Crystal Report form via code

[PatMcLaughlin]

We have a crystal report form that is printed from time to time and called from VFP. When it was created, it used an XML created (Call it 'ABC.xml'). Now they want to change the name of the xml on each form printed... (Call it ABC123.xml) The actual xml has not changed, just the name. In VB.Net, we do this all the time with a FormName.SetDataSource(ABC123). I have had no luck doing the same thing in VFP. Any suggestions? The following code works as long as we use 'ABC.xml' but nothing in it passes the new xml to Crystal when we change the name:

		   loCrystal=CreateObject("CrystalRuntime.Application")
loReport=loCrystal.OpenReport(gcCrystalFiles + lcReport + ".rpt",1)
loReport.PaperOrientation = 2
loReport.DisplayProgressDialog = .F.
loReport.ReportTitle = (tcReportName)  
loReport.PaperSize = lnTray
IF lnDuplex1 > 1 Then
	loReport.PrinterDuplex = lnDuplex1
ENDIF
loReport.PrintOut(.F., INT((tnCopies)))

 

[Olaf Doschke]

I never ever used crystal reports, so I can only give general hints.

To convert TO unicode would rather mean Strconv(text,5) DBCS equals ANSI codepages in general, as unusual as it seems.
But as you tried several things I wouldn't bark up that tree.

The code VFP vs .NET looks very different, it seems you're using different things here, do you? If so, what?
VFP can use OLE classes (COM Servers) and .NET can use that or assemblies.
So - What are you really using here at each side? If you're using COM Server vs Assembly you can expect to need totally different code.

Referring to earlier answers:
You said you had working VFP code, just with the old XML, is that the same code as here?

About .NET:
What is CustStudyRptDS1 in the .NET code? I would like to see more complete code, eg code I could execute, if I had crystal reports. No variables used before defined and set, for example. This just leads to more guessing. Not knowing whether CustStudyRptDS1 is a file name or not, another guess is, that you would need to pass in the XML and not just the file name of the XML file.

How about doing the code in the debuggers (both VFP and .NET) to see more details?
And last not least: How about refering to any help files you have about crystal reports?
You typically don't guess code or adapt something you find somwhere (for another version of Crystal). You bought the product, didn't you? It doesn't come with any manual?

Bye, Olaf.

 

[PatMcLaughlin]

Yes we have purchased several versions of Crystal Reports. We are forced to use CrystalReports 9 with VFP as later versions are not compatible. We are using CrystalReports2013 with VS.net. Both are OLE. CustStudyRptDS1 is a Dataset that is created with code from numerous data tables. It was initially converted to an xml and used to create the initial form. Now it is created and populated by the program and passed (via the SetDataSource) to Crystal when creating a new report. The new data is populated on to the Crystal Form (in this case it was named crCustStudyRpt1) and printed, viewed, or emailed as requested.

Crystal Reports for the most part is a download from SAP. They rely on you getting your instruction/guidance from their website (good luck with that) or from classes given by 3rd party people (usually in California). Their help files are usable and mostly understandable for creating the report form but finding how to use it with other programs is slim and none. To answer your question directly - No-No Manuals

 

[Mike Lewis]

We are forced to use CrystalReports 9 with VFP as later versions are not compatible. We are using CrystalReports2013 with VS.net.

Could that be the key to the problem? You said you can access the XML from VB.Net, by using [tt]FormName.SetDataSource(ABC123)[/tt], but you also said that that doesn't work with VFP. But you are using different versions of CR with .NET and VFP. Is it possible that CR 9.0 supports XML in a different way to CR 2013.

Based on my reading of the CR 9.0 help file, I see that it does support XML as a data source, but only through an ODBC driver. Is that what you are using? Also, the driver has a number of limitations which are documented in the Help file. Have you checked those?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

If all else fails, have you considered loading the XML file into a VFP table, and using that as the data source for the report (which definitely does work)?

I know that's a bit drastic. It would be more satisfactory to find out why you can't get the XML data source to work. But if you are desperate to get the report finished, it might be the least worst option.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[PatMcLaughlin]

Hi Mike,
The versions have progressed without change (us starting at 8.0) as far as .net. As I said before, we are now at 2013. VFP however cannot use any version over 9 due to how it creates and uses the files. (above my pay grade) The VS.net however will work with a CrystalReport9 file in exactly the same way as with the new version. Some features may be lost on how it presents the data on the report may be lost (graphs, meters etc. are not supported) ODBC - Yes. Also it will work fine if the xml is named W:\ABC. It does not if renamed to W:\ABC123. Even if the two are identical and in the identical location.

By the way... You and Olaf should pat yourselves on the back! I have placed this question on 3 of SAP's Crystal Reports Forums (before placing it here) and have yet to get a response! You guys are great!

 

[Olaf Doschke]

If the versions are different, I can imagine it has to do with that.

I can't imagine the VFP code works with ABC.xml and not with ABC123.xml just because of the digits in the file name. The reason it doesn't work with 123 will most likely have to do with the xml structure, the content of the file.

Since you say crystal says it doesn't find the file, has it perhaps to do with exclusive access? If you generate a dataset with .NET it's creating a table with VFP, at first you have exclusive access, until you close and enable other sessions to read the file.

And if it really fails on digits in the file name, what is easier than copying the file and specifying the name of the copy?

Bye, Olaf.

 

[PatMcLaughlin]

Good Morning Olaf...
I just finished testing Versions of Crystal in .net and as I stated earlier, it worked without error in .net on both. W:\ABC.xml was the name used when the form was created. 'SetDataSource' is supposed to change the from W:\ABC.xml to a new name (W:\ABC123.xml). The absolute ONLY difference between W:\ABC.xml and W:ABC123.xml is the name of the xml. As for exclusive use of the file, maybe you hit on it. I didn't consider that VFP would lock an xml file like it does a table.

 

[Olaf Doschke]

>I didn't consider that VFP would lock an xml file like it does a table.
If, then .NET will lock the file. As you say it's a Dataset XML, it comes from .NET, not from VFP.

Bye, Olaf.

 

[Mike Lewis]

Pat, earlier Olaf suggested that you try changing the name of the file from ABC123.xml back to ABC.xml. Did you try that? Doing that would at least eliminate the possibility that the filename itself is in any way to blame.

Also - and this is a real long-shot - I wonder if the name is case-sensitive. I think that's very unlikely, but don't rule it out until you have tested for it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, the name is not necessarily ABC.xml, you could also try XYZ.xml, anything not containing digits, if that would matter.

But now you're again writing W:ABC123.xml instead of W:\ABC123.xml - the backslash has an importance in Windows file names, it's not optional.

Bye, Olaf.

 

[PatMcLaughlin]

Good Morning Mike,
Change the name back... yes I did. Worked fine. I also recreated the form using ABC123 then tried to use ABC. Same problem. Over night I got a response back from SAP stating that since VFP is out of production etc. they no longer support it.

 

[PatMcLaughlin]

Sorry Olaf... Just a typo, the code has the '\'

 

[Olaf Doschke]

>Change the name back... yes I did. Worked fine.

OK, then you have a solution, don't you? Just name the ouptut XML to ABC.xml instead of ABC123.xml
Wouldn't that work?

Bye, Olaf.

 

[PatMcLaughlin]

Yes that will (and does) work. It is the users request to be able to name them differently for where they actually get the data. I have recommended that they put a memo field in their file with that information and they can search for that. I unfortunately told them "We do that all the time in .Net, it should be no problem". Now I am eating my words.

 

[Olaf Doschke]

Well, you can always explain what you thought works doesn't.

Anyway, my current suspicion is, your rpt crystal report files are not taking any other data source and the SetDataSource doesn't really work out, maybe you're setting on the wrong level or wrong instance.
Some rpt is working on ABC.xml and another on ABC123.xml and to switch to other files you (or the users) have to do that in the crystal report instead of you in .NET or VFP code.

Bye, Olaf.

 

[Mike Lewis]

Pat, you can keep your users happy and still solve the problem. Within your VFP code, copy ABC123.xml to ABC.xml, then run the report, then delete ABC.xml. Your users won't have to know you've done that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>Within your VFP code, copy ABC123.xml to ABC.xml, then run the report, then delete ABC.xml.
Which is what I said in other words:
>what is easier than copying the file and specifying the name of the copy?

If you really lack the VFP command to rename a file it's RENAME, eg RENAME W:\ABC123.xml TO W:\ABC.xml
The only reason you can't do so is, both files exist, then first remane ABC.xml to TEMP.xml.

Bye, Olaf.

 

[PatMcLaughlin]

I know where you are both coming from and I agree that this would allow the Crystal report to be created. What you suggest gets murkier when you learn that this one process is used on at least 150 different reports. All created with different xml files. The xml filename and report name are passed to the process when it is called. So ABC.xml would only be valid for this 1 report. It gets worse when other variations are added. At this point, I have informed the users that the xml name is sacred once the report is created and must be used. It has come down to the old saying that "If it ain't broke... Don't Fix It!" If they want to differentiate among data sources, they will need to add a memo line in the trigger file (which they can do very simply).

 

[TamarGranor]

I've never used Crystal, but it makes no sense to me that you're applying STRCONV() to the filename you're passing.

Tamar