Community
Showing results for 
Search instead for 
Do you mean 
Reply

using SQL statement for dashboard control

Nickel Elite Contributor
Posts: 508
Country: USA

using SQL statement for dashboard control

I thought this was a feature of 2010, where you could manually plug in your own SQL statement to create a new chart.  If this is true, how would one go about this?  Thanks...
Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Employee
Posts: 1,163
Country: USA

Re: using SQL statement for dashboard control

Take a look at the Act.Dashboard.Datachart.xml found at Program Files\Act\Act for Windows\Tools. Within the file is a brief explanation of syntax and accepted parameters.
Matthew Wood
Act! SDK Support
Community Moderator
Nickel Elite Contributor
Posts: 508
Country: USA

Re: using SQL statement for dashboard control

That's working - at least in the sense that it's showing the SQL that I've written.  However, I'm having an tough time figuring out what I'm doing incorrect.  Basically, there's a sample statement in that file called Contacts by Country that I'm trying to replicate, but with some Opportunity data. I have people that have numbered their stages using 100's - e.g. 400, 500, 520, 550, 580, 600, etc.  I am trying to rollup the stages by steps of 100, which I've done in SQL.

 

The trick is getting the chart to show correctly. I can get one chart, but the legend is just Value 1, Value 2, etc.  I don't think this is a dashboard issue, rather a SQL issue, and would appreciate any assistance.  I'm going to start messing with using the description possibly - e.g. 400's would *all* have a description of 400, 500's = "500" and so on.

 

Thanks... 

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Nickel Elite Contributor
Posts: 937
Country: USA

Re: using SQL statement for dashboard control

Post your SQL here - if you alias the columns then the chart should show the alias name.

 

 

Nickel Elite Contributor
Posts: 508
Country: USA

Re: using SQL statement for dashboard control

Thanks for any help.  Basically, we changed all the stage "descriptions" to numbers, and a I'm trying to use that to rollup the "stages".  Got it working on my Demo db, but not on any others:

 

 

<item> <key> <string>OppStage Grouping #2</string> </key> <value> <string> SELECT S.DESCRIPTION AS "Stage", COUNT(O.[STAGE]) AS "Count" FROM dbo.OPPORTUNITY AS O INNER JOIN dbo.[STAGE] AS S ON O.STAGEID = S.STAGEID INNER JOIN dbo.[PROCESS] P ON S.PROCESSID = P.PROCESSID WHERE O.RECORD_MANAGER IN ({Record Manager|USERS|%}) GROUP BY S.DESCRIPTION, (CONVERT(INT, ROUND(O.[STAGE]/100, 1))*100) ;0;FALSE</string> </value> </item>

 

 

 

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
Nickel Elite Contributor
Posts: 937
Country: USA

Re: using SQL statement for dashboard control

Thanks Richard,

 

In your statement  the items below in red is what gives my system problems

 

SELECT S.DESCRIPTION AS "Stage", COUNT(O.[STAGE]) AS "Count" 

FROM dbo.OPPORTUNITY AS O INNER JOIN dbo.[STAGE] AS S ON O.STAGEID = S.STAGEID INNER JOIN dbo.[PROCESS] P ON S.PROCESSID = P.PROCESSID 


WHERE O.RECORD_MANAGER IN ({Record Manager|USERS|%})


 GROUP BY S.DESCRIPTION, (CONVERT(INT, ROUND(O.[STAGE]/100, 1))*100)  

 

You don't need it at all actually, when you do a group by you can exclude any return values which are an aggregate function (like sum or count).  You can only specify referenced columns in the group by area of the statement.  

 

I'm not sure I understand about stage being a number - it doesn't need to be a number for the Count function to work in SQL.

 

It looks like you're attempting to do a percentage calculation - you should do this in your select clause if you want it in the SQL return, but depending on your chart selection for rendering you may not need it.  Let me know if you want an example of replacing the COUNT(O.[STAGE]) AS "Count" with a percentage calculation.

 

Regards, 

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: using SQL statement for dashboard control

On second look I think you're looking for this:

 

SELECT O.Stage, COUNT(O.[STAGE]) AS "Count" 

FROM dbo.OPPORTUNITY AS O 

WHERE O.RECORD_MANAGER IN ({Record Manager|USERS|%})

 

 GROUP BY O.Stage 

 

There's no need to join Stage or Process tables as Stage information is surfaced in Opportunity table directly.

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: using SQL statement for dashboard control

[ Edited ]
One more post:  If youre stages are all numerics then you should be able to use a case statement like this to roll up the stage values like you were mentioning earlier in your post.
 
SELECT 'stage' = 
Case
When Cast(O.Stage as int) >=400  and  Cast(O.Stage as int) < 500 Then '400'
When Cast(O.Stage as int) >= 500 and Cast(O.Stage as int) < 600 then '500'
Else 'other'
End,
COUNT(O.[STAGE]) AS "Count" 
FROM dbo.OPPORTUNITY AS O 
WHERE O.RECORD_MANAGER IN ({Record Manager|USERS|%})

 GROUP BY 1
 
 
If any of your stage data in a result set ever comes up with var char values that SQL can't convert to integers then the statement will fail. 
Message Edited by alduet on 06-17-2010 02:18 PM
Nickel Elite Contributor
Posts: 508
Country: USA

Re: using SQL statement for dashboard control

This user's Opp stages are all numeric (or so I think) and are like 100, 200, 300, 310, 350, 400, 420, 480, 500, etc.  I'm trying to rollup an Opp stage "count" based on 100's - so all 4** will be "400", etc.  I'll try the other suggestions like the case statement, but it does work on my Demo database, but I have deleted all the opps that came with the db and manually put in 3 numeric stages...
Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com