Report Design: Select certain record in the formula fields

Print Page | Close Window

Select certain record in the formula fields

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20101
Printed Date: 03 May 2024 at 3:21am

Topic: Select certain record in the formula fields

Posted By: drintharamy
Subject: Select certain record in the formula fields
Date 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

Replies:

Posted By: kostya1122
Date 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

Posted By: drintharamy
Date 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

Posted By: lockwelle
Date 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.

Posted By: DBlank
Date 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

Posted By: DaBoujibo
Date Posted: 11 Oct 2013 at 9:50am