Log In



Forgot Your Password?




Dashboard Documentation for GMPE

Posts 31 | Created 1/12/2011 7:03 AM by stevengraff |  

Is there any documentation for GoldMine's Dashboard functionality beyond what's in the Admin guide? The tutorial is a helpful start, but beyond that, the user is left to do a lot of trial and error "learning."

Posted 1/12/2011 7:03 AM |  

There is no real good documentation on GoldMine Dashboard. Care to write one?

Posted 1/13/2011 6:55 PM |  

Hah! Thanks for that suggestion DJ; sadly, it would be (largely) based on speculation and conjecture rather than truth and insight. So my inquiry was genuinely in the hope that some good doco was somewhere already done.

Posted 1/13/2011 8:45 PM |  

Has anyone tried:

Head on over to GoldMineTrainingResources – http://goldminetrainingresources.com/ to check our our newest product: Mastering Dashboards in GoldMine 9.0. Learn everything you need to know to start creating your own dashboard in GoldMine Premium Edition 9.0.

If it is good then let's hear about it.

Cheers
Bernard

Posted 1/13/2011 8:55 PM |  

Chad Smith has a decent introductory video on the topic on The Goldmine Blog. ( www.the gmblog.com ) Have to start somewhere.

Posted 1/14/2011 11:57 AM |  

Chad's work is excellent... however, what I'm looking for is something a little more in-depth. For example, a neat, comprehensive description of how some of the more complex queries work for the Opportunities dashboards. For example, how to use the radio button and checkbox controls. For example, how to set a default value for a drop-down control.

It's really not too difficult throwing together a simple dashboard, based on the instructions in the Admin guide. This, I have done. The Admin guide, when all's said and done, is somewhat superficial.

What I'm looking for is a technical resource with deeper-dive details.

Posted 1/14/2011 1:25 PM |  

Here are a couple more examples (I imagine) I could do if I knew more about the cell-level biology of dashboards...

A salesrep's quota for the month is $30,000. If he hits his goal I'd like to show his name in bold, brightly glowing fire-engine red.

Some metrics are only interesting if they represent "outliers." So, logically, I'd like to suppress them if they're boring. Any way to programatically show or supress a dashboard element?

Posted 1/14/2011 4:40 PM |  

Although Chad work is good, I believe that it contains just the basics. Come on Steven, we need a real Administrative Guide.

Posted 1/15/2011 8:48 AM |  

Steven,
I could use your salesrep quota example today :-).

Yes different things get different responses from the people who it should matter to. The trick is to focus on who it really matters to.... the boss.

The dashboard functionality will take off in GM when the documentation becomes more fully explained. Too much is left to an "intuitive interface" which doesn't exactly help.

Unroll your detail for the cool example Steven and we can all raise our collective imaginations.

Cheers
Bernard

Posted 1/16/2011 11:10 AM |  

Cool example = several web url components, plus two labels components for the temperatures. The temeprature data comes from an external SQL database.

All this I figured out simply by reading the fine manual (rtfm?). Good examples in there, for getting started; sadly, not for going advanced.

But how would I programmatically have the server room temp appear in red when it's above 76 degrees? How would I have the gen'l room temp appear in blue if it's below 65 degrees? I know FrontRange doesn't (and shouldn't, probably) care about my temperature examples, but the same logic might apply to to sales figures or forecasts.

With some better documentation, and more examples, GoldMine users (and vars) could really catch fire and do some fine wow stuff.

Posted 1/16/2011 11:29 AM |  

The sales user dashboard that comes with GM works with the current log inned user. I am trying to add to it a combo of GM users so when ever I choose a user Ican see his relevant quata data or I can see them all.
I have added the combo with the users list and set the event that will change the data in the table grid but I get error. I have looked on the query that GM runs from behind by using the sql profiler and I have seen that GM doesn't build the query as expected and that's what is causing the error. I have tried various things but only if I use <<~CURRENT_USER>> it works for the current logged in GM user. I have tried <<Activity Owner>> but it gives error when running it though when I am checking the query in the dashboard designer it works ok.

This is the query that should have been built
SELECT CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) as [ENDDATE],
ca.ONDATE as [STARTDATE],
CAST( LTRIM( SUBSTRING( ca.COMPANY, 1, 15 ) ) AS FLOAT ) as [QUOTA],
-- FORECAST
ISNULL( ( SELECT SUM( CAST( ca1.NUMBER1 as float ) ) FROM dbo.CAL ca1
WHERE ca1.RECTYPE = N'S'
AND USERID = N'MICHAEL'
AND ca1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [FORECAST],
-- CLOSED
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM dbo.CONTHIST h1
WHERE h1.RECTYPE = N'S'
AND USERID = N'MICHAEL'
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [CLOSED],
-- LOST
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM dbo.CONTHIST h1
WHERE h1.RECTYPE = N'S U'
AND USERID = N'MICHAEL'
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [LOST],
recid
FROM dbo.CAL ca
WITH (NOLOCK)
WHERE RTRIM(SUBSTRING(ACCOUNTNO, 1,10)) = N'QSMICHAEL ' and ca.rectype=N'Q'

instead of this GM builds the next query

SELECT CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) as [ENDDATE],
ca.ONDATE as [STARTDATE],
CAST( LTRIM( SUBSTRING( ca.COMPANY, 1, 15 ) ) AS FLOAT ) as [QUOTA],
-- FORECAST
ISNULL( ( SELECT SUM( CAST( ca1.NUMBER1 as float ) ) FROM dbo.CAL ca1
WHERE ca1.RECTYPE = N'S'
AND USERID = N' AND ca.USERID = 'MICHAELN' '
AND ca1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [FORECAST],
-- CLOSED
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM dbo.CONTHIST h1
WHERE h1.RECTYPE = N'S'
AND USERID = N' AND ca.USERID = 'MICHAELN' '
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [CLOSED],
-- LOST
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM dbo.CONTHIST h1
WHERE h1.RECTYPE = N'S U'
AND USERID = N' AND ca.USERID = 'MICHAELN' '
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
N'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [LOST],
recid
FROM dbo.CAL ca
WITH (NOLOCK)
WHERE RTRIM(SUBSTRING(ACCOUNTNO, 1,10)) = N'QS AND ca.USERID = 'MICHAELN' ' and ca.rectype=N'Q'

-------------------------------
Here is the query as it looks in the designer

SELECT CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) as [ENDDATE],
ca.ONDATE as [STARTDATE],
CAST( LTRIM( SUBSTRING( ca.COMPANY, 1, 15 ) ) AS FLOAT ) as [QUOTA],
-- FORECAST
ISNULL( ( SELECT SUM( CAST( ca1.NUMBER1 as float ) ) FROM {{owner}}CAL ca1
WHERE ca1.RECTYPE = 'S'
AND USERID = '<<Activity Owner>>'
AND ca1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [FORECAST],
-- CLOSED
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM {{contact_db}}CONTHIST h1
WHERE h1.RECTYPE = 'S'
AND USERID = '<<Activity Owner>>'
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [CLOSED],
-- LOST
ISNULL( ( SELECT SUM( CAST( h1.DURATION as float ) ) FROM {{contact_db}}CONTHIST h1
WHERE h1.RECTYPE = 'S U'
AND USERID = '<<Activity Owner>>'
AND h1.ONDATE between ca.ONDATE
AND CAST( ( SUBSTRING( ca.ACCOUNTNO, 11, 4 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 15, 2 ) +
'-' + SUBSTRING( ca.ACCOUNTNO, 17, 2 ) ) AS DATETIME ) ), 0.0 ) as [LOST],
recid
FROM {{owner}}CAL ca
WITH (NOLOCK)
WHERE RTRIM(SUBSTRING(ACCOUNTNO, 1,10)) = 'QS<<Activity Owner>>' and ca.rectype=N'Q'



Any idea what should I do?

[Updated on 1/17/2011 3:53 AM]

Posted 1/17/2011 1:49 AM |  

There are a couple of of free Dashboard sharing sites:

http://www.DJHunt.US/GuestFTP

or

ftp.DJHunt.US

Login: Guest_FTP_1
Password: *FileTransfer*

as well there is one over at Doug Castell's Contact Review website.

Simply export your dashboard as XML, zip it up, and throw it up to the share site for everyone else. If specific instructions are required, make sure that you include those in the zip file.

Posted 1/17/2011 6:57 AM |  

Here's a new one:

http://www.GoldMineDashboards.com

There's also a wiki spot there for adding your own hints, tips and documentation for dashboarding.

Paul ---

Posted 1/17/2011 7:26 PM |  

Hi Paul,
This is connected to the SalesRabbit team?

Cheers
Bernard

Posted 1/17/2011 8:43 PM |  

Yes, by way of site sponsorship.

Paul ---

Posted 1/17/2011 9:03 PM |  

@shaulbel

<<~CURRENT_USER>> is a direct macro, which circumvents the runtime parameters. <<Activity_Owner>> should be matching the name of the desired value from the 'Runtime Parameter' tab when you are editing the data source.

A simple example:

Pulls the inbox count for a user based on a dropdown.
Runtime Parameters:
AND/OR = AND
Name = USERID
Expression = mb.USERID='<<value>>'

Query:
Select count(*) as count from mailbox mb where (mb.folder = 'X-GM-INBOX' <<USERID>>)

Think of the 'Name' in runtimes as a symbolic link. <<value>.. what is taken from the 'value' of the dropdown. This can also be exchanged with 'text' if desired.

<<USERID>> literally translates into 'AND mb.USERID='MASTER'

Changing the symbolic query of:
Select count(*) as count from mailbox mb where (mb.folder = 'X-GM-INBOX' <<USERID>>)

To the literal:
Select count(*) as count from mailbox mb where (mb.folder = 'X-GM-INBOX' AND mb.USERID='MASTER)

Posted 1/17/2011 9:04 PM |  

@Paul: I would like to test your updated dashboard but I want to know first if it will override the existing default dasboard that comes with GM or any other or will it just will be added?

Posted 1/19/2011 2:30 AM |  

You can adjust the dashboard name and clear the dashboard ID in the XML file to bring it in as a new dashboard. Just be sure to have a backup.

James McCracken

Posted 1/19/2011 3:05 AM |  

@Crispy:
I ave tried wat you have suggested but it doesn't work.
I must emphasize that the query takes the user from the list box as I change it and inserts it into the query but the query is built in a way that it can't be run. As you can see

WHERE ca1.RECTYPE = N'S'
AND USERID = N' AND ca.USERID = 'MICHAELN' '

instead of
WHERE ca1.RECTYPE = N'S'
AND USERID = N'MICHAEL'

It looks more like a bug than something I am doing wrong.

I have created a simple test dashboard gust for testing but the query in the designer is very simple and there it works as expected. Here it is:

SELECT ca.USERID as [Activity_owner], sum(ca.NUMBER1) as [Amount]
FROM <<CAL_TABLE>> ca left join <<OPMGR_TABLE>> op on ca.LOPRECID = op.RECID left join ( <<CONTACT1_TABLE>> c1 inner join <<CONTACT2_TABLE>> c2 on c1.ACCOUNTNO = c2.ACCOUNTNO ) on c1.ACCOUNTNO = ca.ACCOUNTNO {{collation}}
WHERE ( ca.RECTYPE = 'S' ) <<ACTIVITY_USER_GROUP>> <<CONTACT_GROUP_EXPLICIT_FILTER>> <<CONTACT_GROUP_VIA_MACRO>> <<CONTACT_FILTER>> <<CONTACT_ACCOUNT>> <<CONTACT_RECID>> <<OPP_RECID>> <<OPP_USER_GROUP>> <<Activity_Owner>>
GROUP BY ca.USERID

Posted 1/19/2011 6:45 AM |  

Ok. Figured it out after trying lots of ways.
In the Add/edit runtime paramete I have checked the custom radio button
in logical I have choosed EMPTY
in the expression I have typed <<Value>>

I had also to add the ltrim function in the query before the parameter because for some reason I got some extra space before the username so it looks like
AND USERID =ltrim( '<<Activity_Owner>>')

Posted 1/19/2011 2:44 PM |  

It looks like you are not configuring it correctly.

As previously stated, the values from the runtime parameters turn into a literal translation of the fields...

Dropdown value: MICHAELN

Runtime Parameters:
AND/OR = AND
Name = Activity_Owner
Expression = ca.USERID='<<value>>'

WHERE ca1.RECTYPE = N'S'
AND USERID = N' AND ca.USERID = 'MICHAELN' '

The above usually happens because you have your SQL query setup like this:
WHERE ca1.RECTYPE = 'S' AND USERID = <<Activity_Owner>>

The runtime parameter is meant to take care of all of that, it adds it's own AND or OR, the correct way:
WHERE ca1.RECTYPE = 'S' <<Activity_Owner>>

Based on the above parameters, it would translate something like this>

WHERE ca1.RECTYPE = 'S' <<Activity_Owner>>

WHERE ca1.RECTYPE = 'S' AND ca.USERID='<<value>>'

WHERE ca1.RECTYPE = 'S' AND ca.USERID='MICHAELN'

'Empty' is probably reserved for when the parameter is the first where clause where it doesn't need an AND/OR clause.

Posted 1/20/2011 9:41 PM |  

@Crispy:
I did configure it correct now and it's working as I wanted.
My query (as a matter of fact GM query) use the <<Activity Owner>> a couple of time in the query as a parameter and it doesn't have to include the "AND" or the "OR" because they are already written in the base query and in some place it should represent the cal.userid and in another place it should represent conthist.userid and in another place it represents just a string to be contcatanate with another string like "QS" + <<Activity Owner>> which should be written as 'QS<<Activity Owner>>' that there after will give you 'QSMICHAEL' if the value of the dropdown list.
Anothe thing that I have noticed that in most cases of the base datasources (which are realy just queries) GM ad to the where part of the query the phrase "WHERE 1=1" so when in the Add/edit runtime parameter you choose "AND" or "OR" and choose the field from the combo in the field you will ca.USERID='<<value>>', it will add to the base query something like "Where 1=1" <<Activity Owner>> which will later when running will be interpreted as where 1=1 and ca.userid='MICHAEL'
if in the dropdown list the value is 'MICHAEL'
The original query used the macro <<~CURRENT_USER>> which put the logged in username into the query and didn't use the " Add/edit runtime parameter"

I hope I have made it clear now how this window should be used. Anyway, for me it's clear now.

Posted 1/21/2011 7:25 AM |  

Hi team,
I'm trying to figure out how to make the colours in the charts stick. It seems you can specify a colour and save it, then rerun it and the colours change again.

Bernard

Posted 1/27/2011 12:15 AM |  

In general, getting things to "stick" seems to be a pervasive problem.

Posted 1/27/2011 12:29 AM |  

I am trying to pull MS Access data into a dashboard. How do I setup the datasource in Goldmine? There is no documentation! ? Thanks!

Posted 4/3/2012 5:39 PM |  

Best thing I can think of is linking the aaccess file to sql server as a linked server
on the same server that GoldMine database is stored.
I think that there is a way to query access database without linking.
At least it can be done in ssms

Posted 4/3/2012 6:15 PM |  

FrontRange is giving away Chapter 1 - Dashboards of GoldMine Premium - The Definitive Guide and in that document there is a section on Manually Entering Data Sources. That might resolve your issue, and is the best Dashboard resource out there short of purchasing GoldMine Premium - The Definitive Guide which comes with Iain Wicks Dashboard Training Videos to compliment Chapter 1.

Posted 4/4/2012 10:55 AM |  

Thanks. I was told by a sales rep that it can be done and is really easy.... Doesn't look that way.

Posted 4/4/2012 12:03 PM |  

Well, you know the saying about software sales reps -- they don't know when they are lying.
;-)

Also, everyone has their own definition of "easy."

Posted 4/4/2012 1:59 PM |  

DJ, do you have a link to that Chapter 1 offer?

Posted 4/4/2012 3:45 PM |  

I'm sorry Paul, but I do not. You may want to contact your CAM or Paul Peterson, and I'm sure they will help you find it.

@Kathryn Prager - We have a comedian in house.

Posted 4/5/2012 7:10 AM |  
 
 
Subscribe to Dashboard Documentation for GMPE



Hybrid ITSM: Having One Option Isn't An Option
Hybrid ITSM: Having One Option Isn't An Option
Learn about today’s critical ITSM challenges & engage with featured Gartner Analyst Jarod Greene.