Getting most recent field based on date 2

[Scroller52]

Hi, i'm using CR XI on a SQL db. my data looks like this:

Investor Date Tax
1 1/1/07 20%
1 1/1/08 25%

I need the details sections to return just the most recent tax rate of 25%. There should not be a scenario where there are two tax rates on the same date.

any help is appreciated. thanks!

 

[lbass]

Insert a group on investor, and then go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.investor})

-LB

 

[Scroller52]

Thanks for the quick reply lbass, I think my situation might be a little more complicated. Let me try to explain:

I have 3 tables being brought into this CR:
Driver (main report), Specific Tax, Base Tax

I have 2 groups on driver.investor id and driver.fund

The table Driver is used to bring almost all of the information in the report. The other two are used to bring in a tax rate.

The base tax rate is the default tax rate for an investor. This field can have multiple values if an investors tax rate changes, so I will need to pull the most recent field for each investor.

The specific tax rate is if for a particular deal, an investor has a different tax rate than their base tax rate. This field can also have multiple values, so I will again need the most recent tax rate for each investor.

The formula for the tax rate for an investor is if the taxable amount is 0, then the rate is 0, if there is a specific tax rate for a certain deal, then show that. Then if the taxable amount is <>0 and there is no specific tax rate for a certain deal, then show the base tax rate for an investor. I hope that I explained my issue clearly, thanks for the help guys!

 

[lbass]

Please show the content of the formulas and sample data again. You should have laid this all out in your initial post.

-LB

 

[Scroller52]

My main driver report is using the following fields:
Type, Fund, Investor, Investor ID, Deal, Position, Date, Foreign Tax Amount, US Tax Amount, Conversion Rate.

The report is being grouped by Investor ID 1st then by Fund.

There are no formula's used in the report, everything is being pulled directly from the driver report.

Specific Tax Rate includes:
Type, Fund, Investor, Investor ID, Deal, Position, Date, Specific Tax Rate

Base Tax Rate includes:
Type, Fund, Investor, Investor ID, Date, Base Tax Rate

Both of the tax reports are linked to the driver report via:
Type, Legal Entity, Investor, Investor ID. The Specific Tax rate is also linked via the Deal Name and Position fields.

For every date, I want to show the Date, Deal Name, Position, Foreign Tax Amount, Conversion Rate, US Tax Amount. I have that in the Details section and it works out perfectly. The only thing left is the Tax Rate.

The formula for the tax rate for an investor is if the Foreign Tax Amount is 0, then the rate is 0, if there is a specific tax rate for a certain deal, then show that. Then if the taxable amount is <>0 and there is no specific tax rate for a certain deal, then show the base tax rate for an investor.

There can be multiple specific and base tax rate for an investor so I only want the report to return the most recent tax rate.

I've posted up a picture from a sample excel file that my data would be pulled from. In this example for John Smith, his Base tax rate is .20 as of 1/1/07, as of 12/31/07, it changes to .30. So if this report were to be run anytime on or after 12/31/07, the base tax rate is .30.

Also for John Smith, his specific Tax rate for Toyota - Interest is .15. So this would override the Base tax rate whenever there is an entry for Toyota - Interest. It would need to show as .15. If the rate changed later on, the report would have to pick up the newer specific tax rate %.

I hope this clears up what my issue here. thanks!

 

[Scroller52]

LB

it seems like my issue is very similar to Cabrera01's. but i need the info displayed in the details section rather than group footers.

http://tek-tips.com/viewthread.cfm?qid=1530041

 

[lbass]

Please show the actual formula contents.

-LB

 

[Scroller52]

I'm not using any formula's right now, i'm trying to figure out what formula to use as my 'tax rate' that could fulfill all of those requirements. anything that i have thought of would not work or would duplicate data

 

[lbass]

The formula for the tax rate for an investor is if the Foreign Tax Amount is 0, then the rate is 0, if there is a specific tax rate for a certain deal, then show that. Then if the taxable amount is <>0 and there is no specific tax rate for a certain deal, then show the base tax rate for an investor.

-LB

 

[Scroller52]

LB

i was referring to the requirements for the formula. i got as far as this:

if {Driver.Foreign Tax Amount}=0 then 0
else if isnull({Specific Tax.Tax Rate})
then {Base Tax.Tax Rate}*100
else{Specific Tax.Tax Rate}*100

so the problem is when there are 2 or more Tax Rates in the Base or Specific driver reports. CR would return both values, I need just the most recent one.

 

[lbass]

I can't follow all of this without seeing some sample data at the detail level that includes both formulas, dates,and groupfields. Just show a little mockup that identifies the groups, and then show the two taxes (based on your formulas) and the related dates across a couple of groups. Then show us what results you would expect to see. It's not that this is so hard, but that I can't picture the data.

-LB

 

[Scroller52]

Is there a way i can send you a sample excel file and crystal report? I've got some dummy data in there and would be easier to explain than typing another longwinded reply in the forum.

 

[lbass]

I don't really want an explanation or even to see the real report, just to see some mock data that clarifies the issue.

-LB

 

[Scroller52]

i attached a picture of the mock data in a previous post. here it is again.

 

[Scroller52]

In the Base Tax Rate driver, John Smith has 2 base tax rates for the Fund US .20 his original rate but as of 12/31/07, the rate changes to .30.

I need the CR to pull just the most recent field. I would need the same to happen if John smith had another tax rate in the specific tax rate for Toyota. Does this clarify my issue?

 

[lbass]

But your sample only shows one tax rate. I'm trying to see how the data looks (at the detail level) with both tax rates with different dates for each. Or am I thinking incorrectly about this?

-LB

 

[Scroller52]

when the report brings in both 'base tax rates' the details section is doubled. so it will show every field twice, once for each tax rate.

 

[lbass]

Show me--with dates.

-LB

 

[lbass]

Maybe all you need is to create two formulas:

//{@spectx}:
if {table.date} = maximum({table.date},{table.investor}) then {Specific Tax.Tax Rate}

//{@basetx}:
if {table.date} = maximum({table.date},{table.investor}) then {Base Tax.Tax Rate}

Then change your formula to:

if {Driver.Foreign Tax Amount}=0 then
0 else
if isnull(maximum({@spectx},{table.investor})then
maximum({@basetx},{table.investor})*100 else
maximum({@spectx},{table.investor})*100

What I'm not sure about is whether the dates are specific to the type of tax.

-LB

 

[Scroller52]

LB,

When using the ending formula you suggested:

if {Driver.Foreign Tax Amount}=0 then
0 else
if isnull(maximum({@spectx},{table.investor})then
maximum({@basetx},{table.investor})*100 else
maximum({@spectx},{table.investor})*100

i get the error that where it says i cannot summary the following field. and it highlights this :

"maximum({@basetx},{table.investor})"

if i just left the @basetx by itself, it still brings it two tax rates and will duplicate the data.