Creating Cyrstal Report at Runtime With Data Objects

[Tyranno.Lex]

I am using Visual Studio .NET 2003 and have successfully deployed a
commercial web application written in C# and ASP.NET. I am now wanting
to add reporting using Crystal Reports and am having a devil of a time
doing this.

The problem is that my application uses an encapsulated data tier for
accessing the database vs. accessing it directly via the client tier.
However, EVERY example that I have ever seen for creating a Crystal
Report at runtime assumes that one is connecting directly to the
database from the web application code. This is, as we all know, a bad
design. For reasons varying from security to scalability to ease of
programming and support, the database should be exposed via a separate
object library.

Unfortunately, I cannot find a single example where this architecture
is used in conjunction with Crystal Reports. I did find some
information on MSDN where this is done with Visual Studio 2005...with
that version, examples are given where the report wizard can point
straight to the dll exposing the data tier. This is exactly what I
need - however, I have yet to succesfully port this application to
2005. In the meantime, can someone please give me a hint as how I
might dynamically create a report directly from a dataset that I have
created on the fly from the data objects?

Below is the code for the type of thing I am trying to do. A function
creates the dataset that is to be used (I have left that code off as it
is not important) and then another function initializes the report
using this dataset. The report comes up when the page is loaded, but
it is blank. This doesn't surprise me as I have not defined the
fields, etc. that need to be shown on the report (since VS 2003 doesn't
support designing a report while pointing to a data tier.)

How do I do this? TIA for any leads...I think all I need is a push in
the correct direction.

Regards,

T. Lex

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
try
{
LoadDataFromDatabase(false);
}
catch(Exception ex)
{
System.Diagnostics.Debug.Write("GetTopHundred: " + ex.Message);
}

ConfigureCrystalReports();
}

private void ConfigureCrystalReports()
{
string reportPath = Server.MapPath("CrystalReport1.rpt");

rankingDSet = (DataSet) Cache["AppData"];
rankingDView = new DataView(rankingDSet.Tables[0]);

rptStockRankings = new ReportDocument();
rptStockRankings.Load(reportPath);

rptStockRankings.SetDataSource(rankingDSet);

ReportViewer_Rankings.ReportSource=rptStockRankings;
ReportViewer_Rankings.DataBind();
}

 

[Bruce Wood]

You're very close to having what you need. In order to design the
report you just need to call WriteXmlSchema on your data set (I'm not
sure whether this works with data views or not). You may want to make
judicious use of the MappingType property of your DataColumns, and
create Relations between your tables that are "nested" so that the XML
comes out "nice".

Then in Crystal, just choose your data source as "Other data sources"
-> "ADO.NET" -> XML schema and point it at your schema file.

I am using Visual Studio .NET 2003 and have successfully deployed a
commercial web application written in C# and ASP.NET. I am now wanting
to add reporting using Crystal Reports and am having a devil of a time
doing this.

The problem is that my application uses an encapsulated data tier for
accessing the database vs. accessing it directly via the client tier.
However, EVERY example that I have ever seen for creating a Crystal
Report at runtime assumes that one is connecting directly to the
database from the web application code. This is, as we all know, a bad
design. For reasons varying from security to scalability to ease of
programming and support, the database should be exposed via a separate
object library.

Unfortunately, I cannot find a single example where this architecture
is used in conjunction with Crystal Reports. I did find some
information on MSDN where this is done with Visual Studio 2005...with
that version, examples are given where the report wizard can point
straight to the dll exposing the data tier. This is exactly what I
need - however, I have yet to succesfully port this application to
2005. In the meantime, can someone please give me a hint as how I
might dynamically create a report directly from a dataset that I have
created on the fly from the data objects?

Below is the code for the type of thing I am trying to do. A function
creates the dataset that is to be used (I have left that code off as it
is not important) and then another function initializes the report
using this dataset. The report comes up when the page is loaded, but
it is blank. This doesn't surprise me as I have not defined the
fields, etc. that need to be shown on the report (since VS 2003 doesn't
support designing a report while pointing to a data tier.)

How do I do this? TIA for any leads...I think all I need is a push in
the correct direction.

Regards,

T. Lex

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
try
{
LoadDataFromDatabase(false);
}
catch(Exception ex)
{
System.Diagnostics.Debug.Write("GetTopHundred: " + ex.Message);
}

ConfigureCrystalReports();
}

private void ConfigureCrystalReports()
{
string reportPath = Server.MapPath("CrystalReport1.rpt");

rankingDSet = (DataSet) Cache["AppData"];
rankingDView = new DataView(rankingDSet.Tables[0]);

rptStockRankings = new ReportDocument();
rptStockRankings.Load(reportPath);

rptStockRankings.SetDataSource(rankingDSet);

ReportViewer_Rankings.ReportSource=rptStockRankings;
ReportViewer_Rankings.DataBind();
}

 

[Tyranno.Lex]

Thanks for that, Bruce.

I had somehow just come up with that concept myself when I checked back
on usenet and saw your recommendation to do this very thing. I figured
it was a 'work-around' but that it would give me the schema that I need
to design the report.

I wasn't able to get the dataset to bind at runtime, however. Right
now, I am outputting (using WriteXML) the data in the dataset to the
same XML file that the report was pointed to at design time. That
actually works but I am pretty sure this IS a w/a and I worry about
performance. Also, I had to give the ASP.NET account write access to a
file in my web directory, something I don't really want to do.

Any other tips are appreciated. Thanks again for the quick reply. I
think I am okay for the short term as I can at least work on getting
the reports looking good.

T. Lex

 

[Bruce Wood]

I don't design from an XML file. Notice that I said WriteXmlSchema, not
WriteXml. Write a _schema_ and design from the _schema_, then set the
report source at run time to a DataSet with that same schema, and it
should work.

I don't design from an XML file for that very reason... I worry that
telling Crystal "where the data is" will cause it to keep looking
there.

The only other detail is that if you design from a schema then I think
that the schema needs to be available at runtime, too. At least, that's
the way our system works.