| Author |
Message |
robrien01
Newbie
Joined: 25 Dec 2013
Location: United States
Online Status: Offline
Posts: 3
|
  
Topic: Formula to return result from a second database
Posted: 27 Dec 2013 at 5:06am |
|
I have just started using Crystal with more than one database.
I am trying to compare similar data elements in two database files. One contains a list of records which each have a rating and a remaining duration. I have created a formula to sort the remaining duration into segments 1yr, 2yr etc.
The second database contains a rating, a duration 1yr etc. and a corresponding factor for each.
Rating Duration Factor
A 1 .1
A 2 .2
B 1 .3
B 2 .4
I am trying to create a formula which will return the appropriate factor for each instance in the original file.
So if record 1 has a rating of A and a duration of 2 the result would be .2, record 2 B duration 2 = .4, record 3 A duration 1=.1 etc..
I have tried and cannot seem to get it. I thought I could use an if or a select expression.
I appreciate any help. Thanks
|
|
RTO
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 27 Dec 2013 at 5:21am |
|
I am not sure how I would go about this in CR...and I can give you how I would accomplish the report...
I would use a stored procedure, and have the stored procedure return the data from both databases in the same call...it would be a 'bigger' table that would relate everything together on 1 line.
Once all the data is in 1 table, then CR can process it much more easily.
Hopefully, you can create stored procedures, or have someone craft one for you, as I think it might be a simpler solution...though I am biased, as I create all my reports with stored procs.
HTH
|
| IP Logged |
|
robrien01
Newbie
Joined: 25 Dec 2013
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 27 Dec 2013 at 5:38am |
|
I am unfamiliar with stored procedures - combining into one database or report is the problem. Each record has different values for the rating and the duration depending on that combination one of six or seven factor results is possible. Can a stored procedure look at x and y giving result z? I need to read up.
|
|
RTO
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 27 Dec 2013 at 5:50am |
|
A stored proc can do just about anything you want it to.
It can create temporary tables that allow you to manipulate the data as you need. It can return whatever values you want from any combination of columns/tables/databases, so you can craft a solution that returns just the data that you need.
Anything you can do in CR for report writing, you can do in a stored procedure, and more...lots more.
A stored procedure is basically just a set of SQL commands to be executed...I always think of them as batch files for SQL.
There can be alot to learn, depends on how much SQL you already know, and what you need/want your stored proc to do.
I think that will be time well spent
Good luck
|
| IP Logged |
|
robrien01
Newbie
Joined: 25 Dec 2013
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 27 Dec 2013 at 5:55am |
|
Thanks. I was hoping there was a simpler way> I've got a lot to learn.:)
|
|
RTO
|
| IP Logged |
|
|
|
