SQL Chart control - updated

[pregen]

I am attaching a new version of the SQL Chart control.  Don added in some increased time-outs for when you are working with a database with LOTS of histories.  You'll need this control to run the History Summary Classic dashboard he just built.  Here's the SQL string that builds that dashboard.

SELECT C.Contact, C.Company,
CAST(B."CallsAttempted" AS INT) AS 'Calls Attempted',
CAST(B."CallsCompleted" AS INT) AS 'Calls Completed',
CAST(B."MeetingsHeld" AS INT) AS 'Meetings Held',
CAST(B."EmailsSent" AS INT) AS 'E-mails Sent'
From dbo.Contact C
INNER JOIN (
Select A."ContactID",
CAST(SUM(A."CallAttempted") AS INT) AS 'CallsAttempted',
CAST(SUM(A."CallCompleted") AS INT) AS 'CallsCompleted',
CAST(SUM(A."MeetingHeld") AS INT) AS 'MeetingsHeld',
CAST(SUM(A."EmailSent") AS INT) AS 'EmailsSent'
FROM (
SELECT C1.ContactID AS 'ContactID',
CAST(CASE When H.History_Type='Call Attempted' THEN 1 ELSE 0 END AS Int) AS 'CallAttempted',
Cast(CASE When H.History_Type='Call Completed' THEN 1 ELSE 0 END AS Int) AS 'CallCompleted',
Cast(CASE When H.History_Type='Meeting Held' THEN 1 ELSE 0 END AS Int) AS 'MeetingHeld',
Cast(CASE When H.History_Type='E-mail Sent' THEN 1 ELSE 0 END AS Int) AS 'EmailSent'
FROM dbo.Contact C1
INNER JOIN dbo.Contact_History CH ON CH.ContactID=C1.ContactID
INNER JOIN dbo.History H ON H.HistoryID=CH.HistoryID)
AS A
GROUP BY "ContactID")
AS B ON C.ContactID=B.ContactID
WHERE B."CallsAttempted" <> 0 OR B."CallsCompleted" <> 0 OR B."MeetingsHeld" <> 0 OR B."EmailsSent" <> 0
ORDER BY 1,2

As usual, the zip file gets extracted to the program files\act\act for windows\tools folder or the program(86)... folder on a 64 bit machine.

ACTDashboardControls2012SP1-v3.zip ‏239 KB

[daniel_graves]

Thanks, as always, for these controls, Pat!

Have you been successful using these in ACT 2013?  They don't show up for me, in the Dashboard Designer.  Could it be a conflict with another DLL in the Tools folder?

[pregen]

Yes the controls reworked for 2012 work fine. Infact I demo'd them  on my roadshow.  Make sure you have the latest control.

[daniel_graves]

still not working.

I verified I loaded  the ACTDashboardControls2012SP1-v3 from your post above, into Tools

tried on 3 different OS (XP, Vista, Win7) on 3 different workstations

on ACT build 15.0.301 Premium

tried deleting DependentDlls

still no controls show up in Dashboard designer Custom tab (except the Pointer and Datachart)

any troubleshooting ideas?  thx.

anyone else having problems loading these controls?

[pregen]

Daniel, we are in the office today. Give us a call if you want to work on this over the weekend.  We just tested them on 2013 as well and they work fine. I have seen issues where you don't have the right controls that are actually part of Sage's product line.  We just had this with one client.  Turned out he didn't have a complete install and we just had to copy over the controls that ship with Sage that go into the Tools folder.  I got them off of the install image in the ACTWG\program files\actinstalldir\tools folder.  I just copied what was there back into the tools folder under the ACT folders in Program files.  I figured this out by comparing dates on the files in the Tools folder.  His was the only machine that wasn't working so I figured it had to be something with his install.  as it turned out, his whole install was bad due to a bad download image.

Is this not working for you at all anywhere.  We must have this loaded on at least 50 client machines if not more.   ACT installs leave a lot of residue around and cleaning out stuff will make the controls happier.  IN fact it makes a lot of things during an install happier.

[inkey]

Patricia, thanks for your example code.

I'm looking for a way to split a multi-selectable dropdown field, for example ID/Status can be 'customer;friend;supplier' and for another contact 'supplier' or 'prospect;supplier'

How can I count all suppliers?

This is what I have so far:

SELECT
(CASE
When category like '%;%' THEN
SUBSTRING(category,1,CHARINDEX(';',category,1)-1)
ELSE category
END )
FROM  tbl_contact 

But this returns only the first item from the multi-select list.

How can I create a count from the splitted results?

[daniel_graves]

Hey Pat, have you and Don tested these controls on v16?  All OK?