suppressed if duplicated property ?

[Former Member]

Is there a property i can test for "suppress if duplicated property" ?

i have a report, and i have set "suppress if duplicated" . It works fine for formatting and i only get 1 value, but if i total the column, it includes the values for all metrics(plus the suppressed ones).

i want to just total the "first" value that is displayed.

or some other method ??

[Former Member · Accepted solution]

Suppress if duplicated does just that: it suppresses the record. The record is still on your report - you just can not see it. You could try select distinct in the select expert

[Former Member · Accepted solution]

If Select Disinct Records solves your problem and gets you the correct counts without going the running total route, that's a MUCH easier way to go.

However, Select Distinct Records only works if ALL of the fields in the select statement are the same, not just the fields that are visible in your report. For example, if a field is used in a filter or a formula but is not visible on the report and there may be different values in that field in what look like "duplicate" records, then you'll still have what look like duplicates because one non-visible field is different. If this is the case in your data, you'll need to use the running totals.

-Dell

[Former Member · Accepted solution]

The record does contain different values in some of the fields, so select distinct would not work.

i am playing with running total,

Nearly have it working as :-

{SRMVGMETRICS.LVCOUNT} <> previous({SRMVGMETRICS.LVCOUNT})

but count is off by 1,44.2 and 4.2 (the very 1st record) when you do a manual addition.

p.s. i turned off suppress if duplicated for this example

first column should be the total of 1,5,10,6

2nd column 44.2,148.6,22.1,66.4

3rd column 4.2,6.5,0.7,9.4

{

Volume Group Name lvcount Volume Group (GB) Free Volume Group

data5dg 1.00 44.2 4.2

dumpdg 5.00 148.6 6.5

oracledg 10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

10.00 22.1 0.7

poddg 6.00 66.4 9.4

6.00 66.4 9.4

6.00 66.4 9.4

6.00 66.4 9.4

6.00 66.4 9.4

6.00 66.4 9.4

Total (GB): 21.00 237.09 16.58

}

Edited by: John E Fleming on Sep 21, 2010 12:38 AM

[Former Member · Accepted solution]

turn the suppressed with duplicated back on and see if your numbers work.

[Former Member · Accepted solution]

It's because your first record is unique, when it looks for previous it returns a null and the evaluate fails. If your first record was not unique then it would be OK.

I think you can add

Onfirstrecord or

to the evaulate part of the Running Total to solve problem.

Ian

[Former Member · Accepted solution]

Turning "suppress if duplicated" gives the same result, the first value in the 1st record is not added.

if i try the onfirstrecord :

{

onfirstrecord and {SRMVGMETRICS.LVCOUNT} <> previous({SRMVGMETRICS.LVCOUNT})

}

the value for running toal is zero. Is this formula correct ? maybe i need to vreat another running total formula for the first value, and add both together ?

[Former Member · Accepted solution]

You'll need to use Or instead of And:

onfirstrecord OR {SRMVGMETRICS.LVCOUNT} <> previous({SRMVGMETRICS.LVCOUNT})

-Dell

[Former Member · Accepted solution]

Thanks Dell.

[Former Member]

You're going to have to create either a running total or a formula to handle this. The easiest may be to create a running total which will be set up something like this in the Crystal Running Total Field wizard:

Running Total Name: Give it a meaningful name

Field to Summarize: Select the field that you want to add up

Type of Summary: Sum

Evaluate: Use a formula. You'll want to compare one or more key values against the previous record based on what identifies a "unique" record for you in this instance. For example, if UserID and RowDate fields identify a unique record, here's what you would enter:

{table.UserID} <> previous({table.UserID}) and {table.RowDate} <> previous({table.RowDate})

Note that this formula evaluates to True or False - you do NOT need if...then True else False! - and the field you've selected will only be added to the running total when this formula is True.

Reset:If you want the total at a group level, then reset this on change of group. If you want a grand total, then select never. If you need it for both, you'll need to set up two running totals.

-Dell