Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Select certain record in the formula fields Post Reply Post New Topic
Author Message
drintharamy
Newbie
Newbie
Avatar

Joined: 21 Jun 2013
Location: United States
Online Status: Offline
Posts: 8
Quote drintharamy Replybullet Topic: Select certain record in the formula fields
    Posted: 08 Oct 2013 at 10:37am
I have three (3) records in the table:
 
LOB     Effective     Operating Cost    CompanyID
T         2013-02     $75.00                 0104
T         2013-08     $105.00               0104
T         2013-09     $95.00                 0104
 
I want to create a formula fields to retreive certain record based on when the report is running.
 
Example: If I run the report for the month of October, I want to retrieve the most recent record, in this case, 2013-09.  If I run the report for the month of September, I want to retrieve the record with effective date of 2013-09.  If I run the report for the month of August, I want to retrieve the record with effective date of 2013-08, etc, etc...
 
Here is my formula fields:
 
IF  ({RouteOperatingCost_vw_All.LineOfBusinss} = 'T') AND
    (TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod}) >=   TONUMBER(TOTEXT({?StartDate},'yyyyMM')) AND
     TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod}) <= TONUMBER(TOTEXT({?EndDate},'yyyyMM'))) THEN
        {RouteOperatingCost_vw_All.OperatingCostPerHour}
ELSE
    0
 
This formula field does not do what I wanted to do.
 
Any help would be appreciate it.
 
Thanks!
 
DOUANG RINTHARAMY
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 08 Oct 2013 at 1:38pm
i gave it a shot not sure if this will work though

if not(TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod}) in TONUMBER(TOTEXT({?StartDate},'yyyyMM')) to TONUMBER(TOTEXT({?EndDate},'yyyyMM')) ) and
(TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod}) = maximum( (TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod}))
then {RouteOperatingCost_vw_All.OperatingCostPerHour} else
if TONUMBER(TOTEXT({date.field},'yyyyMM')) in TONUMBER(TOTEXT({?StartDate},'yyyyMM')) to TONUMBER(TOTEXT({?EndDate},'yyyyMM'))
then {RouteOperatingCost_vw_All.OperatingCostPerHour} else 0

Edited by kostya1122 - 08 Oct 2013 at 1:39pm
IP IP Logged
drintharamy
Newbie
Newbie
Avatar

Joined: 21 Jun 2013
Location: United States
Online Status: Offline
Posts: 8
Quote drintharamy Replybullet Posted: 09 Oct 2013 at 2:41am
Thanks Kostya1122 for trying!
 
I will incorporate your code into my formula and test it out.
 
Thanks again!
DOUANG RINTHARAMY
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 10 Oct 2013 at 5:03am
Just because I hate to see lots of function calls in logic that are repeatitive, how about something like this:
local numbervar st := TONUMBER(TOTEXT({?StartDate},'yyyyMM'));
local numbervar en := TONUMBER(TOTEXT({?EndDate},'yyyyMM'));
local numbervar ep := TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod};

if not ep in st to en and ep = maximum((TONUMBER({RouteOperatingCost_vw_All.EffectivePeriod})) then {RouteOperatingCost_vw_All.OperatingCostPerHour}
else
if TONUMBER(TOTEXT({date.field},'yyyyMM')) in st to en
then
{RouteOperatingCost_vw_All.OperatingCostPerHour}
else
0


though looking at the post, it says that the formula should be used to retrieve certain records, which sounds like a filter to me. This formula just gives an amount or zero, but will display other 'unwanted' records...at least that is what it seems to me.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Oct 2013 at 5:30am

i read this as

you want a run time parameter that lets you selct the record of choice
if you run it for this month, that record does not exist yet so you want it to pull last month instead.
 
I think you might be overcomplicating this. why not just use a dynamic parameter that points to your 'Effective' field then use that param in the select statement
table.effective=?parameter
IP IP Logged
DaBoujibo
Newbie
Newbie


Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Quote DaBoujibo Replybullet Posted: 11 Oct 2013 at 9:50am
I agree with DBlank. I use a date as a parameter in many of my reports that I create and use it in the select statement. That would be the cleanest way to go and allows you to use the same report for different date ranges. Let's say you wanted to review a report for a past month, then you could pull it based on the date you enter instead of going into the report and changing the selection criteria each time.

Just my 2 cents...

Christy


Originally posted by DBlank

i read this as


you want a run time parameter that lets you selct the record of choice

if you run it for this month, that record does not exist yet so you want it to pull last month instead.

 

I think you might be overcomplicating this. why not just use a dynamic parameter that points to your 'Effective' field then use that param in the select statement

table.effective=?parameter
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.