Crystal Reports - Reporting a custom dataset

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.

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:

hierarchicalGroupingReport = New ReportDocument()

hierarchicalGroupingReport.Load("C:\Program Files\Microsoft Visual Studio 8\ Crystal Reports\Samples\En\Reports\Feature Examples\
Hierarchical Grouping.rpt")

myCrystalReportViewer.ReportSource = hierarchicalGroupingReport

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.

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.

Calling an Oracle Stored Proc from .NET

Well, this has worked far better than I could have expected. First I went into the TableAdapter for the Work_Stats table and added a query.

Of course, not all is perfect. As you can see, the stored procedure radio buttons are grayed out for some reason in the Table Adapter Query Wizard dialog, but we can work around that by kludging in a simple select query and then fixing the properties later.
Here, I've changed the CommandType from 'Text' to 'StoredProcedure' and chosen the correct SP from the dropdown that appears when you click on the CommandText line. In addition, since I expect a return value, I changed the Execute Mode to scalar. The parameters were all auto-sensed for me, so I didn't have to change anything.

Sometimes my update queries don't work...

I added a query to the Work_StatsTableAdapter(TA) that did an update to Work Stats. The problem was that sometimes the update didn't work because the WHERE clause specified rows that didn't exist in the database. There was no signal back to the calling procedure that update didn't. I decided that we needed a number-of-rows counter passed back. The problem is I don't see a way to do this using Oracle. There's no rowcount that can be returned back immediately after a sql statement. You can use SQL%Rowcount, but that has to be used in a begin-end block and the TA query parser doesn't understand this construct. So I guess I'm stuck trying to implement a stored proc and calling it.

My stored proc on Oracle looks like this:

CREATE OR REPLACE PROCEDURE UPDATE_WORK_STATS(
P_COUNT IN WORK_STATS.COUNT%TYPE,
P_USER_ID IN WORK_STATS.USER_ID%TYPE.
P_WORK_DATE IN WORK_STATS.WORK_DATE%TYPE,
P_WORKTYPE IN WORK_STATS.WORKTYPE%TYPE,
P_COMM_MTHD IN WORK_STATS.COMM_MTHD%TYPE,
P_ROWCOUNT OUT NUMBER) AS
BEGIN
UPDATE WORK_STATS SET COUNT=P_COUNT
WHERE USER_ID = P_USER_ID
AND WORK_DATE = P_WORK_DATE
AND WORKTYPE = P_WORK_TYPE
AND COMM_MTHD = P_COMM_MTHD;
P_ROWCOUNT := SQL%ROWCOUNT;
COMMIT;
END UPDATE_WORK_STATS;

Now let's see if I can get VS to invoke this SP and give me back a rowcount.

I wish I'd started this blog earlier in the project. Now I'm going to have to reconstruct where I've gotten to so far.

I first started out creating a database for this project. I sketched out what we'd need for tables and data elements. I had our Oracle DBAs create me a database and I created the tables.

Next, I used Visual Studio .Net 2005 (VS) to create a strongly typed data access layer(DAL). As this was a simple system, I decided not to go with a separate business logic layer. I looked at the tutorial in MSDN for this and was overwhelmed by the arcane syntax and also by the problem with the parameterless constructor and problems with object datasource and strongly-typed datasets. I especially did not like the prospect of having to go and edit generated code.

The other reason for my decision is that I'm going to jump into this without a fully developed data model and expect to have to change things as I go along. I'm figuring this will be easier if there are fewer layers.

In creating the DAL for this project I am beset by some difficulties working with the Oracle database. I was unable to get some of my queries working as stored procedures.

The main query for the dataview grid takes data from my WORK_STATS table which looks like this:
USER_ID
WORK_DATE
WORKTYPE
COUNT
ENTRY_DATE

So there's one row per worktype per day. How are we going to display that in a grid that looks like this?

WORKTYPE	MON	TUE	WED	THU	FRI
Dollars	32	87	32	34	19
Donuts	322	827	342	314	139

The answer is to develop a pivot table query in Oracle. Thanks to help from Ask Tom,
I've come up with this little jewel:

SELECT WORKTYPE_DESC,
MAX(decode(WDAY,'MON',CNT,NULL)) MON,
MAX(decode(WDAY,'TUE',CNT,NULL)) TUE,
MAX(decode(WDAY,'WED',CNT,NULL)) WED,
MAX(decode(WDAY,'THU',CNT,NULL)) THU,
MAX(decode(WDAY,'FRI',CNT,NULL)) FRI,
MAX(WORKTYPE) WORKTYPE
FROM (SELECT WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY') WDAY,
SUM(COUNT) CNT, MAX(WORKTYPE) WORKTYPE
FROM WORK_STATS_VW
WHERE WORK_DATE BETWEEN 'FirstDate' AND 'LastDate'
GROUP BY WORKTYPE_DESC, TO_CHAR(WORK_DATE,'DY'))
GROUP BY WORKTYPE_DESC

This would definitely not work in VS as the add query parser
would not allow it. I created a separate class under AppCode and added a function which invoked this query against the Oracle db.

The website overview

The objective of this web site is to deliver a report of how many work tasks were completed each day in various work categories (worktypes). Data entry will be in a dataview grid set up such that users can enter a week's worth of data (5 entries, Monday thru Friday) at a time for each worktype that they are authorized to enter data for. The back-end database is Oracle 9i, but soon to be upgraded to 10g.

The travails of a web developer in the .Net 2005 environment

This blog details my journeys (the rat in the maze) in trying to set up my first .NET 2005 website. I wanted to write down all the techniques I considered so I'd have a record of what didn't work as well as what I finally decided to go with.