Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Adding SQL to a Report (possibly) Post Reply Post New Topic
Author Message
neilsja
Newbie
Newbie


Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote neilsja Replybullet Topic: Adding SQL to a Report (possibly)
    Posted: 10 Oct 2011 at 1:48am
Hi All
 
Last question for now:
 
I have to add a SQL statement (below) to my Crystal Report as this does not exist as an actual record.  So I have two questions:
 
1. What is the best way to do this? and
2. The field highlighted (which is an inventory item ref) is actually on my report.  But how do I populate this field with the correct reference i.e 00001.
 

(select min(pm_maintsched.mntsched_date)

from pm_maintsched, pm_maintplan_ivtitems, pm_mntplan

where pm_maintsched.mntplan_number = pm_maintplan_ivtitems.mntplan_number

and pm_mntplan.mntplan_number = pm_maintplan_ivtitems.mntplan_number

and pm_maintplan_ivtitems.ivt_ref = IM_INVITEM.IVT_REF

and pm_mntplan.reason_code is null

and pm_maintsched.mntsched_date >= GETDATE())

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 12 Oct 2011 at 5:46am
the first thought that comes to mind is to create a subreport and pass the highlighted field as a link to the subreport.  Include you query as a Command Object using a parameter for the highlighted field.
 
Hopefully it is a path to the solution.
 
 
IP IP Logged
neilsja
Newbie
Newbie


Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote neilsja Replybullet Posted: 12 Oct 2011 at 6:25am
Thank you for your help.
Is there any way to pass a parameter in silently?  As when I add a parameter it asks you to enter something in.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 12 Oct 2011 at 11:50am
I was hoping that when the report ran, it would use the linked value from the main report to gather the information from the Command object and not prompt the user.
 
I know that it will prompt during the design of the subreport, but I was hoping that it wouldn't when called from the main report.
 
I don't use subreports very often, and I don't think that I have ever use a Command object as the data source for the subreport (I mostly use stored procedure) and I was hoping that this approach would not result in the user being prompted for a parameter.
 
Just to make sure, you have linked the subreport to the main report via the field?
IP IP Logged
neilsja
Newbie
Newbie


Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote neilsja Replybullet Posted: 12 Oct 2011 at 9:58pm
Hi
 
I was just checking based on what I have tried.
 
Reading up on sub reports might actually do what I need so thank you for your help.
 
I am going in for surgery tomorrow so I wont be able to check this for a few weeks so I will say I appreciate your help in advance.
IP IP Logged
neilsja
Newbie
Newbie


Joined: 03 Oct 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote neilsja Replybullet Posted: 13 Oct 2011 at 3:01am
Hi
 
I just tried it with sub reports and it works a treat.
 
Thank you for your help, and something new learnt.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.000 seconds.