Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL Chart control for ACT! 2010 using OLEDB2

Nickel Elite Contributor
Posts: 937
Country: USA

SQL Chart control for ACT! 2010 using OLEDB2

If you've modified the Dashboard in ACT! 2010 you'll notice that we've included a data chart component.  This is a new feature based on the datachart component from devnet, but has a few enhancements to streamline the experience for a novice user.  One feature that is missing from this component is the write your own SQL option of the previous devnet component.  

 

I've rebuilt the old version of the data chart component for ACT! 2010 and relabeled it SQL Chart.  SQL Chart component now uses the new OLEDB2 provider in ACT! 2010.

 

 Unzip the contents to tools directory in your ACT! installation directory and restart ACT!.  You should see this as an available component under the Custom section of the toolbox in Dashboard Designer.  

 

Some interesting potential uses here - you can use this component to build and test SQL then modify the ACT.Dashboard.DataChart.xml file so that users can select from your custom selection when creating their dashboard.  

Tuned Listener
Posts: 27
Country: United Kingdom

Re: SQL Chart control for ACT! 2010 using OLEDB2

Fabulous - you have delivered the perfect weapon to an OLE DB newbie! I am trying to create a dashboard that will show completed history for a particular Activity Series (a custom type) as a bar chart for the Companies. I have used your tool to show all histories (COUNT) and get the total number of histories per company using:

Select COUNT(*) From COMPANY_HISTORY GROUP BY COMPANYID

 What I now need to be able to is filter out all Histories except for a certain type, filter by a date range and also show the Company Names or a reference for them (at the moment it just shows 1-15). Any Pointers?

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

You add filters in the SQL statement by Adding { } brackets e.g.

 

{Product Name|STRING|%}

 

It goes:

{Label you want put on the filter dialog | Data type of value from dialog | default value to use}

 

You can see more examples including other datatypes like picklists in the previous controls XML file attached here.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Also I believe if you want to show the company value as the label on your bar chart you'll need:

 

Select COUNT(company_history.companyid), company From COMPANY_HISTORY inner join company on company.companyid=company_history.companyid GROUP BY company

 

Warning - I have not tested this SQL (I'm just free handing it) but this should work.

 

 

Tuned Listener
Posts: 27
Country: United Kingdom

Re: SQL Chart control for ACT! 2010 using OLEDB2

Thanks for this. Is it possible to extract time spent and a specific activity type from the company history?
Tuned Listener
Posts: 27
Country: United Kingdom

Re: SQL Chart control for ACT! 2010 using OLEDB2

RIght - got that bit, now how do I join

Select COUNT(dbo.HISTORY.DURATION) AS "TIME",dbo.HISTORY.CREATE_DATE FROM HISTORY WHERE HISTORYTYPEID=1000

 and 

Select COUNT(COMPANY_HISTORY.COMPANYID),COMPANY From COMPANY_HISTORY,INNER JOIN COMPANY ON COMPANY.COMPANYID=COMPANY_HISTORY.COMPANYIDGROUP BY COMPANY ORDER BY COMPANY

 So that the chart shows the duration by company?

 

Apologies - learning as I go! 

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Select COUNT(COMPANY_HISTORY.COMPANYID), COMPANY From COMPANY_HISTORY

INNER JOIN COMPANY ON COMPANY.COMPANYID=COMPANY_HISTORY.COMPANYID

inner join history on company_history.historyid=history.historyid

 Where history.historytypeid=1000

 

GROUP BY COMPANY ORDER BY COMPANY

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

I believe we expose a nice history type value in history table so you don't ned to use the ID value try this below to see if it works (includes use of filter criteria):

 

 

Select COUNT(COMPANY_HISTORY.COMPANYID), COMPANY From COMPANY_HISTORY

INNER JOIN COMPANY ON COMPANY.COMPANYID=COMPANY_HISTORY.COMPANYID

inner join history on company_history.historyid=history.historyid

 Where history.historytype in {History Type|String|%}

 

GROUP BY COMPANY ORDER BY COMPANY

 

 

Tuned Listener
Posts: 27
Country: United Kingdom

Re: SQL Chart control for ACT! 2010 using OLEDB2

[ Edited ]

Select SUM(DURATION) AS "Time", COMPANY.COMPANY

FROM HISTORY

INNER JOIN COMPANY_HISTORYON HISTORY.HISTORYID=COMPANY_HISTORY.HISTORYID

INNER JOIN COMPANY ON COMPANY.COMPANYID=COMPANY_HISTORY.COMPANYID WHERE HISTORYTYPEID=1003

GROUP BY COMPANY.COMPANY ORDER BY COMPANY.COMPANY

Here is the nut of what I am looking for. I now have a chart of my client companies with the duration of an Activity Type completed shown, in this case Billable time. Last bit is how do I get it to show only items for the current month?

 

Thanks for your help to date Allen. It has been a useful learning exercise. 

Message Edited by bcampbell74 on 04-09-2010 08:33 AM
Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Use the SQL DatePart() function and look for histories with createdates whose month and year = the month and year from the getdate() function.

 

Something like this in your where clause:

 

AND DATEPART(m, HISTORY.CREATEDATE) = DATEPART(m, GetDate()) AND DATEPART(yyyy, HISTORY.CREATEDATE) = DATEPART(yyyy, GetDate()) 

Again freehand untested code - not sure if the column names are correct (I get my SalesLogix and ACT! schema mixed up sometimes).