Sql – Crystal Reports – Group By

crystal-reportssql

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.

Best Answer

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'.

Creating a Group

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')

Group Expert Fields Selection

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 New Count Field

Add a Field which you want to count.

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

Adding Count Field

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 !

Related Topic