cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports does not execute all database queries if first query returns no results

Former Member
0 Kudos

I have a Crystal Report (using CR 2008) that has three database queries (the below is sourced from the "Show SQL" menu option)

MYSERVER "mydb"."dbo"."run_report";1 {d '2000-01-01'}, {d '2015-01-01'}, 'A12345'
MYSERVER select SUser_SName() as [CurrentUser]
MYSERVER select parent_name from containers where id = '{?@id}'

None of these queries depend on each other, although the first and last queries use the same input parameter - @id (which is A12345 in this example)

The above works fine when the run_report stored procedure returns > 0 results. When it returns no results (but no actual errors as far as I can tell), the second and third SQL queries don't appear to be executed as their results (which are inserted into the report as fields) are just blank.

I can see two possible approaches to the issue:

  1. Reorder the SQL queries so that the ones that will always return a result run first, and the stored procedure (which may return no results) runs last; or
  2. Somehow I make Crystal Reports continue on its merry way even if the stored procedure query doesn't return any rows.

...but I'm not sure how to achieve either approach.

(This is a cross-post from http://stackoverflow.com/q/28956976/79450 )

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hello Andrew,

Solution A,

add to the run_report stored procedure if there is not return value, it must return and empty line with NULL values.

it will have always return value (an empty row with NULL values in each column) so it will process the next statements.

Solution B,

Separate your report using +2 subreports

- main report executes the command: MYSERVER
"mydb"."dbo"."run_report";1 {d '2000-01-01'}, {d '2015-01-01'}, 'A12345'

- 1st subreport executes next command

MYSERVER select SUser_SName() as [CurrentUser]

-2nd subreport executes the next command

MYSERVER select parent_name from containers where id = '{?@id}'

Each solutions are good

János

Answers (1)

Answers (1)

Former Member
0 Kudos

Thanks János. I used the first approach and it worked fine.


Many thanks,

Andrew