?
Solved

Using data from a formula for a parameter

Posted on 2012-04-02
12
Medium Priority
?
200 Views
Last Modified: 2012-04-30
If i have two formula fields which calculate the dateDiff's of two dates by days, how can I use the data from both formulas as a single parameter?

Example:

Doc A Days between Start and stop:  70
Doc B Days between Start and Stop: 30

User needs to see all records that fall between 25 and 55 days and it needs to be pulled records need to be shown from both tables.
0
Comment
Question by:susnewyork
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37798830
What are the formulas?

Are the tables joined?

mlmcc
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37801566
The tables are Left joined to a patient table. The formulas show the difference between two dates producing a number.

Formula 1 Start Stop:
if {pt_docs.template_file}='Doc A' then {signoff_hx.create_timestamp}-{@admitdate}-30
else
if {pt_docs.template_file}='Doc B'  then {signoff_hx.create_timestamp}-{@lastHistory}-90

Open in new window


Formula 2 Start Stop:
Basically the same as one, but more logic involved which selects the most recent date from a possible 7. The end result is:
CurrentDate-{@nextDueDate}
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37802576
I just rethought this, although it may be the same logic ultimately.

Instead of numbers, somehow I would need to have two due dates from two different fields fall into one parameter and show results based on the one parameter.
0
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

 
LVL 101

Expert Comment

by:mlmcc
ID: 37802842
So the formula calculates the DateSigned Off - Date Created.  What is the -30  is that also sutracting?

How about the other one where the number is 90?

Since it says time stamp, is that a date time type or an integer for the number of seconds?

You want to see any that are Date Signed Off - Date Created > 25 and Date Signed Off - Date Created < 55?

mlmcc
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37802879
The -30 takes away 30 days from DateSigned Off - Date Created and -90 from another calculation.

it's a datetime type.

The user will need to enter a date range and see how many patients show as a result. The results need to fall within the users entered date range. so they will use the parameter to select two dates and then see the results, if possible.
0
 
LVL 35

Expert Comment

by:James0628
ID: 37804628
Ignoring the 25 to 55 days from your first post and the days between dates calculations, if you just want to compare a date range entered by the user to two date fields, you could just use:

{date field 1} = {?date range parameter} and
{date field 2} = {?date range parameter}

 Use AND to get the records where both fields are in the range, or OR to get the records where either field is in the range.

 But I'm guessing that what you're really looking for is not that simple.

 James
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37805963
James.

The date fields are calculated within a formula. I would need to use those dates within the parameter, which doesn't seem easily possible. Any ideas how I can do that?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37805981
Depends on the formula.

What formula are you trying to use?

You cannot use a printtime formula so it cannot have WhilePrintingRecords or shared variables

mlmcc
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37806016
The date formulas don't have WhilePrintingRecords or shared variables. The formulas simply evaluate a set of date fields and choose the latest one, then add 30 or 90 days onto them.
0
 
LVL 1

Author Comment

by:susnewyork
ID: 37807015
instead of going extra fancy, I used a group which shows different time intervals based on the combined formulas. Any ideas to solve this issue otherwise?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37808981
Can you upload the report?

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 37809526
As mlmcc said, it depends on what you're doing in the formula(s).  For example, you posted this earlier:

if {pt_docs.template_file}='Doc A' then {signoff_hx.create_timestamp}-{@admitdate}-30
else
if {pt_docs.template_file}='Doc B'  then {signoff_hx.create_timestamp}-{@lastHistory}-90


 Assuming that @admitdate and @lastHistory produce a date/datetime result, then that formula just produces a number (the number of days between two dates, minus 30 or 90), so you can't compare that to a date.  But you could, for example, add the result of that formula to some date and compare that to another date; or take the difference between two other dates and compare that difference to the result from that formula.

 If you want to compare the results from a formula like that with a date range that the user enters, how is the comparison supposed to work?  What are you really trying to compare?

 OTOH, if @admitdate and @lastHistory actually produce a numeric result, the formula above would produce a date/datetime result (create_timestamp minus X number of days).

 James
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question