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.

Solution 1

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 !

Solution 2

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

Solution 3

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.

Solution 4

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.

View more solutions