| Author |
Message |
JDodd
Newbie
Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
|
   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 Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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 Logged |
|
JDodd
Newbie
Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
|
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 Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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 Logged |
|
JDodd
Newbie
Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
|
Posted: 06 Apr 2007 at 12:44pm |
|
How do you go about creating a stored procedure?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
JDodd
Newbie
Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
|
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 Logged |
|
JDodd
Newbie
Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
|
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 Logged |
|
|
|
