Errors on SQL Commands in Calculated fields

[cjbates]

Hi Bob,

I am trying to get a distinct count of a field and use it in the Totals tab in my report. When I drill down to the details section I am getting:
Dynamic AI can't open the list Indicator Scorecard with Details:


While connecting to the SQL Server database (trackersqlbuildsql), a problem arose.

Dynamic AI is using the view (ID:1160 - SEMAPI to PICWEB Final) with the following SQL defined:

SELECT *
FROM dbo.SEMAPX DAI_
LEFT JOIN dbo.SEMAPXD J2 ON DAI_.INDICATOR_ID = J2.INDICATOR_ID AND DAI_.PROCESSING_DATE = J2.PROCESSING_DATE AND DAI_.RAA_ID = J2.RAA_ID
LEFT JOIN dbo.SEMAPI J3 ON DAI_.INDICATOR_ID = J3.INDICATOR_ID
LEFT JOIN dbo.S8DHRAA J4 ON DAI_.RAA_ID = J4.RAAID
LEFT JOIN dbo.PICTEN J5 ON J2.PICTEN_ID = J5.PICTEN_ID
LEFT JOIN dbo.FOMALL J6 ON J5.ALL_ID = J6.ALL_ID

The issue reported from the database is:

Incorrect syntax near the keyword Distinct.

How can I get a Distinct Count of something?

Thanks
Cynthia

[Bob Cergol]

Cynthia,

Instead of using Distinct() in your +column formula -- try setting the total type on the total column involved.

Count_Distinct_Total_Type.png (17.78 KiB) Viewed 28665 times

Bob

[cjbates]

Duhh...sorry I didnt see that as an option in the pull down. Works great!

Thanks
Cynthia

[Bob Cergol]

Don't feel bad. -- When I first started using Dynamic AI I didn't notice that option either, since all but that one total type are defined as radio button on the list / totals tab where you can set the total type -- except for "count distinct".
Bob