Print Page | Close Window

SQL in Crystal gives “blank” but works in SQL Serv

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15818
Printed Date: 31 Mar 2025 at 12:35am


Topic: SQL in Crystal gives “blank” but works in SQL Serv
Posted By: WGroleau
Subject: SQL in Crystal gives “blank” but works in SQL Serv
Date Posted: 07 Mar 2012 at 5:08am

I was asked to add an "as of" date to an Existing Crystal report.

I went into SQL Server (management studio), and figured out how to get a single date meeting the requirement.

Then I copy/pasted the SQL into Crystal's "add command" box. When I put that field in the report footer, it is empty. Go back to SQL server, and it still gives the date I expect.

Checked the formatting properties--it isn't that.

Is there a fix for this?

Normally, I would put the SQL in the server as a stored procedure or function, but it's a vendor-supplied DB that I am not allowed to alter. Can't use profiler for the same reason.

Found a blog article where someone claimed that Crystal demands column names and aliases (but not table names) to be in double quotes. Found that hard to believe but I tried it. No change.


Replies:
Posted By: rkrowland
Date Posted: 07 Mar 2012 at 5:28am
What's the SQL code for the field you're trying to create so I can try and replicate?
 
The double quotes thing is a lie, I use SQL commands in Crystal all the time and I've never once used double quotes.
 
Regards,
Ryan.

Posted By: kevlray
Date Posted: 07 Mar 2012 at 6:35am
The double quotes are handy if the table, field names are separated by an space (i.e, Adult ID, "Adult ID")

Posted By: WGroleau
Date Posted: 07 Mar 2012 at 7:21am
Originally posted by rkrowland

What's the SQL code ...
SELECT CONVERT(VARCHAR(25), MAX(END_TIME), 120) AS DTG

  FROM CLARITY_EXT_BENCH


 WHERE TABLE_NAME = 'ARPB_TRANSACTIONS';
Originally posted by rkrowland

The double quotes thing is a lie,....
Like I said, it was hard to believe...

Posted By: kevlray
Date Posted: 07 Mar 2012 at 10:17am
The SQL looks pretty straightforward.  Just as a test, have you tried taking of the where clause to see if you get any data?


Print Page | Close Window