I've just gone thru the exercise of trying to use Crystal Reports with a custom dataset generated by a complex Oracle database query. I was helped greatly by Chuck Bradley's article at http://aspalliance.com/776. I tried to enter comments but wasn't successful, so here they are:
Many thanks, Mr. Bradley! I've just gotten this to work properly. It took me a couple of days though, as I spent time wading through the Microsoft and Business Objects tutorials and countless web articles. It seems that this is the only way to bind arbitrary dataset data to a Crystal Report. The report designer definitely seems to need an XML schema in order to be able to lay out the report.
I have a data access class that fills a datasource from a complex Oracle SQL query. This class creates the db connection, creates a command object and loads it with the query string, creates an OracleDataAdapter, then creates a dataset and uses adapter.fill to load it. Pretty standard stuff. I'm not explicitly creating any tables.
In this class, after the dataset is filled, I have a line of code to create an XML *Schema* file from my dataset:
ds.WriteXmlSchema("c:\etc...").
Of course I got the UnauthorizedAccessException as mentioned in your article, but after granting write priv to my machines' aspnet account, that went away.
I stepped thru this code so that the XMLSchema file was written then quit and went back to the report design as shown, and the designer showed the data fields from my query!
After designing the report, I went to the code-behind page of the page where I dragged the Crystal Report Viewer, and in the Partial Class I inserted the statement below at the top of the file:
Imports CrystalDecisions.CrystalReprorts.Engine
I then then inserted a Page_Init Sub with the following code:
Dim rptdoc as New ReportDocument
'
'important or you get invalid file path error - thanks clartsonly
'
rptdoc.Load(Server.MapPath("my.rpt"))
rptdoc.SetDataSource(myDataAccessClass.myGetDataset)
crv1.ReportSource = rptdoc 'my crystal reports viewer control is crv1
crv1.DataBind()
crv1.DisplayToolbar = True
When I ran this page, the report displayed properly.
Tuesday, January 23, 2007
Tuesday, January 2, 2007
A first look at Crystal Reports in VS.NET 2005
I found a tutorial here: http://msdn2.microsoft.com/en-us/library/ms227881(VS.80).aspx. I clicked on 'Setting Up the Development Environment' in the Releated Sections at the bottom of the page.
Clicking on 'System Setup', I visited the 'What Needs to be Installed?' link and 'Visual Studio Versions', I saw that CR should be installed as part of my VS2005 environment. Returning to 'What Needs to be Installed' and clicking on the 'Crystal Reports Versions', I verified that the latest version of CrystalDecision.CrystalReports.Engine in c:\windows\assembly is 10.2.3600 (file version 10.2.51014.0)(I also have the 9.1.50000.0 file from VS 2003).
Backing up two pages to 'System Setup' I clicked 'What Needs to be Verified?' and then 'Add New Item Dialog Box Includes Crystal Reports'. I do have 'Crystal Report' as one of the available items in the Project > Add New Item dialog. The tutorial says to look for "Crystal Reports", but I don't think that is significant.
Back a level, and verified that there is content in C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Reports\Feature Examples\
and C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Reports\General Business\.
Back a level, and verified that the C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\en\Code\TutorialSampleCodeProjects.msi exists.
Back a level to 'Viewers Virtual Directory'. Here I found my first departure from the standard configuration - In IIS, the default web site only had a CrystalReportFormView2 virtual directory. I went ahead and tried to create a new virtual directory pointing at the suggested path for the ASP.NET development server at c:\windows\\Microsoft.NET\Framework\v2.0.51014\ASP.NETClientFiles\CrystalReportWebFormViewer3 - but my framework version was .50727 instead of .51014 - I hope that does not present a problem.
If you want to use IIS for development the suggested path is: C:\Inetpub\wwwroot\aspnet_client\system_web\2_0_50526\CrystalReportWebFormViewer3 but my system has a 2_0_50727 folder - I'll try this if I run into problems above.
At the same level I verified that the Xtreme Sample Database was available at C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Databases\xtreme.mdb and that the ODBC entry 'Xtreme Sample Database 2005' exists and points to the Access database at: C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Database\xtreme.mdb
I did not set up an MSDE or Northwind Database Installation, or any 64 bit stuff.
Returning back to the 'Setting Up the Development Environment' page, I clicked the Project Setup link, and then the 'Visual Studio 2005 link on the next page, 'Web Site Setup' and then 'Creating a New Web Site'. I created a new ASP.NET web site with file system location, vb, and directory path c:\websites\CRTutorial.0
Clicking on the "Preparing the Web Form" link, we right-clicked the default.aspx node in solution explorer and chose 'View Code' There is no 'Code' option on the view menu as the example suggests. Added a Page_Init handler that calls ConfigureCrystalReports(); Added a shell function for ConfigureCrystalReports.
Continuing on with 'Adding a CrystalReportViewerControl', dragged a CrystalReportViewer control from ToolBox to default.aspx in design mode. I set it's ID property to myCrystalReportViewer (how nice and homey :-) I feel much more warm and fuzzy now. Maybe that's due to lunch? Anyway, the Imports statements were added as Imports CrystalDecisions.CrystalReports.Engine and Imports CrystalDecisions.Shared. Clicked on the 'Additional Setup Requirements'
Faced with a bewildering array of options, I'm going to choose the 'Add a Sample Report as a Non-embedded Report in a Visual Studio 2005 Web Site'. On that page, I'm choosing 'Binding the Non-embedded Report to the CrystalReportViewer Control'.
Following along with the instructions in this section, we are Directed to create a class-level declaration 'Private hierarchicalGroupingReport As ReportDocument'
and then add the following lines to the ConfigureCrystalreports() class:
Running the report in debug showed a rather eye-popping report! This is a good start!
But where do I go from here? Guess I'll click on Tutorials and Sample Code. I looked at the 'Sample Code' link and decided maybe I need to know a little more before I go there. Back to 'Tutorials and Sample Code', I clicked 'CrystalReportViewer Object Model Tutorials'. I tried to set up one of the non-embedded reports (customerReport) and got hung up with how to create a dataset that CR would understand. The samples said you should create an XML dataset, but I think it should work with a regular dataset. Before I get into the XML stuff, I want to take a look at the reduced code model.
The reduced code model is accessible from the Reduced-Code Tutorials in Visual Studio 2005 link on the first page of the tutorial. Starting with the Reduced-Code Web Site Setup with Crystal Reports Using Smart Tasks and following thru with the steps provided, I was able to set up a report in less than a minute! I'm very impressed!
I then tried to create my own dataset and create a report on it, but could not get CR to recognize the dataset. Hmm, let's look some more at the tutorials.
Found out how to do this - follow the Reduced-Code Secure Database Logon in a Web Site tutorial and it shows how to create a connection to the report. I used my Oracle WorkFlow database and was able to hack a quick report from it.
Clicking on 'System Setup', I visited the 'What Needs to be Installed?' link and 'Visual Studio Versions', I saw that CR should be installed as part of my VS2005 environment. Returning to 'What Needs to be Installed' and clicking on the 'Crystal Reports Versions', I verified that the latest version of CrystalDecision.CrystalReports.Engine in c:\windows\assembly is 10.2.3600 (file version 10.2.51014.0)(I also have the 9.1.50000.0 file from VS 2003).
Backing up two pages to 'System Setup' I clicked 'What Needs to be Verified?' and then 'Add New Item Dialog Box Includes Crystal Reports'. I do have 'Crystal Report' as one of the available items in the Project > Add New Item dialog. The tutorial says to look for "Crystal Reports", but I don't think that is significant.
Back a level, and verified that there is content in C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Reports\Feature Examples\
and C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Reports\General Business\.
Back a level, and verified that the C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\en\Code\TutorialSampleCodeProjects.msi exists.
Back a level to 'Viewers Virtual Directory'. Here I found my first departure from the standard configuration - In IIS, the default web site only had a CrystalReportFormView2 virtual directory. I went ahead and tried to create a new virtual directory pointing at the suggested path for the ASP.NET development server at c:\windows\\Microsoft.NET\Framework\v2.0.51014\ASP.NETClientFiles\CrystalReportWebFormViewer3 - but my framework version was .50727 instead of .51014 - I hope that does not present a problem.
If you want to use IIS for development the suggested path is: C:\Inetpub\wwwroot\aspnet_client\system_web\2_0_50526\CrystalReportWebFormViewer3 but my system has a 2_0_50727 folder - I'll try this if I run into problems above.
At the same level I verified that the Xtreme Sample Database was available at C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Databases\xtreme.mdb and that the ODBC entry 'Xtreme Sample Database 2005' exists and points to the Access database at: C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Database\xtreme.mdb
I did not set up an MSDE or Northwind Database Installation, or any 64 bit stuff.
Returning back to the 'Setting Up the Development Environment' page, I clicked the Project Setup link, and then the 'Visual Studio 2005 link on the next page, 'Web Site Setup' and then 'Creating a New Web Site'. I created a new ASP.NET web site with file system location, vb, and directory path c:\websites\CRTutorial.0
Clicking on the "Preparing the Web Form" link, we right-clicked the default.aspx node in solution explorer and chose 'View Code' There is no 'Code' option on the view menu as the example suggests. Added a Page_Init handler that calls ConfigureCrystalReports(); Added a shell function for ConfigureCrystalReports.
Continuing on with 'Adding a CrystalReportViewerControl', dragged a CrystalReportViewer control from ToolBox to default.aspx in design mode. I set it's ID property to myCrystalReportViewer (how nice and homey :-) I feel much more warm and fuzzy now. Maybe that's due to lunch? Anyway, the Imports statements were added as Imports CrystalDecisions.CrystalReports.Engine and Imports CrystalDecisions.Shared. Clicked on the 'Additional Setup Requirements'
Faced with a bewildering array of options, I'm going to choose the 'Add a Sample Report as a Non-embedded Report in a Visual Studio 2005 Web Site'. On that page, I'm choosing 'Binding the Non-embedded Report to the CrystalReportViewer Control'.
Following along with the instructions in this section, we are Directed to create a class-level declaration 'Private hierarchicalGroupingReport As ReportDocument'
and then add the following lines to the ConfigureCrystalreports() class:
Now I build the project and get an error "MyCrystalReportViewer is not defined". Going back to the design view on default.aspx, I see that the ID of the Viewer control was not changed as I had thought. When I tried to change it, stubbornly the name remained "CrystalReportViewer1". Ok, I'll go with the flow and change my code. When I changed the code and Rebuilt Solution, I didn't get any errors, but the code window gives me a squiggly blue underline with a tool tip "CrystalReportViewer1" not defined. I went back and changed the ID in design-view properties and it did change the code behind this time even though the property reset itself back to CrystalReportViewer1. I then went to view the HTML code and saw that the id of the CRViewer control hadn't been changed, so I force-changed it to myCrystalReportViewer and it took it, now showing correctly in design view and code behind and also building correctly.hierarchicalGroupingReport = New ReportDocument()
Visual Studio 8\ Crystal Reports\Samples\En\Reports\Feature Examples\
hierarchicalGroupingReport.Load("C:\Program Files\Microsoft
Hierarchical Grouping.rpt")
myCrystalReportViewer.ReportSource = hierarchicalGroupingReport
Running the report in debug showed a rather eye-popping report! This is a good start!
But where do I go from here? Guess I'll click on Tutorials and Sample Code. I looked at the 'Sample Code' link and decided maybe I need to know a little more before I go there. Back to 'Tutorials and Sample Code', I clicked 'CrystalReportViewer Object Model Tutorials'. I tried to set up one of the non-embedded reports (customerReport) and got hung up with how to create a dataset that CR would understand. The samples said you should create an XML dataset, but I think it should work with a regular dataset. Before I get into the XML stuff, I want to take a look at the reduced code model.
The reduced code model is accessible from the Reduced-Code Tutorials in Visual Studio 2005 link on the first page of the tutorial. Starting with the Reduced-Code Web Site Setup with Crystal Reports Using Smart Tasks and following thru with the steps provided, I was able to set up a report in less than a minute! I'm very impressed!
I then tried to create my own dataset and create a report on it, but could not get CR to recognize the dataset. Hmm, let's look some more at the tutorials.
Found out how to do this - follow the Reduced-Code Secure Database Logon in a Web Site tutorial and it shows how to create a connection to the report. I used my Oracle WorkFlow database and was able to hack a quick report from it.
Subscribe to:
Posts (Atom)