Calculating Values after a Parameter Date 1

[Storyteller]

Hello All,
I've been trying to claculate the {ChangeInUnits}that occur after my {?Fiscal Year End}. The reason is that I know what the {TotalUnits} is for Today, but I have to subtract any transactions that occured after {?Fiscal Year End}

I've created the following formula. But, I still get {TotalUnits} not {TotalUnits} less the transactions that occured after {?Fiscal Year End}
*********************
@S Closing Share Balance

If {?Fiscal Year End} < Today
Then {InvestHolding.TotalUnits}-
(If {InvestTrans.EffectDt}>{?Fiscal Year End} Then
(If{InvestTrans.TransType} in [&quot;Dividend&quot;,&quot;Buy&quot;]
Then {InvestTrans.ChangeInUnits}))
Else If {?Fiscal Year End} < Today
Then {InvestHolding.TotalUnits}+
(If {InvestTrans.EffectDt}>{?Fiscal Year End} Then
(If {InvestTrans.TransType} = &quot;Sell&quot;
Then {InvestTrans.ChangeInUnits}))

I'm using CR 7 reporting from an SQl datasource

Any help would be greatly appreciated !!

 

[toekneel]

My first thought on this would be to create a formula to define if a record fits your date range. Something like...

@Formula1
If {?Fiscal Year End} < Today then True else False.

Then I'd create another formula where I'd total items fitting the criteria for @Formula1

@Formula2
If {@Formula1} is true then {ChangeInUnits} else 0

Then by totalling on the @Formula2 you would have an accurate count of {ChangeInUnits} for your date range.

 

[Storyteller]

Hello ToeKneel,
Thanks for the advice. The results are very close to what they are suppose to be.

The frustration is that I can verbalize what I want to do. It's just that I can't seem to do it programatically (sp?)

What I would truely like to do is:
SELECT {ChangeInUnits} WHERE {EffectDt} > {?Fiscal Year End}
But CR7 does not support SQL, and unfortunately I cannot use a different version of Crystal.

Have a great weekend,
MO

 

[dgillz]

CR7 DOES support SQL - At least mine does, its called Crystal SQL Designer. My copy of 7 was bundled with Macola software. Maybe yours did not have that option or the install was not a full install. There are 3 &quot;flavors&quot; of CR - Standard, Professional and Developer. Maybe they do not all suport SQl. I'd revisit this if you can. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

StoryTeller,

You could do it in SQL, but CR can only use one SELECT per report, which would make you use subreports to get separate numbers.

When you tried ToeKneel's approach, you said it was close. How is it off? Note that each formula creates a column of detail values. You will probably need a couple of formula columns, each one with a total, and then a formula that works with the totals. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Storyteller]

Hello All,
I followed toekneel's advice and created the following:

@Test 1
If {InvestTrans.EffectDt}<={CorpUserDef.Date3} then True else False
//{InvestTrans.EffectDt}=Date of Transaction
//{CorpUserDef.Date3} = Fiscal Year End

@Test 2
If {@test 1} = False then {InvestTrans.ChangeInUnits} else 0

@S Closing Share Balance
{InvestHolding.TotalUnits}-{@test 2}
//{InvestHolding.TotalUnits}= Shares as of today's date
// This field is placed into Group Footer #2

The formula works correctly if there is only one transaction after the Fiscal Year End. However, if there is more
than one transaction after the Fiscal year end it only returns/uses the {InvestTrans.ChangeInUnits} for the last transaction
date and not the Sum({InvestTrans.ChangeInUnits})

I've created the following formula
@Transactions since FYE
If {InvestTrans.EffectDt}> {CorpUserDef.Date3}
Then Sum({InvestTrans.ChangeInUnits},{InvestSpec.AbbrName})

I've then changed @Test 2 to read
If {@test 1} = False then {@Transactions since FYE} else 0

The result of the @S Closing Share Balance is then a blank field.

Thank you for your help in this problem.

I would like to wish everyone a Happy New Year

MO

 

[kenhamady]

You need a total of test2, so try using this:

{InvestHolding.TotalUnits}- Sum ( {@test 2} )

This subtracts the grand total of test2 from the Units. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Storyteller]

Hello Ken,

Thanks for the advice. Shortly after I posted my last message I figured out the exact same thing you suggested.

I hope you had a great New Year's Celebration.

Thank you to eveyone for your help with this problem.

Michael