Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL Chart control - updated

Bronze Contributor
Posts: 1,246
Country: USA

SQL Chart control - updated

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.

 

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Nickel Super Contributor
Posts: 439
Country: USA

Re: SQL Chart control - updated

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?

Daniel Graves . . . DesignR1, LLC . . . Los Angeles . . . 818-780-3201
¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤
Bronze Contributor
Posts: 1,246
Country: USA

Re: SQL Chart control - updated

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

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Nickel Super Contributor
Posts: 439
Country: USA

Re: SQL Chart control - updated

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?

Daniel Graves . . . DesignR1, LLC . . . Los Angeles . . . 818-780-3201
¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤
Bronze Contributor
Posts: 1,246
Country: USA

Re: SQL Chart control - updated

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.

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Copper Contributor
Posts: 134
Country: USA

Re: SQL Chart control - updated

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?

Thanks,
Inge van Gemert
ACT! Certified Consultant
The Netherlands
inge@inkey.net
Highlighted
Nickel Super Contributor
Posts: 439
Country: USA

Re: SQL Chart control - updated

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

Daniel Graves . . . DesignR1, LLC . . . Los Angeles . . . 818-780-3201
¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤