OT: Reports that bind to BOs????

Dog Ears replied on Saturday, August 12, 2006

It just so happens that I was forced to bind crystal reports to a Object graph, in the past i've just inssisted that objects are saved before printing but this is a bit limiting so I hacked togehter this solution and it seems to work.

I have a generic functino that uses CR10 and a report viewer control to display my reports [the reprots are created using field definition files (.TTX)] eg.

PrintReport(data, "Resources\C1.rpt", Handle)

I then pass the "data" as a DataSet created like this from a public method on the BO, the objects all accept a DataSet so that tables can be recursively added to the data set so the table order matches that in the report to accomodate multiple table reports and subreports.

EG.

public void GetData(DataSet data)

{
   DataTable _dt = new DataTable();
   _dt.Columns.Add("C2id", typeof(int));
   _dt.Columns.Add("Description", typeof(string));
   _dt.Columns.Add("Details", typeof(string));
   _dt.Columns.Add("Size", typeof(int));
   _dt.Columns.Add("HaKm", typeof(int));
....
   //Add Data Row
   _dt.LoadDataRow(new object[] {this.QuoteId, this.Description, this.Verbose, this.Size, this.HaKm, ....}, true);
   data.Tables.Add(_dt);

   this.Sites.GetData(data);
}

Maybe this would help,

Regards,

Graeme.

DansDreams replied on Tuesday, August 15, 2006

I use Xtra not Active. But when I evaluated the two of them together AR was even more primitive than Xtra - to utterly ridiculous levels IMO. You actually had to write code to get a "Page x of y" to display on a page. I've really got more important things to do with my time than do a report writer's work for it.

The visual designer is plagued with the same problems the VS forms designer has with it occasionally choking on complex layouts using a BindingSource. The designer basically somehow gets all twisted into knots and can no longer instantiate objects correctly and decides to just display a message that it can't load the design.

That and numerous nuisance issues like grabbing a label and trying to move it over a couple of pixels and instead ending up with a copy. Coming to understand quirks like if you set the font properties for a report control using the property grid you're using Microsoft's property editor and it's quirks (like changing 10 to 9.75 for certain fonts) but if you use the Xtra menu it doesn't behave that way - so you end up with inconsistent settings.

It's interesting you mention the Crystal designer, because one of the reasons I'm considering going back to it is because I always loved the designer. That was back in the VB6 days, mind you. Could you be even a little more specific?

What do you mean about having the reports in the same assembly? Doesn't it still use the concept of the report defenition file (.rpt)? When I used it before I basically had just a generic report loader that looked in a specified folder for those files, read the report name etc. with the DOM and put them in a selection control on the parameters form. What part would go in the same assembly? Do you mean the viewer control, etc?

tetranz replied on Thursday, August 17, 2006

Since last week I've been meaning to reply to this.

ward0093:

O.K.... fed up with Crystal Reports!
Does anyone have a good recommendation for a reporting package that can be bound to a Business Object Collection (CSLA Collections)?
I can not seem to get Crystal Reports to bind to that

I just completed a WinForms project with a Crystal Report driven from objects using CSLA.Data.ObjectAdapter and once I was over the quirks, it wasn't too bad. I'll probably use it again. I've used Crystal somewhat reluctantly since early VB days and its always been a bit of an "adventure". They seem to have the uncanny knack of doing very well for 95% of the way and then frustrating the hell out of everyone for the last few meters of the journey.

The key is that you really need to go for the ObjectAdapter and DataSet unless its a very simple report. I have a colleague who is a bit of a Crystal Expert but not a programmer so his reports are all direct db table driven often using what seems to me to be painfully convoluted grouping, oddball functions written in Crystal language and other obscure ways of figuring out when to hide a section or a subreport or not etc. He doesn't want to use SSRS. I integrated his shipping manifest report into my CSLA app. It worked okay but I needed to enhance it a bit so I decided to go for objects. Here's my experience.

I needed to build the report from several "tables" or collections. As you know, the Crystal ReportClass can take an IEnumerable or a DataSet. Great, I thought. First test was to try a single object. I created an array of one so that it was IEnumerable. That worked. Looking good Smile [:)] . Now lets add some more objects or collections. Umm ... no Sad [:(] . I think you can only add one IEnumerable collection. Okay, so lets create a single root object with multiple child collections. No again. In the Crystal designer, you can't see the child collections.

Late at night, under some pressure now, working remotely from home and without the book. Lets look at the ObjectAdapter. I'm relieved to find that its very easy to use, thanks Rocky. Create a DataSet and then use ObjectAdapter to create and fill DataTables with my object data. Umm ... but what to do now? Someone correct me if I'm wrong but to design the report, I think the Crystal designer needs a typed DataSet. So ... lets have a few lines of code that creates a generic DataSet and does a WriteXmlSchema() out to an xsd file. Open that in Visual Studio and it looks like a typed DataSet but its not. Its just a schema. I couldn't find built-in way of creating a typed DataSet from the xsd so I added a DataSet to my project and then copied and pasted the tables across. Now in Crystal I can point to the DataSet and see all tables, link them etc. I need to update that DataSet if my objects change.

My data is a ReadOnlyBase CSLA object with properties that are several ILists of simple "info" type objects plus one property that is a single object which has the root properties. DataPortal_Fetch calls one multi-resultset stored procedure to load the properties and collections. No more nonsense of returning header records umpteen times and taking the first just because there is a table join to a child table. At runtime I use ObjectAdaptor to fill the multiple DataTables in the typed DataSet. It needs to be flat. You don't want a hierarchy resulting in DataTables containing child tables. Crystal doesn't seem to handle that. My objects have some nice clean boolean properties with logic to determine whether or not to show subreports etc replacing all the previous Crystal logic. All this works well for "document" type reports. Obviously memory and perhaps performance would be an issue for seriously long real reports.

ObjectAdapter makes all properties type String when its creating new DataTable columns which means that you can't use Crystal's number formatting stuff unless you then edit the type in the typed DataSet. I didn't try that because it didn't matter in this report but I think ObjectAdaptor could be modified to examine the datatype of the property and create a column of appropriate type.

Just when you think you're done .... Crystal always has one more challenge ... A frustration with the WinForms viewer is that there seems to be no way to set the default printer if the user hits the toolbar print button. It always comes up as the Windows default. I think it can be set at design time and saved with the rpt (embedded) file but that's obviously not good. You can hide the print icon and make your own button which onclick creates your own System.Windows.Forms.PrintDialog() and then sets PrintOptions and calls PrintToPrinter() on the reportSource. Well ... you can't really add your own button to the toolbar but you can put one on top of the toolbar to give the illusion of it being integrated.

Cheers
Ross

bobhagan replied on Saturday, August 19, 2006

I can pretty much confirm Ross' experience. I'm working on a project to convert a Win/CS to the Web, (asp and .Net1.1) and had a large library of Crystal 8.0 reports that were still usable.

There are two ways to use Crystal here: whats called "PULL" in which Crystal manages connections relations, parameters and pulls the data from the source. "PUSH" creates the source outside and sends the data to the report. The most helpful thing out there is Brian Bishof's book, available on Amazon. The best sources of info on the web are the Cyrstal forums at ASPAlliance (articles), ASP.NET (questions), and CodeProject (articles, but hard to find).

In push, you create a datatable in a dataset and bind the report to that (You don't seem to be able to put in multiple tables (at least in 2003) You must define a typed dataset in the VS dataset builder, because this is what's getting read as the datasource. Find it in the CR datasources/newds/other/... Rocky's Object Adapter then, is a way to fill the dataset. (I'm runing a query directly, and passing it to a dataset, because the "programmers" in my shop think csla is too complicated. I'm a social worker; what would I know)

I have no experience on CR 2005 except to say that mypull reports all run)

Here's an article
http://www.codeproject.com/useritems/CrystalWin.asp that has export code. as well as a demo of how to read the Crystal param collection (mostly for pull)

HTH
Bob Hagan

tetranz replied on Thursday, December 21, 2006

dscannell:
I would be very interested if you had some sample code explaining your Crystal Reports / ObjectAdapter solution

Here's the general technique I'm using quite successfully with Crystal reports. Its all quite simple stuff really. Sorry if I'm repeating some of my post back in August. I've been revisiting this recently and have tidied things up a bit.

I have a "Reports" project in VS containing the following code for multiple reports. the resulting DLL is used in several web and Windows apps. All the files for one report / document is in its own folder / namespace so I can use generic names for things.

Lets say you want a Crystal report for an order. It needs an Order header and a collection of OrderItems.

Create simple classes for OrderHeader and OrderItem. They don't have to be CSLA objects. I usually have read-only properties and a constructor that accepts and sets all the properties.

Create one CSLA object derived from ReadOnlyBase. I name that class Data. It has two main business properties. OrderHeader who's type is my OrderHeader class and OrderItems who's type is List<OrderItem>. A simple list works fine for the collection, it doesn't have to be a CSLA collection. In the usual CSLA way, DataPortal_Fetch in Data does whatever you need to do to populate the OrderHeader and List<OrderItems>. I usually call a stored procedure that returns two results, one for the header and one for the items. You could use embedded SQL or whatever. Of course there is nothing that says it even has to involve a database.

The factory method in Data is simply this:

public static Data GetData(int id)
{
    return DataPortal.Fetch<Data>(new Criteria(id));
}

id is the unique id of the order which DataPortal_Fetch gets via a criteria object in the normal CSLA way.

Data also has a method like this:

public static void FillDataSet(DataSet ds, int id)
{
    Data data = Data.GetData(id);
    ObjectAdapter oa = new ObjectAdapter();

    oa.Fill(ds, "OrderHeader", data.OrderHeader);
    oa.Fill(ds, "OrderItems", data.OrderItems);
}

Note that this accepts a generic System.Data.DataSet

I have a Windows project that is really just for test and dev. Its still kind of rough but it works. It has code behind a "Build DataSet" button like this:

using System.Data;
using System.IO;

DataSet ds = new DataSet("DSet");
Data.FillDataSet(ds, 12345);            // 12345 is just an order id to get some data

string file = "Path to report folder described above" \ DSet.xsd";

if (File.Exists(file))
{
    File.Delete(file);
}
ds.WriteXmlSchema(file);

I run that once. I then tell VS to include the resulting DSet.xsd file into the report project. VS automatically generates DSet.Designer.cs, DSet.xsc and DSet.xss therefore turning the schema into a DataSet. Thanks Sukhorukov, that didn't quite work for me the first time. I usually open the dataset in VS and create any required relationships between the DataTables. Note that the ObjectAdaptor converts everything to strings. I think someone published a enhancement on this forum a while back. It hasn't really been an issue for me but it could be if you're formatting numbers. You can easily right justfy but if you want commas to separate the thousands etc then it might be an issue.

Now ... back to the Data class. Add this factory method:

public static DSet GetDataSet(int id)
{
    DSet ds = new DSet();
    FillDataSet(ds, id);
    return ds;
}

Now you can create you Crystal Report in the same folder. Your dataset will be listed under Project Data / ADO.NET DataSets. Select them and its just like working with database tables.

To view the report in either a Windows or web form, drop on a CrystalReportViewer and do this in the form load event. You'll need using statements etc to make sure you're pointing to the right Data, DSet and Report

DSet ds = Data.GetDataSet(orderId);
Report rpt = new Report();
rpt.SetDataSource(ds);
crystalReportViewer1.ReportSource = rpt;

That's basically it. The cool thing and sort of the whole point of doing this rather than the standard "Crystal accessing the db" thing is that I'm really only using Crystal is a means of display. I can do any massaging of the data in my code. Crystal only sees the resulting properties. This includes making boolean properties to show or hide sections of the report etc. I find that MUCH cleaner and more flexible than messing about with the klugy Crystal or Basic syntax functions inside Crystal.

Of course, when you change your data classes, you need to rebuild the dataset. Run the DSet generate code again, exclude the dataset from the project, delete the other VS generated files and then include the DSet.xsd again. That will rebuild the other files. In the report designer you then need to do a "verify database" to recognise the changes. It could probably be automated better but this works for me.

Note that even if you are only producing one document, eg there is only one order header and the items can only belong to that order, you still need a one to many relationship between the header and items otherwise Crystal will give you a warning saying that it "can't find the starting point". For some reports where the source of the data has been somewhat unstructured, I've created a meaningless id in the header and a HeaderId property in the items always with a value of 1 to satisfy this.

Note that I've only used this for "document" type reports. I'm not sure how well it would work for long many page reports. There is obviously an inefficiency in loading into objects, then into the dataset and then into Crystal.

Despite its somewhat frustrating history, I have to say that the "free" Crystal Reports in VS 2005 is working very well for me. Its very cool to be able to use the same report in both a Windows and web app or to generate a PDF. I know some people disagree but I find that the designer is pretty good and DLL hell seems to be a thing of the past with the Crystal Frameworks for .NET that you can install with ClickOnce. I'm using the free VS means of doing the web reports on a low traffic app despite the warnings that it is only intended for test and small applications. I'm not sure how far we can go before we'll need to think about buying the server product but so far so good. The web apps produce quite nice html that works perfectly in both Internet Explorer and FireFox. One reason that we're not using SQL Reporting is that just produces an embarrassing mess in FireFox. BTW, I have recently seen a blog with a CSS hack that is said to fix much that problem in SSRS but I haven't tried it.

Happy holidays everyone, especially to you Rocky for all your hard work and enormous contribution to the .NET community. CSLA is just getting better and better.

Cheers
Ross

As a Kiwi in USA, Christmas in winter just doesn't feel right Smile [:)]

NightOwl888 replied on Sunday, July 27, 2008

Wow, I can hardly believe someone is praising Crystal. I just want to be sure I am reading this right!

I have always had issues with Crystal, although I must say they do back their product with good support. To be honest, I never tried the "push" model. I was trying to find a way to get Crystal to work on a 3 physical tier environment, but the "push" model you describe here wasn't an option that I explored.

I have a couple of Crystal reports in my CSLA application, and I was thinking about changing to Active Reports before I read this post. Now I am starting to lean the other way - after all, Crystal does have a better report designer tool that saves a lot of time, and if it can operate in a physical 3-tier environment with no-touch deployment, why not use it?

Before I make my final decision though, I need to confirm some things. If you are not the original poster and you know the answer, feel free to chime in.

Since you posted this code sample, VS 2008 has been released and so has a new version of Crystal. I just wanted to confirm that the no-touch deployment still works in Visual Studio 2008 for Crystal. I doubt they would remove something so valuable, but you never know.
You mentioned that in order to do this you have to build the report into the application (an exe or dll) which is something I have always tried to avoid because I liked the portability of the rpt file format. Is there a way to do this using an rpt file?
I primarily avoided building the report into the application because the instant preview feature of Crystal Reports wasn't available in the Visual Studio designer (to my knowledge), and compiling and starting an application over and over took a lot of time when developing detailed reports. Is there a way to preview the reports when integrating them this way without doing the compile?
The version that shipped with VS 2005 wasn't technically "free" as you put it. You could basically develop anything you wanted, but it requires a licesnse key to deploy otherwise it won't function. Just out of curiousity, how did you work around that detail?

Thanks in advance for your assistance.

-NightOwl888

tetranz replied on Monday, July 28, 2008

We're certainly resurrecting an old thread here but ...

NightOwl888:

Since you posted this code sample, VS 2008 has been released and so has a new version of Crystal. I just wanted to confirm that the no-touch deployment still works in Visual Studio 2008 for Crystal. I doubt they would remove something so valuable, but you never know.

Crystal reports is still there in VS2008. I don't think it's changed. I haven't created any new reports but I have recompiled and deployed my existing apps without problem. When you "Add new item" to a project now you need to select Reporting before you see Crystal Report.

NightOwl888:

You mentioned that in order to do this you have to build the report into the application (an exe or dll) which is something I have always tried to avoid because I liked the portability of the rpt file format. Is there a way to do this using an rpt file.

The rpt file has a "Build Action" property in VS. I've never changed it from Embedded Resource" but I'm sure you can change it and stay with a separate file. It has several other allowed values. I don't know what they all mean. You might also have to change the "Copy to Output Directory" property.

NightOwl888:

I primarily avoided building the report into the application because the instant preview feature of Crystal Reports wasn't available in the Visual Studio designer (to my knowledge), and compiling and starting an application over and over took a lot of time when developing detailed reports. Is there a way to preview the reports when integrating them this way without doing the compile?

Sort of. The report viewer displays sample data that it dreams up itself. You can edit the rpt file and then go back to the form with the viewer and the layout follows your changes all at design time.

NightOwl888:

The version that shipped with VS 2005 wasn't technically "free" as you put it. You could basically develop anything you wanted, but it requires a licesnse key to deploy otherwise it won't function. Just out of curiousity, how did you work around that detail?

Are you sure about that? I never had to do anything to make it work when deployed and don't think we've done anything bad in terms of working around anyone's license. I'm pretty sure that anything you do with VS out of the box is royalty free distribution. I assume Microsoft pay Crystal something to bundle it and Crystal are hoping that you're going buy the full product or their server products.

NightOwl888 replied on Thursday, July 31, 2008

tetranz:
We're certainly resurrecting an old thread here but ...

NightOwl888:

The version that shipped with VS 2005 wasn't technically "free" as you put it. You could basically develop anything you wanted, but it requires a licesnse key to deploy otherwise it won't function. Just out of curiousity, how did you work around that detail?

Are you sure about that? I never had to do anything to make it work when deployed and don't think we've done anything bad in terms of working around anyone's license. I'm pretty sure that anything you do with VS out of the box is royalty free distribution. I assume Microsoft pay Crystal something to bundle it and Crystal are hoping that you're going buy the full product or their server products.

Sorry I didn't respond sooner. I forgot to subscribe to this post. Not sure why that is not the default setting...

Actually, I just started experimenting with this again. I was able to create a seperate application with a simple embedded crystal report and load it on another machine with click once with no problem.

Going over the license terms, they basically say you can deploy an unlimited number of reports as long as they are all done "in conjunction with VS 2005". Maybe the problem I am having is trying to use the seperate .rpt file or something.

But then again, I am trying to deploy my Crystal Reports into a Windows service, so there may be Windows permission issues instead of deployment issues. Truth be told, I didn't spend much time to try to find a solution - I have just been running the Windows service on my development machine because it runs fine there. But deploying the service to another machine has now become a priority, so I will try to work through the issues.

NightOwl888 replied on Saturday, August 16, 2008

tetranz,

I just finished my....well, second report and had some questions and feedback (not just for you but for the forum in general).

The approach I used to take was to build a report based directly off of a SQL Server 2005 stored procedure. This had some advantages, notably better performance, easier grouping of data, instant preview of the report with data, and the ability to include additional "look up" data just by adding a JOIN. However, it came with a couple of significant drawbacks - namely the application the report was integrated into needed direct access to the database server (and knowledge of the database username and password) and if you wanted to apply additional business logic to the data, you either had to put that logic directly into the database, the report, or as with the approach I took, duplicate the post-processed data into another database table.

So now on to the "push" model using CSLA and the ObjectAdapter using the approach that tetranz outlined above. First of all the pros and cons to this approach...

Pros:

The business logic for the application is all in .NET objects. I had some complex pre-processing for my report (namely, some grandchild objects needed to be converted to child objects). This was more logic than I cared to put into the database, and it was difficult to maintain using my former approach. The approach I took was to store 2 copies of the data in the database (one pre and one post processing). While storing two copies of the same data is something to generally avoid in database design, since all of my business logic dealing with that data was in CSLA it wasn't too bad. However, I much prefer putting the pre-processing directly in line with the report (as it is now) without having to persist 2 copies of the same data. Putting the business logic within CSLA (or other objects) would arguably be worth the trouble even if the list of "Cons" were twice as long as it is below.
Some of the display logic could be removed from the report itself and put into standard properties and methods of an object. One could probably make a case against this if you consider the page generation of the report to be another UI and anything having to do with display should be in the report itself. However, I found this to be easier and more flexible than Crystal could provide alone.
Logic that a normal Crystal report couldn't possibly do could be added, such as knowing what part of a series of mini reports the current report is without having to print out each of those mini reports. For example, my packing slip report is designed to go to more than one vendor. At the bottom of the report, I have "Packing Slip N of M" displayed. If a filter was applied to only print the second packing slip in a series of 3, the report will still display "Packing Slip 2 of 3" even though Crystal is not aware of the other pages.

Cons:

While this might just turn out to be an issue that I couldn't find a workaround for, the default behavior of connecting to a dataset with mutiple tables isn't the same as connecting to a single output of a stored procedure. Specifically, I couldn't find a way to left join my child objects with my grandchild objects. Neither Crystal nor the ADO.NET dataset have a concept that is similar to a left join (well, the Crystal designer has this option, but I couldn't seem to get it to function the way I needed to). In some cases, my child data didn't require any grandchild data and in these cases Crystal simply ignored the child data altogether rather than displaying it without the grandchild data. The solution I came up with was to create "ghost" grandchild objects with the "Suppress" property set to true, which would mean that Crystal would automatically suppress the section with the ghost grandchild while still displaying the child.
This approach is much more time consuming than building a report from a stored procedure. It took about 3x as long to build essentially the same report I had created before (and before I didn't have a sample to work from).
There is no way to preview the report with the data in it without actually running the project. Part of the reason why this approach takes so much longer is because of all of the compilation time between changes and unit testing. The included preview feature works fine for static report objects, but anything that is dependent on business data to test requires you to run a test project. If there is some way to pre-load the "preview" function with your report's custom data that I may have missed, please let me know.
Crystal is sensitive to changes in the underlying data. It is preferable to create a layer of abstraction from the rest of your CSLA objects to allow them to change freely (similar to the approach that Rocky did for the Web Service UI). This means that you pretty much have to create a whole object model for every report if you want to make changes to your CSLA objects later. Granted, this object model can simply read the data from your central CSLA library, but it is definitely more work to build and maintain.
If you have your abstraction layer read data from your original CSLA business classes as I did, it is quite possible that you will need to make additional round trips to the database to retrieve "look up" data that was not in your original business objects.
Although SOME of the display logic can be put into your abstraction layer objects, this is not a complete solution. There are times when the object model is unaware of the operation required to implement the logic, for example with print state functions. In other cases, it is not very practical to put the business logic into the abstraction layer, such as when a display operation depends on both a parent object's value and a grandchild object's value. The end result is that the display logic may likely be divided between two different layers (the report and your custom object model) in two different formats.
As far as I could tell (and according to this forum: http://www.eggheadcafe.com/software/aspnet/31622564/change-namespace-of-typed.aspx) it is not possible to move a Crystal report or an ADO.NET typed dataset into a specific namespace unless you create a separate DLL for each report/dataset combination. Therefore, not everything will be tucked neatly away into its own namespace if you follow this approach. I tried renaming the file as in the last post, but it didn't properly put the dataset into the namespace.

Now, on to the questions...

How did you get all of your "DSet" objects and Crystal reports into their own namespaces (assuming that you did)?
Clearly you have been developing Crytsal reports with more than one "table" for longer than I have. Did you ever run into the issue with trying to enforce a left join between 2 of the tables as I described above (Con #1)? If so, did you find a workaround that was not a hack?

Thanks,

-NightOwl888

tetranz replied on Monday, August 18, 2008

I'm looking at my project now. It's been quite a while since I've touched it. I think I follow most of what you're saying.

NightOwl888:

How did you get all of your "DSet" objects and Crystal reports into their own namespaces (assuming that you did)?

I didn't. My dataset and report are in the same namespace which is really a subfolder / namespace of a general "Reports" namespace. It's a pity you have to do that but it didn't bother me much in my case because it's all related to the report. My read-only objects that load the dataset via the object adapter are also in that namespace but they wouldn't have to be.

NightOwl888:

Clearly you have been developing Crystal reports with more than one "table" for longer than I have. Did you ever run into the issue with trying to enforce a left join between 2 of the tables as I described above (Con #1)? If so, did you find a workaround that was not a hack?

I didn't hit that problem but perhaps I'm not doing quite the same thing. I have a left join defined in the Crystal designer in one of my reports and it seems to be working okay. I'm not sure if it makes any difference if you defined the relationship in the dataset designer or not. If you do then Crystal recognizes it but if you don't then you can still create the relationship manually in the Crystal designer. I guess it's much the same situation with a direct db report as to whether or not you have foreign keys defined in the db.

NightOwl888 replied on Monday, August 18, 2008

tetranz:

NightOwl888:

How did you get all of your "DSet" objects and Crystal reports into their own namespaces (assuming that you did)?

I didn't. My dataset and report are in the same namespace which is really a subfolder / namespace of a general "Reports" namespace. It's a pity you have to do that but it didn't bother me much in my case because it's all related to the report. My read-only objects that load the dataset via the object adapter are also in that namespace but they wouldn't have to be.

To follow up, the way I did it was to use a naming convention for each of the objects ending in suffix "DataSet" or "Report" as appropriate. So for example, my PackingSlip report would have an object named PackingSlipReport.rpt and one named PackingSlipDataSet.xsd. It would definitely improve managability if they were in a namespace that corresponded to the folder they are in, but this is a close second.

Putting each report in a DLL is an option but that would increase compile time significantly, especially if you have several dozen reports.

tetranz:

I didn't hit that problem but perhaps I'm not doing quite the same thing. I have a left join defined in the Crystal designer in one of my reports and it seems to be working okay.

You are correct. The way you described it above, you won't ever run into this problem. The problem arises because I have a grandchild table as follows:

Order > OrderProduct > OrderProductAttribute

The issue is that Crystal doesn't seem to support the concept of 1 to 0* (including 0 rows) - the closest you can get is 1 to 1*(including 1 row, but excluding 0). So when I had a record in OrderProduct with no corresponding record in OrderProductAttribute, Crystal would just ignore the OrderProduct row.

In your case, it is very unlikely you would ever have an order without lineitems, and in that rare case you probably wouldn't want to be able to generate the report anyway.

tetranz:

I'm not sure if it makes any difference if you defined the relationship in the dataset designer or not. If you do then Crystal recognizes it but if you don't then you can still create the relationship manually in the Crystal designer.

It doesn't. I initially went down that path, but ended up removing the code that created the relationships between the data tables because Crystal depends on its own relationships in its "Link Tables" designer rather than the ones in the underlying dataset. Even without those relationships, Crystal recognizes and creates those links automatically if the PK and FK have the same name.

I put the FK fields directly in my CSLA abstraction layer, so along with the CSLA object from my application library, I pass the ID of the parent object and populate it too. Since my collections are read-only and can't be populated any other way, this was not hard to implement. I ran them through the ObjectAdapter and it created tables where the PK and FK matched in name. The end result is that Crystal created the links automatically when I added the tables to the report.

tetranz:

I guess it's much the same situation with a direct db report as to whether or not you have foreign keys defined in the db.

Yes and no. The way I did it before was to join all of the data in a SQL stored procedure so there was only one result set when it got to Crystal. Crystal still doesn't understand the concept of a null row, but at least that could be worked around using its internal functions.

Had I built a report based on more than one table or view before, I probably would have run into this issue sooner. The database (and DataSet) support 1 to 0* (including 0 rows), but Crystal doesn't seem to. If I am wrong about this, I would appreciate knowing what the solution is because the workaround I came up with was a bit of a hack.

Last but not least, I thought of a couple more items for the "Pros" section after considering how the new approach integrates with my environment.

Pros (continued)

Basing the report on a stored procedure required passing parameters for record selection to the report and relying on the report to pass them to the database. This was a bit flakey sometimes and Crystal would require a "verify database" even if the parameters and result set didn't change. Sometimes Crystal would choke completely and you would have to update the stored procedure so it wouldn't include any parameters and then do a "verify database" and then repeat the process, the second time adding the parameters back in. Building the report based on a dataset is much more flexible because any method imaginable could be used to populate the dataset and record selection is not routed through Crystal. Running "verify database" only needs to be done when changing the DataSet schema and in no other case.
I was having issues testing when running directly from a stored procedure because my test database is a named instance of SQL Server on the same physical machine as my production environment. When I tried to pass the new server information to Crystal (i.e. DATA\Test instead of DATA), Crystal wouldn't recognize the change and effectively I could not test without physically updating the Crystal Report (which defeated the purpose of testing somewhat). Since I am using ADO.NET to retrieve the data from the DB now, this is no longer an issue and my reports function in the test environment.

OT: Reports that bind to BOs????

ward0093 posted on Saturday, August 12, 2006

Dog Ears replied on Saturday, August 12, 2006

RockfordLhotka replied on Saturday, August 12, 2006

matt tag replied on Sunday, August 13, 2006

ward0093 replied on Sunday, August 13, 2006

matt tag replied on Monday, August 14, 2006

figuerres replied on Thursday, August 17, 2006

ward0093 replied on Sunday, August 13, 2006

DansDreams replied on Monday, August 14, 2006

ward0093 replied on Monday, August 14, 2006

DansDreams replied on Tuesday, August 15, 2006

Jav replied on Saturday, August 19, 2006

tetranz replied on Thursday, August 17, 2006

tetranz replied on Thursday, August 17, 2006

bobhagan replied on Saturday, August 19, 2006

sukhorukov replied on Tuesday, August 29, 2006

dscannell replied on Tuesday, December 19, 2006

tetranz replied on Thursday, December 21, 2006

ajj3085 replied on Thursday, December 21, 2006

NightOwl888 replied on Sunday, July 27, 2008

tetranz replied on Monday, July 28, 2008

NightOwl888 replied on Thursday, July 31, 2008

NightOwl888 replied on Saturday, August 16, 2008

tetranz replied on Sunday, August 17, 2008

tetranz replied on Monday, August 18, 2008

NightOwl888 replied on Monday, August 18, 2008