Crystal Reports - Group By

Question

I'm using Visual Studio 2008 and would like to present the following SQL query in a report:

select name, count(*) from mytable group by name;

I can achieve this by creating a dataset that is essentially the above query (consisting of columns 'name' and 'count'), however it seems overkill to create an additional dataset just for this query. I'd rather do what I've done with other reports in my project and that is have a dataset that is the entire table:

select * from mytable;

And then use Crystal Report features to perform the grouping. Howver I can't find a way to do this grouping. I assume this is possible? This would allow me to reuse the dataset for other areas in the project rather than having to create a unique dataset for each report.

Answer 1

For optimal performance, you should really use GROUP BY on the server side.
Create a view that feeds data to your Crystal Report.

You can do grouping in Crystal, however you will encounter performance issues
as the data queried by your report scales up.

It is a design decision, but it saves lots of time and client processing in the long run.

I tend to design the view first (WHERE clause, GROUP BY clause, DB Functions in SELECT)
and then the Crystal Report after I have the prepared the data on the DB side.

My advice is to start with the end in mind.

Answer 2

Add a group field in crystal; in this example on the name field.

Insert details into the footer of the group.

Insert a count function into the footer of the group, counting on the record unique identifiers. Can also use a distinct count if you have multiple occurences of the same name for example.

Answer 3

If possible, create a database view that groups the dataset and then use that view in the crystal report. This is the easiest and most readable implementation of what you're trying to accomplish in the report.

Answer 4

Grouping Fields

Without Writing SQL query, you can group the Data from the table.

In Field Explorer, you can find 'Group Name Fields' Right Click and Select 'Group Expert'.

After selecting, you will get a Group Expert Dialog Box, which shows the Fields found in your datatable. Select a Field Name which you want to group. (Here I selected 'Project Name')

You can also create Sub-Groups under that field, the Groups ll be classified as Group A, Group B.

Now you can find Group Fields added in your crystal report automatically ! Grouping is Done !

Counting Fields

To Count the no of records or fields in a report, you need to add a new 'Running Total Fields'

Add a Field which you want to count.

After adding the Field Change the 'Type of Summary' to Count, and press OK.

Add this Total Field to your report in the area where you need (Group Footer will be advisable).

Now find your Field Name is Grouped and Counted without writing any SQL Query.

Hope this ll help you, Leave comments if any !

Answer 5

In crystal reports, add group by 'name' field and insert there summary of any field from detail, changing summary function to 'count'.