Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    12

    Unanswered: Need help creating a SQL Expression field in Crystal Reports

    I am new to Crystal Reports. And I'm designing a manual cross-tab report. My database field of Group Name is what is use for the rows. Each group name has data for each month of the year. So the columns are each month of the year. So now I need to fill in the rest of the row for that particular group. I have tried a formula field and that does not work properly. So I have been trying to select the data using the SQL Expression Editor so that I can create a SQL Expression field. But I cannot figure out the syntax or find the proper example to learn from. I have found some examples, but I get errors when trying to save the field. I am running Crystal Reports version 8.5.0.217. This is the SQL statement that I need to work. If any knows the proper syntax could they please show me an example or at least point me in the right direction in order to find a proper example.


    The example below gives me an error on the select statement.

    Select GroupActivity."MedicalCount"
    From 'GroupActivity' GroupActivity
    Where GroupActivity."PayMonth" = 1

    The example below gives me Database error saying Syntax error or access violation. I am running the report form a database that is in SQL Server. Could it be I don't have the correct properties set in SQL Server to allow this type of action.

    SELECT {GroupActivity.MedicalCount}
    FROM 'dbo.GroupActivity' GroupActivity
    WHERE ({GroupActivity.PayMonth} = 1)

    Thanks in advance for any help that you can give me.

  2. #2
    Join Date
    Sep 2006
    Location
    Singapore
    Posts
    10
    (Select MedicalCount from GroupActivity where PayMonth=1)
    An apple a day keeps the doctor away..
    If the doctor is handsome, throw the apple away..

  3. #3
    Join Date
    Sep 2006
    Location
    Singapore
    Posts
    10

    Question using parameter in sql expression field

    Can i do that? And how to do it?
    If cannot, can you suggest any method instead of using parameter in sql expression field?
    An apple a day keeps the doctor away..
    If the doctor is handsome, throw the apple away..

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    I've never worked in 8.5.....7, I assume you don't have a crosstab expert?

    when I look at my SQL statements, both the table name and field name are in quotes
    like:
    Select "GroupActivity"."MedicalCount" unless it's a microsoft product I am pulling from where it uses single quotes. Either way the table name and the field name appear to need to be formatted the same in all the statements.

    I think this is a SQL server specific thing, I am not yet to that point but if someone knows how you write a query on a sql server DB without any help from experts and editors, I would write it that way.

  5. #5
    Join Date
    Sep 2006
    Location
    Singapore
    Posts
    10
    can i use parameter in sql expression field?
    An apple a day keeps the doctor away..
    If the doctor is handsome, throw the apple away..

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    I'm not familiar with the SQL Expression Field but if you mean the SQL that shows your data, just add a parameter field to the selection expert. If you're trying to output a parameter values, that should be easy with a parameter field in ad caluclated field.

Posting Permissions

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