07-02-2012 02:28 PM
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.
09-30-2012 09:58 PM
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?
10-01-2012 05:59 AM
Yes the controls reworked for 2012 work fine. Infact I demo'd them on my roadshow. Make sure you have the latest control.
10-20-2012 07:43 AM
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?
10-21-2012 07:24 AM
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.
03-01-2013 03:15 PM
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?
08-24-2013 03:31 PM
Hey Pat, have you and Don tested these controls on v16? All OK?