Link to home
Start Free TrialLog in
Avatar of Ravi_Jaganathan
Ravi_JaganathanFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Need to print null if the query doesn't return any value

Hi,

I have crystal report with sub report (sum of open balance). the query associated with sub query return result. But the query associated with main report doesn't return any result. So the sub report also doesn't show up in the report even though the query returned the results.

Is there a way to print the subreport even if the main report query doesn't return any values.

Note: sub report query just get the parameter from main report query, that is the reason why it brings results.

Ravi
Avatar of crgary_tx
crgary_tx
Flag of United States of America image

Always get a dummy value from the main query even when the query results is zero though you dont have to display on the report. Modify your main query to something like this:

select* from
                (select acct_id, 'dummy' as dummy from
                ci_acct
                where acct_id = '13434'
                ) main_query,
                (select 'dummy' as dummy from dual) b
            where main_query.dummy(+) = b.dummy
     
hth..
Gary
Does the main report sometimes return results or does it never return results and is only a holder for the subreport(s)?  If it never returns results then just simplify the main query:

   select 'dummy' as dummy from dual

frodoman
Avatar of Ravi_Jaganathan

ASKER

most of the time main report retrun the result
You should be trying the query I have suggested.

select* from
                (
                 <your main report query>
                ) main_query,
                (select 'dummy' as dummy from dual) b
            where main_query.dummy(+) = b.dummy
     

Incase your main query is blanck the modified query brings just one record something like below which is suffiecient to print the subreport. You can always supress the record using detail supression formula

Field1 Field 2 Field3....Dummy  Dummy
Null     Null     Null        Null        Dummy

hth..
Gary
Gary is correct - I was pointing out a simpler approach *IF* your main report was intentionally blank.  If there is supossed to be data sometimes then use the approach that he suggested.
ASKER CERTIFIED SOLUTION
Avatar of Ravi_Jaganathan
Ravi_Jaganathan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your help