crystal report (Having ) statement

Question

I have an SQL query like

SELECT
             GNTD_SCHEDULE_CODE SCHEDULE,
             GNTD_PARENT_ACCT PARENT_ACCOUNT,
             GNTD_ACCOUNT_CODE ACCOUNT,
             GNTD_COMP_CODE,
             SUM(CONVERT(FLOAT, ISNULL(GNTD_CUR_MNTH_ACT, 0))) CURR_MONTH_END,
             SUM(CONVERT(FLOAT, ISNULL(GNTD_PRV_PRD_ACT, 0))) PREV_MONTH_END,
             SUM(CONVERT(FLOAT, ISNULL(GNTD_CUR_YEA_YTD_ACT, 0))) CURR_YEAR_END,
             SUM(CONVERT(FLOAT, ISNULL(GNTD_LST_YEA_YTD_ACT, 0))) PRV_YEAR_END
    FROM  GLAS_NLMR_PROCESSED_DATA 
    WHERE    GNTD_COMP_CODE  = '1' 
 AND GNTD_ACCT_TYPE = 'P' AND 
GNTD_DEP_CODE = isnull ( '01' , GNTD_DEP_CODE)
GROUP BY GNTD_COMP_CODE , GNTD_SCHEDULE_CODE , GNTD_PARENT_ACCT , GNTD_ACCOUNT_CODE
HAVING ( SUM ( isnull ( GNTD_CUR_YEA_YTD_ACT , 0)) <> 0) OR ( SUM ( isnull ( GNTD_CUR_MNTH_ACT , 0)) <> 0)

I am using asp.net crystal report formula editor to conver above query like

{GLAS_NLMR_PROCESSED_DATA.GNTD_COMP_CODE} = {?Company} and
if({?Dept Code})=""
then
  {GLAS_NLMR_PROCESSED_DATA.GNTD_DEP_CODE}="01"
else
  {GLAS_NLMR_PROCESSED_DATA.GNTD_DEP_CODE} = {?Dept Code} and

---

I dont know how to use group by and having statement in crystal report.

How can i?

Answer 1

You can add grouping to a report by right-clicking, and selecting Insert -> Group, and then selecting the field you want to group on. Repeat this process to add more groups.

The 'having' is a little tricky to do in the report. You need to use a supression formula. Create a summary field in your group footer (right-click, Insert -> Summary, the select the field and the 'sum' operation). Then, in the section expert, add click the 'Suppress' button and add a formula like this:

WhilePrintingRecords;
Sum({summary_field_name}) = 0;

Add this formula in the group header, details, and group footer, and then do it again for each 'having' clause you want to implement.

FYI - This is probably more efficiently done in the database, using the query you already have.

Edit: More Info

You do not use the formula editor to create a summary field. Use Insert -> Summary as described above to create the field. Then, in the Suppress formula, select the new summary field from the 'Report Fields' list. It will be named something like 'Sum of '