Suppress Details - If,Then

[ddnh]

Is there a way to supress details based on an If, Then?

I want to suppress the details based on a paramter entered, but there's a hitch. If the paramter is entered, I only want certain IDs suppressed, not all of them. I know I can put a formula in the Section Expert to suppress the details based on an inserted formula. My report is not grouped by grouped by the version ID, and it can't be. My question is, can I use an If,Then in the formula?

If @Parameter = 3 then I want to suppress the details when VersionID = 1 (version ID can have the value of 1, 2, or 3). It'd be very easy if I wanted to supreess all of the details when @parameter = 3, but how do I only suppress certain VersionIDs?

 

[lbass]

Try this in the x+2 suppression area for the details section:

{?parameter} = 3 and
{table.VersionID} = 1

If you wanted to suppress multiple VersionIDs (let's say 1,2,&3) then change it to:

{?parameter} = 3 and
{table.VersionID} in [1,2,3]

-LB

 

[ddnh]

Gee, I forgot another important part. In some cases, I need only the latest record with the particular VersoinId in the details. I tried doing all of ths in the selection criteria, but it wouldn't allow me to use the Maximum function there. Would that be allowed in the x+2 section?

More details...

CASE 1: If @Paramter1 = 3 and @Parameter2 = 1, then I need the latest record with VersionID = 1.

CASE 2: If @Paramter1 = 3 and @Parameter2 = 2, then I need all records with VersionID = 1.

so it seems as though the suggestion from lbass would work in CASE 2, but not CASE 1.

Any tips?

 

[synapsevampire]

I doubt that you want to suppress the details section, rather a record selection formula will eliminate the rows from the report altogether.

You'll have to spill the beans to get the answer though, please post the table layout, example data and expected output, your text decriptions are helpful, but inconsistent.

I'm guessing that you'll end up using a group selection formula with a max date in one instance, and a record selection formula to eliminate rows in the other, thereby sidestepping the details suppression altogether and eliminating the records from the recordset returned by the query.

-k

 

[lbass]

It's a little unclear what you're looking for since originally you wanted to suppress certain records and now you want to select. Let's say you have no groups, and you want to select all versionIDs, except that you only want to display only the most recent row for versionId = 1. Then you would go to report->edit selection formula->group and enter:

if {?parameter1} = 3 and
{parameter2} = 1 and
{table.versionID} = 1 then
{table.versionID} = maximum(table.versionID}

You could also display only the maximum for versionID = 1 and all records for other versionIDS by using a detail section suppression statement like this:

if {?parameter1} = 3 and
{parameter2} = 1 and
{table.versionID} = 1 then
{table.versionID} <> maximum(table.versionID}

-LB

 

[ddnh]

lbass: I tried your suggestion in the group selection area, but was told that &quot;the result of selection formula must be a boolean.&quot; I also tried the same thing in the record selection area but was told that &quot;this function cannot be used because it must be evaluated later.&quot; (referring to the Maximum function)

YES, synapsevampire, I want to eliminate the rows from the report. I'll spill the beans...

I'm calling a stored procedure with 7 parameters. My issue is with two parameters (Estimate, Change). Each has 3 values. 1 = Last, 2 = ALL, 3 = None. Estimate is associated with VersionID 1, Change is associated with VersionID 3.

If both parameters = 2 then I want to show all records. Here's an example

ProjNum ProjName Version
6 Proj6 EST 3
6 Proj6 EST 2
6 Proj6 EST 1
6 Proj6 CHG 3
6 Proj6 CHG 2
6 Proj6 CHG 1

If Estimate = 1 and Change = 1 then:
ProjNum ProjName Version
6 Proj6 EST 3
6 Proj6 CHG 3

If Estimate = 3 and Change = 2 then:
ProjNum ProjName Version
6 Proj6 CHG 3
6 Proj6 CHG 2
6 Proj6 CHG 1

this can obviously go on for a while with the different combinations of parameter values.

I currently have the following in the record selection expert:

(If {?@Estimate} = 3 and {?@Change} = 2 then {proc.VersionID} = 3) or
(If {?@Estimate} = 2 and {?@Change} = 3 then {proc.VersionID} = 1) or
(If {?@Estimate} = 2 and {?@Change} = 2 then {proc.VersionID} in [1,3])

It works perfectly if either parameter is 2 or 3. The issue is if the parameter is 1 (Last). then I only want the last record to show (the version with the highest number). I can't seem to figure out how to use the Maximum function. I've tried using it in both the group and record select areas. Seems as though the first idea of suppressing the details was all wrong. My apologies for not putting enough info initially. But I'm stuck now on the Max function. any suggestions?

 

[lbass]

Isn't version 3 always the maximum version? And why are you showing &quot;EST&quot; and &quot;CHG&quot; next to the version ID--are they part of the version ID field?

-LB

 

[ddnh]

No, Version 3 isn't always the maximum. It happens to be in the example I used, but it's not so with the real data. The number of versions of estimates/changes don't always match either.

The EST and CHG are in a formula to indicate if the version type is an estimate or a change (VersionID 1 or 3):

If{proc.VersionID}=1 then &quot;EST &quot; + ToText({proc.Version})
else if {proc.VersionID}=3 then &quot;CHG &quot; + ToText({proc.Version})
else &quot;&quot;

 

[lbass]

I would really like to help, but I can't follow what you are trying to do. Maybe I can help indirectly by correcting my previous post, since I noticed some errors in it.

Again, let's say you have no groups, and you want to select all versionIDs, except that you only want to display only the most recent row for versionId = 1. Then you would go to report->edit selection formula->group and enter:

if {?parameter1} = 3 and
{?parameter2} = 1 and
{table.versionID} = 1 then
{table.versionID} = maximum({table.versionID})

...although I'm not sure the values are correct for your purposes.

You could also display only the maximum for versionID = 1 and all records for other versionIDS by using a detail section suppression statement like this:

if {?parameter1} = 3 and
{?parameter2} = 1 and
{table.versionID} = 1 then
{table.versionID} <> maximum({table.versionID})

Looking at the above you can see the inconsistency--the formula says if the value is one then give me the maximum value, which can only be 1. At one point you say you want the maximum ID if the ID = 1, and in another place you say that the maximum you want is the highest number ID. Putting those two thoughts together, then when ID = 1, the maximum ID is 1. Perhaps you really want the most recent record according to a date field if the version ID = 1?

Anyway, there is no reason that I can see that a group select statement like the above would result in a boolean error. The maximum function will work in a suppression statement, too. If you think the maximum function is the answer, then maybe test it out in a simple way in the group select (you can't use it in record select). Just try this:

{table.versionID} = maximum({table.versionID})

If you have {table.versionID} with values 1 to 3, then only the 3's should be returned.

If you have a group and want to find the maximum value within the group, use the following:

{table.versionID} = maximum({table.versionID},{yourgroupfield})

This should return whatever value is the maximum within your particular groups, e.g., it could be 2 for the first group, 3 for the second, 1 for the third, etc.

-LB

 

[ddnh]

lbass,
I see why you're so confused now. I didn't specifically spell out that Version and VersionID are two different fields. I have mentioned it in the post about the EST/CHG and also in the title and last paragraph of my long explanation with sample data, I just didn't specifically spell it out well enough. I apologize for wasting your time, but greatly appreciate your assistance.

If Estimate = 1 then I want the MAX(Version) when VersionID=1.

If Change = 1 then I want the MAX(Version) when VersionID=3.

I tried the following in the X+2 area for details:

If {?@Estimate} = 1 and
{?@Change} = 3 and
{proc.VersionID}=1 then
{proc.Version}<>(Maximum ({proc.Version}, {proc.ProjNum}))

I've also tried:

If {?@Estimate} = 1 and
{?@Change} = 3 then
{proc.Version}<>(Maximum ({proc.Version}, {proc.ProjNum}))

I've also tried the following in the group select area:

If {?@Estimate} = 1 and {?@Change} = 3 then
Maximum({proc.Version}, {proc.ProjNum})

In all 3 instances I get the following error:

The summary/running total field could not be created

 

[lbass]

Thanks for clarifying the version versus VersionID. With regard to the error you keep getting--did you group on {proc.ProjNum}? You would get that error if you had not created the group based on that field.

-LB

 

[ddnh]

Nope, I hadn't grouped by ProjNum. I guess I misread the help file. So here's what I ended up doing.

I grouped by ProjNum and VersionID

In the record selection expert:

(If {?@Estimate} = 3 and {?@Change} = 2 then {proc.VersionID} = 3) or
(If {?@Estimate} = 3 and {?@Change} = 1 then {proc.VersionID} = 3) or
(If {?@Estimate} = 2 and {?@Change} = 3 then {proc.VersionID} = 1) or
(If {?@Estimate} = 2 and {?@Change} = 2 then {proc.VersionID} in [1,3]) or
(If {?@Estimate} = 2 and {?@Change} = 1 then {proc.VersionID} in [1,3]) or
(If {?@Estimate} = 1 and {?@Change} = 3 then {proc.VersionID} = 1) or
(If {?@Estimate} = 1 and {?@Change} = 2 then {proc.VersionID} in [1,3]) or
(If {?@Estimate} = 1 and {?@Change} = 1 then {proc.VersionID} in [1,3])


In the details X+2 suppression section:

({proc.Version} <> Maximum ({proc.Version},{proc.ProjNum})
and {?@Estimate} = 1)

or

({proc.Version} <> Maximum ({proc.Version},{proc.ProjNum})
and {?@Change} = 1)


It took MUCH longer than I thought, but it finally works!!! Thanks for all of your help.

 

[ddnh]

Since I added the ProjNum and VersionId groups my totals (subtotal/grand total in the footer) no longer work correctly. They now add everything in the details whether it is suppressed or not. I only want the totals to include actual data shown on the report.

I have 4 groups total....in this order:
PoolID
StatusID
ProjNum
VersionID

I have the subtotal in the footer for StatusID and the Grand Total in the footer for PoolID. They were in the same place before I added the other groups.

Is there a reason ALL data is included in the totals? I have tried using the Sum function as well as running totals. I've tried formulas similar to the suppression formula, but nothing seems to be working. Any suggestions?

 

[lbass]

If you have suppressed records, you need to use running totals so you can specify the conditions under which you want records counted or totaled. So try using the running total editor, and evaluate based on a formula. There you should enter the opposite of the criteria used for suppression, and then reset either at the group or report level, depending on your needs.

The more usual summaries count suppressed records and non-group selected records because they are still available to the report, though not displayed.

-LB

 

[ddnh]

I realized when I tried it the first time I forgot to reset on group change, and must have used an incorrect formula. So it works now!!! I put in running totals evaluated on the opposite of the suppression formula and reset on change of group.

Thanks so much for all of your help.