Crystal Reports - Grouping

Sheela iyer · August 2015

I am wondering if someone can help me. This is my first time with Crystal report although I am not new to databases and reporting.

I have created a query to generate a list of network partners within Canada. This is a straight forward query. Then I exported this as a access database file with all the relevant fields and the select records query.

When I went into Crystal report and laid out the fields to start with, it does list the right number of record rows however couple of fields and the column display blank. I checked my query and those are the fields I am using but for some reason when it is exported in to Cyrstal report these two field columns appaear blank. I have not applied formulas or grouping at this point.

Has any one confronted this sort of a situation or have any solutions for me ?

Alan French · August 2015

Can I ask which tables and fields you're exporting, and which ones are appearing blank? Is the entire column blank or only for particular records?

If it had been an older report you'd inherited I would have suggested checking to see if the fields were being suppressed, but as it's a new report then that's probably unlikely.

If you go into the Database Expert, are all your tables linked (and with the correct join type)? If you've got the right number of records then they probably are but no harm in checking.

Marie Stark · August 2015

Do you need it to be in Access format? It usually works best if you select Blackbaud Report Writer Database (MDB).

Sheela iyer · August 2015

I apologize. I am using the *.MDB which is the report writer database format and the recommended format by BB.

I am going to check on the tables.

Sheela iyer · August 2015

The fields I am trying to export are Constituent information such as code, id, active or inactive, org name and there are four specific attributes that are being used such as priority area, age focus, community group and national association.

Crystal report is displaying all the fields except community group and national association column or field values. National association is blank all the way so that is fine. However community group has values but not displaying in the report.

Table join seems to be okay as well as it is linking to the right tables.

Thanks

Chester Bammel · August 2015

I'm at a disadvantage not being able to see the data, but could it be possible that Community Group is linked, but to the wrong field? If it is a left outer join, that would still give you the full number of records, but all the fields would be blank, since it would not find any matches.

Can you create that same query in Access with the same links and get results?

Marie Stark · August 2015

If you go to your Field Explorer, right click on the field for your community group, and select Browse Data, does it show any values?

That would show you if your data is coming into the report or not.

Sheela iyer · August 2015

Thank so much that is helptul to know. Yes it does show the data values but oddly enough not displaying it !

Sheela iyer · August 2015

Chester Bammel:

I'm at a disadvantage not being able to see the data, but could it be possible that Community Group is linked, but to the wrong field? If it is a left outer join, that would still give you the full number of records, but all the fields would be blank, since it would not find any matches.

Can you create that same query in Access with the same links and get results?

Hi Chester,

We are on a hosted service so we don't host the application nor do we have Access. We are also on a Mac environment here which makes it a bit complicated compared to the rest of the world on Windows ! I do not think the issue has anything to do with Windows or Mac any ways. I created another report on Crystal and that seems to work fine with or without Grouping.

Marie Stark · August 2015

Do you have any groups on your report? If so, are the fields in the Detail Section, or in the Group Header or Footer?

Sheela iyer · August 2015

Sheela iyer:

Thank so much that is helptul to know. Yes it does show the data values but oddly enough not displaying it !

What I did notice is when I just include the Community Group field by itself the values are displayed but it not displaying when I add all the four specific attributes in the Details row . Almost seems like it is trying to group but I have not applied any grouping at all within Cyrstal report !

Marie Stark · August 2015

I have seen that happen on my reports.

Try making sure the link to that field is a Left Outer Join.

Sheela iyer · August 2015

Marie Stark:

I have seen that happen on my reports.

Try making sure the link to that field is a Left Outer Join.

Hi Marie,

Thanks for the input. How do I confirm it is Left Outer Join ? From Crystal report I guess where it is linking the tables, is it ? Within Raiser's Edge it is displaying everything so I don't quite see a problem there.

Marie Stark · August 2015

In the Crystal Report, go to Database/Database Export, and the Links tab.

Try to figure out which is the link to your attribute, click on it, then select Link Options on the menu on the right hand side.

Change the Join type to Left Outer Join, then OK.

I hope that does the trick!

Sheela iyer · August 2015

Sheela iyer:

Marie Stark:

I have seen that happen on my reports.

Try making sure the link to that field is a Left Outer Join.

Hi Marie,

Thanks for the input. How do I confirm it is Left Outer Join ? From Crystal report I guess where it is linking the tables, is it ? Within Raiser's Edge it is displaying everything so I don't quite see a problem there.

Yes that did the trick. The table was not linked properly. It was inner join as opposed to Outer join. I am learning since this is my first time with Crystal Report with no support whatsoever.

Thanks to everyone who helped me through this post. You guys are wonderful !

Have great day !

Suzette Ball · August 2015

I beleive that Blackbaud recommends that all of the fields should have the Left Outer Join rathen than the Inner Join. I know that is generally what I use.

Sheela iyer · August 2015

Thanks ! I kind of figured that out myself after several hours of frustration. By default it seems to be doing an INNER JOIN on all the tables although some tables have to be inner join or else the report cannot display the results.

Thanks again .....

John Heizer · August 2015

Sheela iyer:

Thanks ! I kind of figured that out myself after several hours of frustration. By default it seems to be doing an INNER JOIN on all the tables although some tables have to be inner join or else the report cannot display the results.

Thanks again .....

Depending on the data you may run into trouble trying to mix LEFT OUTER with INNER joins in Crystal. I've had it work fine initially, but later when the data changed it would result in an error because it could no longer get a one-to-one relationship between the tables.

Using all LEFT OUTER joins should result in all data being displayed, maybe more than once if there is a one-to-many relationship between the tables. At that point you have to either live with the duplicates or put that output into a Subreport.

Also, be aware that sometimes when rearranging tables and links in the Database Expert LEFT versus RIGHT relationships can change. Clicking on the "Auto Arrange" button can reveal such reversals.

Sheela iyer · August 2015

Thanks and appreciate the tip. Will keep an eye out for this.

Crystal Reports - Grouping

Comments

Categories