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
Ravi_JaganathanAsked:
Who is Participating?
 
Ravi_JaganathanConnect With a Mentor Author Commented:
Actually I created a union which bring a null row if the main query doesn't return any rows.
0
 
crgary_txCommented:
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
0
 
frodomanCommented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Ravi_JaganathanAuthor Commented:
most of the time main report retrun the result
0
 
crgary_txCommented:
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
0
 
frodomanCommented:
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.
0
 
Ravi_JaganathanAuthor Commented:
Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.