Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Group by on server

    I am trying to get crystal to do all of my grouping on the server. I have the perform grouping on server checkbox checked, but when I view the sql statement, there is no group by clause. AS/400 DB2 is my backend database. I have the Detail section hidden, because the help section said at least one section needs to be hidden (although I don't understand why...). Anyway, can someone help me out on this? I would just add a command and write the sql statement, but it is not allowed unless it is a last resort. If anyone else had to view the report, they wouldn't know what to do, because they don't know sql. Thanks,
    Randy

  2. #2
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Question

    I tried a very simple grouping report (counting the number of rows per result value, grouping by result value). I tried with the Perform Grouping On Server option unchecked, and then checked. I looked at the SQL using the Database menu option, and then clicking on 'Show SQL Query ...'.


    Without server-side grouping:


    SELECT "tblIsolate"."FinalID"
    FROM "SWIN"."dbo"."tblIsolate" "tblIsolate"
    ORDER BY "tblIsolate"."FinalID"


    With server-side grouping:


    SELECT "tblIsolate"."FinalID", COUNT("tblIsolate"."FinalID")
    FROM "SWIN"."dbo"."tblIsolate" "tblIsolate"
    GROUP BY "tblIsolate"."FinalID"
    ORDER BY "tblIsolate"."FinalID"


    Can you post what you get in the Show SQL Query window for the 2 options please?

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    Thanks for the help. I figured it out. I had a formula field in one of the group sections. With the formula inserted, the sql statement had no 'Group By' clause. If I took the formula field out, 'Group By' appeared in the sql statement. Its a shame you can't use formula fields and have the group by performed on the server. I was only checking the value of the group by field and if it was equal to a specific value, then I wanted to print a specific string. I appreciate the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •