Sunday, February 11, 2007

Crystal Reports Madness (Formulas / Datasets)

"There must be a group that matches this field."

I recently was given the task at work to get a bunch of crystal reports working from a win32 application, that we inherited, to our new web app version. I had never done anything with Crystal Reports before so I wasn't sure what I was getting myself into.

Crystal Reports ended up being pretty straightforward and easy to figure out, but that wasn't the hard part. The current reports we had connected directly with the database to get the information they needed. We didn't really like this 'pull' model for a couple reasons.

1 - The current application only connected to Oracle. The new web application was not DB specific.

2 - Didn't really like the idea of having the credentials stored within each report file.

3 - Changing DataSets was a pain, and had caused problems with the report files themselves in the past.

So we decided to implement the 'push' modal.

(Push / Pull explained here...)

This seemed pretty straight forward. We created XSD's for each report, and then changed the datasource location in the report file using VS2005.

Well... This ended up not working It wasn't until I stumbled accross some very sparse information on the web explaining that it's best to disconnect the report from the DB and THEN change the DS location. This finally ended up working for us (Task 1 DONE!)

The next issue was that we changed some of the report paramater names to make more sense. I figured this wouldn't be much of a problem, but boy was I wrong. When I saved the report I received multiple complaints from the report. Most of which were due to some of the report formulas. I loaded up a few of these formulas and received this error when I tried to save them

"There must be a group that matches this field."

I searched the web trying to figure out what it wanted, but I mostly stumbled accross web sites only offering training, actual documentation was kinda sparse, or not really that great.

Basically the solution boils down to this.

Our formulas group options were being set dynamically in code behind, so this condition wasn't in the original formulas which caused the error. The weird thing is that they had worked before we changed the dataSets, now we had to set it (we set it to 'for each month') but it would then be changed when the report was run dynamically. hmmm

The 'Average' function follows these signatures

Average(field), or Average(field, condFld), or Average(field, condFld, cond), or Average(x).

The third param 'cond' had to be set to match it's corresponding groups grouping. Now if you go to the group on the report, right click, and go to 'group expert' you can view/change the grouping (Monthly, Annually, etc) but in the formula it's not so clear as what to use. I looked in the actual CR documentation and they actually refer to the wrong thing to use for this (Many peoples posts, blogs etc had complained about CR's documentation) I stumbled accross the correct value to use and the reports save fine now, and also work, even though it's just dummy data that is changed at run time >(

Oh well... I hope this may help some poor sould out there


Sweet Camden Lass said...

Well, I know *why* I'm getting the error now. I have no idea what to actually do about it in the recalcitrant formula.

I hate Crystal Reports.

Justin Wilson said...

Are you still have issues with the grouping in CR? I may be able to help

Anonymous said...

We are upgrading CR8 to Cr 11. while running the CR 8 report in Cr 11 I am getting an error "There must be a group that matches this field"

Any sugggestion Please !!!

Charles said...

The detailed issue at the top gave me the breakthrough I needed. Thanks.

When you change data sources, it drops all the report groupings (Report -> Group Expert. Before you can do certain calculations you need to re-define these groups at the report level. I hope this further helps any of you who are beating your keyboard against your head in frustration right now.

Josh Robinson said...

Crystal reports suck ass. It's always been buggy and poorly supported. Try to use something else if you handlers will allow ya.