Specify group for group selection formula 2

[-cush-]

How do I limit one group in my group selection formula and not another?

Shouldn't there be a group selection formula for each group?

 

[synapsevampire]

Rather than trying to describe a problem/question with text, consider posting:

Crystal version
Database used
Example data
Expected output

Here's an example of a Group Selection:

thread767-810244

-k

 

[-cush-]

Ok, since no one knows what I am talking about, let me be more descriptive 8(

I have TWO groups. Group #1 uses a Top N sort to show only the top 5 results from that ONE Group. The second group has no such limitations. Anyway I have a bar chart, that shows these top 5 results like:
--------------------------------------------------------
A|===================================================
F|====
L|===
k|===
B|===
---------------------------------------------------------
2k 4k 6k 8k 10k 12k 14k 16k 18k 20k 22k 24k 26k 28k

The problem is that the first one item on the chart had such a bigger total than the rest, that I wanted to give the end user of the report the ablity to eliminate the first X number of results. I created a parameter field called {exceptions} where they could eliminate the first line by inputing 1. Then I put in the Group Selection Formula:
groupnumber <= {?Exceptions}

The attempt was to eliminate the first bar in the chart, so that the other bars could extend across and have a better comparison with each other. So far I am getting nothing back.

So again my question is when you enter something into the "Group Selection Formula", how do you know what GROUP it is effecting!

 

[lbass]

You could approach it this way:

First do a topN in the main report on your summary field. Then create two parameters, both discrete number parameters:
{?topN} and {?exceptions}

For {?exceptions}, create a default of 0, with other numerical options you want to allow.

Then create a formula {@topN}:

if groupnumber in {?exceptions}+1 to {?topN} then {table.grpfield}

Use this as your "on change of" field in the chart. Highlight the field->Order->Specified Order and add ALL possible values of your groupfield. Next choose the "Others" tab and choose "Discard all others."

This will work and is simple to do if you don't have too many group instances to add into the specified order. The point of the specified order is not to order the groups per se, but to eliminate the "Other" category. To order the groups, highlight the field again, choose topN and choose the summary field and ALL--the formula itself will limit the results to the groups selected by parameter.

-LB

 

[-cush-]

Quote: "Highlight the field->Order->Specified Order and add ALL possible values of your groupfield"

Where exactly do you do that?

 

[synapsevampire]

Right click the chart and select Chart Expert.

Select the Data tab

On the right hand side under the On Change of select the field

Below you'll see the Order button activate, click it

-k

 

[-cush-]

There are unfortunately over 800 possible group instances to add into the specified order. In trying it without that step, it only eliminated the group instance name from the chart, but the bar was still there. It's probably going to always be the same group instance, so I just added a boolean parameter field ask them if they want to eliminate it. Then I changed my record selection formula to:

if {?ExcludeTS} then
{cc_data.occ_datetime} in {?DateRange} and not ({cc_data.case_type} in ["TRST","MESS"])
else
{cc_data.occ_datetime} in {?DateRange}

Unfortunatly, now the report iterates through every record in the table, where before it was very fast and only pulled records within the date range. You would think that since my date range parameter is still first, and uses the "AND" operator, that it would still use the index on the date first before trying to compare the other value. I am using Crystal 8.5 and hitting a SQL Server 2000. Is there a way to pass through the SQL statement?

 

[lbass]

Try something like:

{cc_data.occ_datetime} in {?DateRange} and
(
if {?ExcludeTS} = true then
not ({cc_data.case_type} in ["TRST","MESS"])
)
else
(
if {?ExcludeTS} = false then
{cc_data.case_type} <> ""
)

The above uses SV's approach to passing to the SQL statement, and worked when I tested it.

-LB

 

[-cush-]

Wow, ANDing to an if statement. That works great! THANKS

My neural net is running dry trying to keep up with the Synapse Vampire!

Since you say it was SV that came up with the idea, I'll give you both a star.