Solved

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

Posted on 2008-06-12
10
383 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:Ravi_Jaganathan
  • 3
  • 2
  • 2
10 Comments
 
LVL 13

Expert Comment

by:crgary_tx
ID: 21769847
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
 
LVL 42

Expert Comment

by:frodoman
ID: 21770686
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
 

Author Comment

by:Ravi_Jaganathan
ID: 21770701
most of the time main report retrun the result
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:crgary_tx
ID: 21770948
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
 
LVL 42

Expert Comment

by:frodoman
ID: 21772276
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
 

Accepted Solution

by:
Ravi_Jaganathan earned 0 total points
ID: 21804398
Actually I created a union which bring a null row if the main query doesn't return any rows.
0
 

Author Comment

by:Ravi_Jaganathan
ID: 21804424
Thank you for your help
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now