Skip to Content
avatar image
Former Member

How to implement the Oracle Group by function in Crystal reports?

Hi all,

In SQL, for example we have a group function like:

select district,state, country, continent, sum(no.of people) from world.

Now, How to implement this group function in crystal reports? Please advise.

Thanks in advance..

Regards,

sriram

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 15, 2008 at 01:24 PM

    Hi Sriram,

    If you have to group by so many fields then try to use Add Command to get the required functionality.

    --Create a New Connection

    --Select the Datasource

    --Double click on Add Command, now you insert your SQL statement.

    This will execute your SQL statement and insert grouped records in your report

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I encountered this exact same issue tonight, using Crystal Report XI R2. The SQL for this report was very simple, along the order of:

      select a, b, c, count(d)

      from x

      group by a, b, c

      Though I'm proficient in SQL, I wanted to do things the "Crystal" way, so I used the Group Expert to group by 3 separate columns, creating 3 separate group fields (and 3 group headers, 3 group footers.) . Like Sriram, I then added the summary field in the footer for the last of the 3 group fields, then suppressed detail and all the headers and footers except the footer for the 3rd group field. Ugh! That's ugly. Even though I have the checkbox checked in Database Options to "Perform Grouping on Server", it seems to be pulling all 15,000 rows back to Crystal, then condensing them down to the 70 rows in the report. Show SQL doesn't reveal any GROUP BY clauses.

      I'll create a Command in future reports and just write the SQL myself. Thanks for clarifying.

  • avatar image
    Former Member
    Oct 15, 2008 at 12:59 PM

    Hi Sriram,

    In crystal reports it is similar to the SQL statement

    Goto > Insert menu > Select Group > Select the field for which you want to group by.

    Or

    select district,state, country, continent, sum(no.of people) from world group by district,state, country, continent.

    You can observe a new section is being printed in the report.

    Regards,

    Vinay

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Vinay,

      Thanks for the prompt reply.

      In one of our report, we are supposed to perform group by for 14 columns to get sum of 3 columns and there by displaying 17 columns in the report.

      When we tried in crystal reports to implement this oracle group by functionality:

      1. We created 14 groups from the Insert->Group option.

      2. By performing this, we got 14 group sections vertically(one inside the other).

      3. Then we created the sum(15th column),sum(16th column), sum(17th column) by Insert->Summary option.

      4. We suppresed all the group sections except for the last group.

      5. Then, dragged all the groups to the last group section along with the summary fields.

      This is how, we tried to acheive the oracle group by function in Crystal reports.

      Please advise, whether our approach is right. If not, please suggest the appropriate approach with a bit detailed explanation.

      Thanks,

      Sriram.