Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL Chart control for ACT! 2010 using OLEDB2

Tuned Listener
Posts: 27
Country: United Kingdom

Re: SQL Chart control for ACT! 2010 using OLEDB2

Select SUM(DURATION) AS "Time", COMPANY.COMPANYFROM HISTORYINNER JOIN COMPANY_HISTORYON HISTORY.HISTORYID=COMPANY_HISTORY.HISTORYIDINNER JOIN COMPANY ON COMPANY.COMPANYID=COMPANY_HISTORY.COMPANYID WHERE HISTORYTYPEID=1003AND DATEPART(m, HISTORY.CREATE_DATE) = DATEPART(m, GetDate())AND DATEPART(yyyy, HISTORY.CREATE_DATE) = DATEPART(yyyy, GetDate())GROUP BY COMPANY.COMPANY ORDER BY COMPANY.COMPANY

Beautiful! Thanks for all your help on this Allen. Checked against my History Reports and numbers match. I shall refine this to show part hours rather than minutes. What would be nice is to add a threshold i.e marks a target in the column and how  do I change the colour from blue?

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Good suggestion on the conditional formatting feature for the grids for this component.  

 

This dashboard component is fun to develop for, but it's challenging trying to add features and keep the control generic enough to accommodate any user selected chart/graph type.   We do have an improvement in the next release of ACT! where selecting a entity record in your return will navigate you to that record from this control.  

 

In some cases for interactive graphs/charts, or graphs where you want to show additional dimensions it's better to write a custom control using third party graphs (there are a lot of cool free ones out there), if you write your control using OLEDB2 as a data access medium you can reuse a lot of statements used in this control or custom built for this control.

 

I'm glad to help and hope you found this feature useful -  keep the feedback and feature requests coming in.

Bronze Super Contributor
Posts: 1,170
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Allen -

 

  Could you toss out a couple of the names of the (free) third party controls to which you mention to help me get rolling on some further research?

 

  Thank you.

 

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

[ Edited ]

Just an FYI the charts in the existing control are ChartFX controls.  If you already have a dev license for ChartFX they have even more interactive charts in their WPF edition.

 

 

Personally I like the XML - swf charts @

 

http://www.maani.us/xml_charts/

 

This requires Flash, but the interactivity of some of these charts are great for what little implementation you need to get them working.

 

There's a good blog on MSDN which is a good starting point for free chart controls @

 

http://blogs.msdn.com/davidlean/archive/2009/04/20/free-chart-controls-for-net-windows-and-web-handy...

Message Edited by alduet on 04-14-2010 08:40 AM
Bronze Super Contributor
Posts: 1,170
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Bronze Super Contributor
Posts: 1,170
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Oh, I just spent several hours working with the SQL chart control, and I was stumped:  How do you embed a not equal to in the XML template file?

 

XML doesn't like <> and I could not get the SQL to parse with =!...

 

I ended up doing a

C.State &lt; 'NY' OR C.State &gt; 'NY'

 

That doesn't seem like the best way...
Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

I see you're already escaping the characters did you already try &lt;&gt;  ?

 

Bronze Super Contributor
Posts: 1,170
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

I guess really the question is - Is there an escape sequence to represent Not Equal to that passes muster for XML but that the control/provider will parse?

 

Also, is the SQL Having clause one of the ones that provider v2 still doesn't handle?

 

I was attempting to use in my templates and it seems to just get ignored.  Doesn't break the script, but doesn't have any impact either...

New Member
Posts: 3
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Please excuse the intrussion but I can't seem to find anywhere documented the password for accessing the ACT sql database. WHere do you find this information?

 

Given what I see here it seems like you would know.

 

Thanks

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

There's a couple of ways to access the data in ACT!.   One method is to use the ACT! OLEDB2 Provider (if using ACT! 2010 or higher) or the ACT! OLEDB provider for older versions of ACT!.  Setup a connection string to the provider and use the security credentials of an ACT! user to establish a connection.  You can accomplish almost any select operations you'd like against the ACT! data this way.  You cannot execute update or insert statements against this provider.  In general it is not supported to directly manipulate the data inside of an ACT! database using SQL, you can manipulate the data by using the supported SDK which honors the established business rules and data storage in the database.  

 

Were you looking for other info like the SA password for the SQL server instance?