Linking Report?

[ddbrook]

I have a report..(Accounts > than 40,000). Looks like this.

Acct#,Util type,Month,Revenue.
9099, E, 7/01/02, $55,000.

Now I need the tax for these same accounts > than 40,000.

Util type is equal to "X" which is the Tax code. It is in the same Revenue column.
Which is less than $40,000...

How will I link these together??
Can I make a subreport??

Any help is greatly appreciated.

Thanks,
DDbrook

 

[IanWaterman]

Can you group on Account.

Create formula called @Limit(or whatever)

If ({Util Typ} = "E" and Revenue >40000) then true else false

In Group Selection add

@Limit = true

Report will then look like
9000
E Date 50000
X Date 12000

Good luck

Ian Waterman
Crystal Programmer UK

 

[ddbrook]

Thanks.
But it still is not showing me anything for the "X" type because it is less than 40,000.
I did everthing you recommended.

Another suggestion!!

Thanks,
DDBrook

 

[basil3legs]

When you import the records, you should have a Selection Formula something like:

{Accounts} > 40000 OR {Util Type} = "X"

This will bring in all the records where the Account is greater than 40,000 as well as all the Tax entries (Util Type = "X&quot. However, this will bring in ALL Tax entries, even if on Accounts less than 40,000. You will then need to pair them up. If there is only one tax entry per account, this can be done as follows:

Create a formula, call it nottax with the formula:

if {Util Type} <> X then {Revenue} else 0

and then sum it for the group, you can then suppress the Group Header/Footer (if normally printed - this will work as the sum is calculated before the printing is done.) and the Details where Sum({@nottax}, {Acct#}) = 0.

If there are more than one entry per account, you will need an invoice number or something to group on to pair up the tax with the cost.

If you are putting a total, you will have to only add the amounts in where the Sum is zero also or you will add all the Tax on and it will not tally.

 

[ddbrook]

I have all the entries for 40,000 for all utility types(E,G,W). Works fine.
But...My problem is my tax is less than 40,000. and utility type = &quot;X&quot;.

There is only one entry per account over 40,000.

Thanks,
DDBrook

 

[ddbrook]

Ian,
I completed what you recommended.
It does do like you said but I do not have Revenue over 40,000.

This is the code I used in @Limit:
If ({pujhaccd.net} >= 40000 and
{pujhaccd.utility_type} = &quot;E&quot; or
{pujhaccd.utility_type} = &quot;G&quot; or
{pujhaccd.utility_type} = &quot;W&quot; or
{pujhaccd.utility_type} = &quot;X&quot then
true
else
false

My report looks like this:
9000
E Date 500
G Date 7.89
X Date 2.99

Why am I loosing my accounts over 40,000.
Please remember...My tax will be less than 40,000.

Thanks for your help,
DDBrook

 

[basil3legs]

Let me clarify a bit:

First, remove your @Limit formula completely!! May be easiest to start again.

Use the following selection formula:

{yourdatabase.revenue} > 40000 OR {yourdatabase.Util Type} = &quot;X&quot;

This will bring in only Revenues over 40,000 but will also bring in ALL tax figures regardless of their equivalent Revenue size. (you could always bring them all in and use @nottax below to remove the revenues that are too small but this way may be quicker).

Group by Account.

Create a Formula, say 'nottax' equal to:

if {yourdatabase.Util Type} <> &quot;X&quot; then {yourdatabase.Revenue} else 0

This will only have a value for non tax items.

Sum this for the Group which will give the non-tax revenue for the Account. This will either be >40,000 due to our record selection, or 0 if the revenue is <= 40,000 but a tax figure was found.

Put yuor &quot;Acct#,Util type,Month,Revenue&quot; header line in the Page Header (or the group above the account group). Supress the Account Group Header and Footer and set the Details Group to be suppressed if Sum({@nottax}, {yourdatabase.Acct#}) = 0 (This is done through &quot;Format Section...&quot; then Supress(No Drill-Down). Click on the X-2 to the right and enter Sum({@nottax}, {yourdatabase.Acct#}) = 0 [you can select the sum from the report fields above]). If you have decided to bring in all the records, change the suppress formula to Sum({@nottax}, {yourdatabase.Acct#}) <= 40000.

This should give you exactly what you are after.

As stated before, if you want to total these amounts, you will need to exclude accounts when Sum(@nottax) = 0 again.

 

[IanWaterman]

Your formula is not quite right. Try

This is the code I used in @Limit:
If (({pujhaccd.net} >= 40000 and
{pujhaccd.utility_type} = &quot;E&quot or
{pujhaccd.utility_type} = &quot;G&quot; or
{pujhaccd.utility_type} = &quot;W&quot; or
{pujhaccd.utility_type} = &quot;X&quot then
true
else
false

Note I have wrapped the net amount and type E together.

Which version of Crystal are you using ?

Ian

 

[Naith]

Why do you need to wrap the net amount and type E together?

 

[basil3legs]

Ian, I was under the impression he only wanted revenues over 40,000 exept for the Tax (&quot;X&quot which may be less.

In which case he only needs:

{yourdatabase.revenue} >= 40000 OR {yourdatabase.Util Type} = &quot;X&quot;

Also, he has never said what he is doing with the formula, I don't see where he is going to use it!!

P.S. in my previous entry, replace any > 40000 with >= 40000 and any <= 40000 with < 40000. I wasn't sure if he wanted to show 40000 as well (his latest post suggest so) or only values above it as initially stated.

 

[basil3legs]

Or using his database fields:

({pujhaccd.net} >= 40000 OR {pujhaccd.utility_type} = &quot;X&quot

 

[dgillz]

Use a record selectoin formaul as follows:

({Revenue} >= 40000 AND {utility_type} = &quot;E&quot
or {utility_type} = &quot;X&quot

This will give you only type E records greater than 40,000 plus ALL type X records regardless of the value. Then group by acct as Ian Suggested.

Finally, to get rid of the &quot;orpahned&quot; tax records that have no corresponding revenue records (because the revenue is less than 40,000), create a GROUP selection formula:

Count({Revenue},{Acct})>1

This will ensure that ever group by accont has at least 2 records, and that the revenue record is 40,000 or greater. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[basil3legs]

ddbrook, let us know what data you are trying to retrieve. If you are only after the revenue and Tax for Electric (over 40000) then use dgillz selection formula, if you are after all revenues and Tax (over 40000), use mine. Or if the table contanis loads of different types but you are only after &quot;W&quot;, &quot;E&quot;, and &quot;G&quot; (and their associated Tax) use:

({Revenue} >= 40000 AND {utility_type} in [&quot;E&quot;,&quot;G&quot;,&quot;W&quot;])
or {utility_type} = &quot;X&quot

The filter out the orthaned Tax figures using any of the above methods. However, dgillz's method won't work if you have anything which is non-taxable and is over 40,000, i.e. has a Revenue over 40000 but there is no corresponding &quot;X&quot; value.