Errors on SQL Commands in Calculated fields

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Errors on SQL Commands in Calculated fields

Postby cjbates » Mon Dec 17, 2012 4:00 pm

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
Attachments
DistinctCount.png
DistinctCount.png (85.6 KiB) Viewed 25634 times
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Errors on SQL Commands in Calculated fields

Postby Bob Cergol » Mon Dec 17, 2012 4:25 pm

Cynthia,

Instead of using Distinct() in your +column formula -- try setting the total type on the total column involved.
Count_Distinct_Total_Type.png
Count_Distinct_Total_Type.png (17.78 KiB) Viewed 25631 times

Bob
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm

Re: Errors on SQL Commands in Calculated fields

Postby cjbates » Mon Dec 17, 2012 4:52 pm

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

Thanks
Cynthia
cjbates
 
Posts: 25
Joined: Thu May 10, 2012 7:46 pm

Re: Errors on SQL Commands in Calculated fields

Postby Bob Cergol » Mon Dec 17, 2012 5:47 pm

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
User avatar
Bob Cergol
Certified Dynamic AI Professional
 
Posts: 359
Joined: Fri Dec 11, 2009 5:48 pm


Return to Technical Support

cron