Record selection for not exact match

[kthacher]

Using CR8, I am reporting sales in $ and units, then calculating an average sales price.

Each sales account represents a class of customers--residential, commercial, etc. There are sub-accounts within the $ sales accounts that represent various types of revenue--accruals for unbilled, adjustments, etc. However, the units do not have sub-accounts.

Reporting on overall sales works fine. But when I want to limit the report to one sub-account, it does not pick up units so can't calc an average price.

How can I report on revenue related to one sub-account such as adjustments and get CR to pick up the related unit sales even though they do not have a sub-account but share the same account?

Example of raw data:

Account Sub-Account Revenue Amount Units Amount
12345 0000 10,000 5,000
12345 0550 500 000


When I tell the Select Expert to retrieve Sub-Account 0550, I would like the report to show the $500 revenue, the 5,000 units, and a price of $0.10

 

[satinsilhouette]

You do not want the revenue for 12345 of 10000 to show up?

 

[lbass]

You should not limit the results to the subaccount in the select expert, since you need the result of other rows. For your calculation, after inserting a group on {table.account}, you could use the following formula in the detail section:

{table.revenueamount}/maximum({table.unitsamount},{table.account})

If you only want to display a specific row, you could either use section expression:

{table.subaccount} <> "0550"

Or you could go to the group expert (report->selection formula->GROUP) and enter:

{table.subaccount} = "0550"

The underlying account data will still be available for the maximum, without being displayed.

-LB

 

[kthacher]

satinsilhouette--No, I don't want the 10,000 to show up when I am reporting on sub-account 0550 only.

lbass--I took the Sub-Account out of Select Expert, changed the price formula as you stated, and put

{table.subaccount} = "0550"

in the group expert.

Now I am getting the entire $10,500 as revenue in the GF and still not picking up units on the detail lines.

So I changed my detail units formula from

{table.units_amount}

to

Maximum({table.units_Amount},{table.account})

I get a big error "A running total cannot refer to a print time formula!"

 

[lbass]

If you only want to display the one subaccount, then you should suppress the group footer, display only the detail section, and use the following formulas in the detail line:

//{@averageprice}:
{table.revenueamount}/maximum({table.unitsamount},{table.account})

//{@units}:
maximum({table.unitsamount},{table.account})

Where and why are you using running totals? First we heard of this...

Are you then trying to summarize the results across groups or something? If so, you need to use variables.

-LB

 

[satinsilhouette]

That makes more sense lbass. That's why I was asking if kthacher was wanting the whole number to show up or only that for the subaccount.

 

[kthacher]

I apologize for not detailing the whole report.

I'm using running totals to pick up a budget amount from a second table. Since there are multiple revenue transaction records per account but only one budget record, the budget amounts were being duplicated for each transaction record. The running total gives me just the one budget amount. The budget amount is not included in the detail lines. It is a component of the Group Footer formulas to calculate the variance from budget.

I have two levels of grouping. Detail lines are hidden since I don't want to see every transaction. Group #2 is Account (representing Large Commercial, Small Commercial, etc.) Group #1 is Customer Type (Total Commercial, Total Residential, etc.)

Here is an expanded look at what the raw data looks like:

Account Sub-Account Revenue Amount Units Amount
12345 0000 10,000 5,000
12345 0550 500 000
54321 0000 11,000 4,000
12345 0000 9,000 000
12345 0000 5,000 000
54321 0000 6,000 000
54321 0550 800 000

Not every revenue record has units (we sell electricity).

Thanks for all your help so far.

 

[lbass]

The same method would work for the budget amount--use maximum({table.budget},{table.account}) and eliminate the running total.

What exactly do you want to see for your results? It sounded as though you wanted to display only one subaccount, and I assumed that was for a specific account. If you want to show multiple subaccounts, please show an example.

-LB

 

[kthacher]

No, I'm not trying to show multiple sub-accounts. Just one sub-account but multiple accounts. Also, each account/sub-account combination could have multiple transactions so I don't want to show detail lines of each transaction.

The result for my above sample data would be:

Account Sub Rev Units Price
12345 550 500 5,000 $0.10
54321 550 800 4,000 $0.20

The revenue, units, and price would also be compared to budget but those are just calculations.

After multiple records such as the above were returned, they are grouped as in my previous post--total account, then total customer type.

I will try getting rid of my running totals (which could be a whole other thread!), using Maximum, and then putting your formulas in my group footers????

 

[lbass]

Then insert a third group on subaccount. You haven't shown any examples where there are multiple 0550 per account, so I'm assuming the revenue amount does need to get summed?

Use the group selection formula I mentioned earlier:

{table.subaccount} = "0550"

Then your formulas become the following (placed in GF#3):

//{@averageprice}:
sum({table.revenueamount},{table.subaccount})/maximum({table.unitsamount},{table.account})

//{@units}:
maximum({table.unitsamount},{table.account})

Then suppress all sections except the group footer #3. Drag the group #2 name into the GF#3.

-LB