How to get distinct count when there are duplicate records.

[Former Member]

  I have an issue and require expertise advice as I am new to SAP Crystal reporting.

1. I am generating report on total number of Incidents
   resolved by different ITSM tools.
2. There are two tools IT-Assist and QMS.
3. And the Boolean which helps me to filter data is
   column 3 ByITAs.
4. If column 3 is 0 then resolved by QMS else
   IT-Assist.
5. Now the issue is we have reopened Incidents on
   the list coming.
6. As you can see Incident 181 is reflecting
   multiple times this is effecting my total count.

I used a section suppress and suppressed the ID column to
show only distinct records but there is no alterations in count.

How can I get exact count now, kindly help?

[abhilash_kumar · Accepted solution]

Hi Rizwan,

Try this please:

1) Right-click 'Running Total fields' from the Field Explorer > Choose the Incident ID as the 'Field to Summarize' > Choose 'Distinct Count' as the Summary Type > Name this "QMS"

2) Under Evaluate, choose 'Use a formula' and use this code:

{Column3} = 0

3) Under 'Reset' Select Never

4) Create another Running Total field > Name this 'IT' > and use this Evaluate Formula:

{Column3} <> 0

5) Drag and drop both the Running Totals on the Footer and place them beside their respective labels.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you very much, it worked.

Also i want the same to reflect on my cross tab report total.

[abhilash_kumar · Accepted solution]

Use the same Running Total in the 'Summarized fields' section on the Crosstab.

-Abhilash

[Former Member · Accepted solution]

i just changed summarized field to Distinctcount{Incident ID} it worked.

Thank you Abhilash.

This is first time i raised my roadblock here and the response is very quick.