Count of duplicated rows in a page

[Dampa]

Dear all,

I'm building a report and, for simplicity, I need to take out rows which are not duplicated (in the outcome, not in the query).
Basically, I'm thinking to build a calculated expression "Count" in order to take out all the 1.



I have already tried with the count function without success. Probably I need to manage the settings of this "data item" or of the filter.

As it is easy to get something like this in Excel, I believe it is in Cognos too.
However I'm struggling with it and would appreciate any help/suggestion.

Thank you,
Daniele

[MFGF]

Dear all,

I'm building a report and, for simplicity, I need to take out rows which are not duplicated (in the outcome, not in the query).
Basically, I'm thinking to build a calculated expression "Count" in order to take out all the 1.



I have already tried with the count function without success. Probably I need to manage the settings of this "data item" or of the filter.

As it is easy to get something like this in Excel, I believe it is in Cognos too.
However I'm struggling with it and would appreciate any help/suggestion.

Thank you,
Daniele

Hi Daniele,

The first challenge you'll need to overcome is that your Cognos report will automatically consolidate duplicate rows, so you'll probably never see any duplicates at all. In the properties of the Query you will see an 'Auto Group & Summarize' property which drives this behaviour. You'll need to set this to 'No' as the first step.

Then you can try adding your running counter calculation. I'd probably do this with the expression

running-total(1 for [Item1],[Item2])

The items after the 'for' clause should be all the descriptive items in your data that combine to identify a row.

Finally, add a detail filter with the expression [Your counter calculation] > 1, and set the timing to After Auto Aggregation.

Cheers!

MF.

[Dampa]

Hi MF,

Thanks for the quick reply on this.

The problem is that I can have n different items to count.
Could a report expression help in this case? What I would do in Excel is the countif formula.

Thanks,
Daniele

[MFGF]

Hi MF,

Thanks for the quick reply on this.

The problem is that I can have n different items to count.
Could a report expression help in this case? What I would do in Excel is the countif formula.

Thanks,
Daniele

Hi,

Sorry, I don't quite follow what you're asking here? Wouldn't a row be classed as a duplicate if ALL the descriptive items were the same?

Can you describe what you need?

Thanks.

MF.

[Dampa]

Hi MF,

Yes correct, I consider a raw duplicated if all the descriptive items are the same (column "Main Info" of the shared screenshot)
The problem is that I do not know how many different items I can have, so I cannot list them here > running-total(1 for [Item1],[Item2])

In the example I put just Option A, Option B, Option C, but I can n-different type of options.

I hope it's a bit clearer now.

Thansk,
Daniele

[MFGF]

Hi MF,

Yes correct, I consider a raw duplicated if all the descriptive items are the same (column "Main Info" of the shared screenshot)
The problem is that I do not know how many different items I can have, so I cannot list them here > running-total(1 for [Item1],[Item2])

In the example I put just Option A, Option B, Option C, but I can n-different type of options.

I hope it's a bit clearer now.

Thansk,
Daniele

Hi,

Whatever descriptive items you bring in, you'll need to add them into the FOR clause of the expression. I'm guessing this isn't a one-off requirement?

MF.

[Dampa]

Hi MF,

I cannot know in advance how many descriptive items the query would extract.
I can have even more than 10.000 different items (and they are changing over time), so bringing them into the FOR clause of the expression is not feasible.

Thanks,
Daniele

[MFGF]

Hi MF,

I cannot know in advance how many descriptive items the query would extract.
I can have even more than 10.000 different items (and they are changing over time), so bringing them into the FOR clause of the expression is not feasible.

Thanks,
Daniele

Hi,

This doesn't sound like a normal reporting request (at least not from my prior experience). Can you describe why you are trying to do this? Is it part of a wider requirement which might be achieved a different (and easier) way?

Cheers!

MF.

[Dampa]

Hi MF,

Let me explain better what I need to obtain.

I have a query which extracts a report of thousands of rows.
In the report there are just few lines which are duplicated (due to some additional attributes) and they need to be investigated. On the other hand, if the report does not contain any duplicated rows, no action is required.
For this reason, I would like to obtain only the rows which would be duplicated in the final report.

Taking into consideration the example above/below, I consider a duplicated row if I see more than one value for each "Main Info".
However, as I'm really a newbie, I believe I misunderstood your expression.
I though I should put running-total(1 for [Option A],[Option B],[Option C],[Option-n]) instead of running-total(1 for [Main Info]).

All the attributes are needed in the final report and with the expression running-total(1 for [Main Info]) I'm getting:



Red columns is what I get, green columns is what I'd aiming to get.
I tried to put maximum before the expression but it does not the job (I get 2 also for the third line).

Basically, imaging the screenshot above as a report, I should be able to extract only row 1-2-4-5-6 and not the third one.

Thank you,
Daniele

[MFGF]

Hi MF,

Let me explain better what I need to obtain.

I have a query which extracts a report of thousands of rows.
In the report there are just few lines which are duplicated (due to some additional attributes) and they need to be investigated. On the other hand, if the report does not contain any duplicated rows, no action is required.
For this reason, I would like to obtain only the rows which would be duplicated in the final report.

Taking into consideration the example above/below, I consider a duplicated row if I see more than one value for each "Main Info".
However, as I'm really a newbie, I believe I misunderstood your expression.
I though I should put running-total(1 for [Option A],[Option B],[Option C],[Option-n]) instead of running-total(1 for [Main Info]).

All the attributes are needed in the final report and with the expression running-total(1 for [Main Info]) I'm getting:



Red columns is what I get, green columns is what I'd aiming to get.
I tried to put maximum before the expression but it does not the job (I get 2 also for the third line).

Basically, imaging the screenshot above as a report, I should be able to extract only row 1-2-4-5-6 and not the third one.

Thank you,
Daniele

Hi Daniele,

Firstly, my profuse apologies. I've been using my work laptop to post on here, and (unbeknown to me) it suppresses embedded images in posts. I hadn't realised you were including screenshots until this last post. I think I've been misunderstanding what you need to do. I just jumped onto a different computer and now I see your screenshots.

So. Let's unwind this a little now I understand what you're trying to do (having seen the screenshot you posted at the beginning - doh!) :)
Ignore the part about turning off 'Auto Group and Summarize' - we'll need the rows to be consolidated as normal for this.
As you're only considering the values of [Main Info] to determine duplicates, this is all we need in our expression.

Add a query calculation to your report - total(1 for [Main Info])
Then add a filter to your report (after default aggregation) with the expression [Your calculation above] > 1

So sorry - this should really have been a simple answer, if only I had seen the screenshot you posted. :)

Cheers!

MF.

[Dampa]

Hi MF,

Do not worry, indeed at a certain time I had the impression you were not seeing the screenshots  :)
Although my English might be not really good, the example provided was very simple and I was wondering how to better explain it  :D

I have just used the expression you suggested and works really well.

I thank you so much for the hint and I have also learnt something new for the future > running-total

KR,
Daniele

[MFGF]

Hi MF,

Do not worry, indeed at a certain time I had the impression you were not seeing the screenshots  :)
Although my English might be not really good, the example provided was very simple and I was wondering how to better explain it  :D

I have just used the expression you suggested and works really well.

I thank you so much for the hint and I have also learnt something new for the future > running-total

KR,
Daniele

Hi Daniele,

I'm glad this worked! The old saying "A picture is worth a thousand words" has been proven accurate here :)

MF.