Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Shared Variables in SQL Post Reply Post New Topic
Author Message
iSing
Newbie
Newbie


Joined: 12 Mar 2013
Online Status: Offline
Posts: 22
Quote iSing Replybullet Topic: Shared Variables in SQL
     Posted: 04 Apr 2013 at 9:42pm

Hello all.

I am using Crystal XI via Citrix connecting to an SQL database.
I am reporting on client data.  I am writing a report to use for data integrity as we cannot change the programming of the dbase.
 
The first stage is to collect information of a particular type of record (s01). 
Once that is done, it's intended that this is to be used to check if other records (eg NDA) are in sync with the s01 dates.  However, the s01 only has a creation date (eg s01.date) & so I'm using a shared variable to artificially create an end date (the date of the next s01 for that client.  If there is no s01 for the client, then the end date is 31/12/1899 (dd/mm/yyyy)).
 
I've started by creating the report that finds the s01 data & creates the end date.  The report is currently grouped on client and then grouped on the s01.date (a rule in the dbase means that a client cannot have more than s01 per day).
 
Thus:
Client A
     s01 date is 12/01/2013 - end date is 07/02/2013 //(the date of the next s01)
     s01 date is 07/02/2013 - end date is 03/04/2013
     s01 date is 03/04/2013 - end date is 31/12/1899 //as no further s01 for this client, the default date is used.
Client B
     s01 date is 15/01/2013 - end date is 30/03/2013
     s01 date is 30/03/2013 - end date is 31/12/1899 //as no further s01 for this client, the default date is used.
 
This works fine.
 
I then want to use this to check that another type of record (NDA) is in date sync with the s01.  Naturally the client can have multiple NDA.  I can't see how to use a subreport for this (as I need the s01 date & enddate before I can check the NDA dates), so I was going to use an SQL command.
 
So my problem is - how can I create a SQL command that includes the shared variable to generate the end date of the s01?
 
I suspect this isn't possible, but I thought I'd ask.
The only thing I can think of is to create a report that generates the s01 end date etc, export it out into Excel or Access & then create another report that points to this s01 data & the dbase (for the NDA data) which does the comparison. 
I'd rather not do this, as this would mean users could not access this report (due to network restrictions).
 
 


Edited by iSing - 04 Apr 2013 at 9:53pm
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 09 Apr 2013 at 2:58am
set your dates in shared variables...since they cross the boundary of subreports and main report. Link you subreport on the customer data (like ID). Then in the subreport filter the data using your shared variable values.
 
At least this is the tack that I would try first.
 
Shared variables are really easy.
 
In the main report and the subreport you just need a line like:
shared numbervar varName;
 
Once that is in the formula, the variable is accessable, so you can set your start and stop values.
 
This would only work for example if you call the subreport between each date line.
 
My other solution depends on if you are allowed to create stored procedures in the database. If so, I would go that way, building a temp table of values for your customers and populating them as desired.  Then you could just return the table from the stored proc, and the values would persist and be 'pre-calculated' for you.
 
HTH
IP IP Logged
iSing
Newbie
Newbie


Joined: 12 Mar 2013
Online Status: Offline
Posts: 22
Quote iSing Replybullet Posted: 14 Apr 2013 at 8:42pm
Hi Lockwelle
I'm honoured that you replied to my post.
I should have explained that the report looking for the s01 is already a subreport - that's why I was looking to use an SQL command.
I will rejig things & try to navigate the many to many relationship. 
(Unfortunately, I am waaay to far down the feeding chain to create a stored procedure.)
I will post my success (or lack of it), so others may benefit. 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 15 Apr 2013 at 7:54am
Some databases will let you use a select statement for a field.  So, you could try something like this:
Select 
  <fields>,
  min(Dates.date) as EndDate
from SO1
  left join SO1 as Dates on 
    SO1.client = Dates.client and
    SO1.date < Dates.date
where <where criteria>
group by <fields>
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.