Group specified on non-recurring field

[luciousB]

Hi there,

I am using CR 9 and using MS SQL.

I am having a problem keeping similar records in a group. Example:

I have 2 clients, each with 2 accounts. I need to measure each account to ensure it is within its tolerance range. I have one record per account and the data is similar to the following example:

Account1 | ClientA | 0
Account2 | ClientA | 1
Account3 | ClientB | 0
Account4 | ClientB | 0

The value for each account is evaluated to see if it is within tolerance range and the first group of the report groups by either outside tolerance range or within.

Grouping should be as follows:

Tolerance {"outside" or "within"}
Client Name
<Details> Account data

Assuming that a value greater than zero is outside of a tolerance range, the results should be as follows:

Outside of range
Client A
Account 1
Account 2
Totals for Client A

Within Range
Client B
Account 3
Account 4
Totals for Client B

Even though Account 1 is within tolerance, because a record for the client is out of tolerance and all records must remain under the client, account 1 will fall under the out of tolerance group.

I have tried a number of different methods to group by tolerance result including using manual running totals and I can not seem to get this to work.

I apologize in advance for the long winded question!

 

[lbass]

Try using a SQL expression {%maxtol}:

(select max(AKA.`tolerance`) from Table AKA where
AKA.`Client` = Table.`Client`)

I'm using "tolerance" to mean the field that returns 1 or 0, so you should replace that and "Client" with the exact field names. Replace "Table" with your table name, but leave "AKA" as is, since it represents an alias table name. If you are using record selection criteria, e.g., a date period, this would need to be built into the expression.

You should now be able to group on {%maxtol}, with an inner group on client and accounts as the detail section.

-LB

 

[luciousB]

LB -

Thanks for the quick reply! One thing that I failed to mention is that the "tolerance" field is not a database field but the result of an evaluation made by the report.

As an example, (in my manual running total) I used the following logic to determine whether an account is outside of its tolerance range:

WhilePrintingRecords;
NumberVar outband;
IF {prt_axys_cnt_view.ac_attr28} <> "Y" THEN
(IF {@Equity % of account} < {Targets_.Equity Min} or {Targets_.Equity Max} < {@Equity % of account} THEN
outband := outband + 1 ELSE
IF {@Cash % of Account} < {Targets_.Cash Min} or {Targets_.Cash Max} < {@Cash % of Account} THEN
outband := outband + 1 ELSE
IF {@Fixed % of Account} < {Targets_.Fixed Min} or {Targets_.Fixed Max} < {@Fixed % of Account} THEN
outband := outband + 1 ELSE
0);

Then I used a formula for grouping based on the value of the running total:

stringvar exnote;
IF {prt_axys_cnt_view.ac_attr28} <> "Y" and {@DISPLAY MRT - Outside Band} > 0 THEN
exnote := "All Monitored Accounts - Outside The Target Range" ELSE
IF {prt_axys_cnt_view.ac_attr28} <> "Y" and {@DISPLAY MRT - Outside Band} = 0 THEN
exnote := "All Monitored Accounts - Within The Target Range" ELSE
IF {prt_axys_cnt_view.ac_attr28} = "Y" THEN
exnote := "Excluded Accounts" ELSE
exnote := "Remainder of Accounts";
exnote;

Using this formula provides the desired values but I can not group on it.

I assume a SQL statement like the one you recommended might not work in this example. Any other thoughts?

Thanks again.

 

[lbass]

That was quite a significant omission! Please note that I cannot evaluate whether you still might be able to use a SQL expression because you have used nested formulas without providing their content. Specifically, you should provide the content of:

{@Equity % of account}
{@Cash % of Account}
{@Fixed % of Account}
{@DISPLAY MRT - Outside Band}

It does not look promising, but it might be worth sharing. There might be a way of simplifying.

Please note that you will not be able to group on any formula that executes "whileprintingrecords".

-LB