Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Values as of an effective date. Post Reply Post New Topic
Author Message
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Topic: Values as of an effective date.
     Posted: 06 Apr 2007 at 6:44am
HI all,
 
Here is my next challenge in Crystal.
 
I have a claim file keyed by risk and claim and a reserve history file keyed by risk, claim and effective date for the reserve amounts.  I want to create a list of all of the risks and claims and their reserve amounts as of user entered date.  So I need to get the reserve amount from the reserve history file where the effective date is the most recent date not exceeding the user entered date for the corresponding risk and claim.  The claim may have 30 reserve records but I'm interested in only 1 record.  I know you can do this using a couple of sub reports and shared variables.  The first subreport finds the max(effective date) not exceeding the entered passes this value back as a shared variable then call a second sub report that receives the risk, claim, and effective date and places the reserve amount in a shared variable.  The problem is that when I go to do a cross tab the shared varialve containing the reserve amount is not available.
 
How else can this be done?
 
Thanks so much
 
Joe  
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 06 Apr 2007 at 11:32am
The cross-tab part confuses me b/c I'm not sure how you want to report the data within the cross-tab. But for the part about pulling out a record for the max date, I would use a custom command object and use SQL to filter the records. Doing all that subreport stuff you mentioned is too much overhead. Here is a sample SQL command that you should be able to review and apply to your own situation. Oh yeah - I got this template from the book, SQL for Smarties. 
SELECT *
FROM SalaryHistory AS S0
WHERE S0.change_date =
   (SELECT Max(S1.change_date)
   FROM SalaryHistory as S1
   WHERE S0.emp_id = S1.emp_id)



Edited by BrianBischof - 06 Apr 2007 at 11:33am
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Posted: 06 Apr 2007 at 12:18pm
I have tried doing it using the command but when I link my claim file to the command file via risk and claim  it takes forever to run just one small risk.  I 've never bee patient enought to let it finish.  I'm running crystal on my PC and accessing data on an AS/400 over an ODBC connection.  Is there a secret to speeding it up?
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 06 Apr 2007 at 12:34pm
No, no secret. Maybe if you set a filter to only do it for a range of records and try that for testing purposes.

Oh yeah, of course you should put this into a stored procedure for optimal performance.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Posted: 06 Apr 2007 at 12:44pm
How do you go about creating a stored procedure?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 06 Apr 2007 at 12:46pm
Instead of using a command and a table, you could try doing something like this with the SQL:
 

Select c.risk, c.claim, rh.effective_date
from Claim as c
left join Reserve_History as rh
  on rh.risk = c.risk
    and rh.claim = c.claim
where rh.effective_date = 
  (select max(rh1.effective_date)
   from Reserver_History as rh1
   where rh1.risk = c.risk
       and rh1.claim = c.claim
       and rh1.effective_date <= :effective_date)
:effective_date is a parameter to the SQL.  Brian, do you know the correct syntax for settin that up in a command so that it can be set from a parameter in the Crystal Report?


Edited by hilfy - 06 Apr 2007 at 12:47pm
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Posted: 06 Apr 2007 at 12:58pm
That's exactly what I was going to try next after researching stored procedures a little more.  By the time I get all of the tables joined that i'm going to eventually need in this particular report I will have 15 or so.  There are all kind of codes and descriptions of codes that need to be looked up.  Joining them in the database expert is just too easy.
 
If you can think of anything else let me know
 
Thanks so much for your help.
 
Joe
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Posted: 09 Apr 2007 at 11:12am
Not having much luck with this project.  I tried doing the entire thing as one big SQL command and using that command as my only table in the Query.  It will work but it takes hours to run.  Not really acceptable.  I noticed you can have prarmeters in the command.  Is there any way to pass a parameter from the report to the command? or pass parameters to an SQL expression? 
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 | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.