using SQL statement for dashboard control

[rbrust]

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...

[mwood]

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.

[rbrust]

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... 

[alduet]

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

[rbrust]

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>

 

[alduet]

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, 

[alduet]

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.

[alduet]

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

[rbrust]

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...