Strictly speaking this is not an integration item as the following SQL calls can only be made from within the application. However, they can provide useful information for other activities.

There are two ways SQL calls can be made:

1. Go to Charts & Reports, click on + New Report and then select SQL Console
2. Go to Settings (small cog, lower left of the screen), click on SQL Console

The following calls are allowable:

******************************************
Show users
******************************************
select *
from users
where userid > 0

******************************************
Show groups
******************************************
select *
from usergroups

******************************************
Show groups that a user belongs to
******************************************
select ug.usergroupname
from users u inner join usergroupmemberships ugm on (u.userid=ugm.userid) inner join usergroups ug on (ug.usergroupid=ugm.usergroupid)
where u.username=‘john.smith’

******************************************
Show organizations (in tree order)
******************************************
select o.organizationnodename
from organizationnodes o
order by o.leftbound

******************************************
Show scorecard (in tree order)
******************************************
select sn.scorecardnodeid, sn.scorecardnodename, sn.nodetype, sn.description
from scorecardnodes sn inner join scorecardnodes sn_root on (sn.scorecardrootnodeid=sn_root.scorecardnodeid and sn_root.nodetype=0) inner join organizationnodes o on (o.organizationnodeid=sn_root.organizationnodeid)
where o.organizationnodename=‘Organization Name’
order by sn.leftbound

“nodetype” explanation:
0 = scorecard root
1 = generic node
2 = perspective
3 = objective
4 = metric
5 = linked node

********************************************
Show scorecard’s metrics (in tree order)
********************************************
select sn.scorecardnodeid, sn.scorecardnodename, sn.nodetype, sn.description, sn.datatype, c.calendarname
from scorecardnodes sn inner join scorecardnodes sn_root on (sn.scorecardrootnodeid=sn_root.scorecardnodeid and sn_root.nodetype=0) inner join organizationnodes o on (o.organizationnodeid=sn_root.organizationnodeid) left join calendars c on (sn.calendarid = c.calendarid)
where sn.nodetype=4 and o.organizationnodename=?
order by sn.leftbound

“datatype” explanation:
1 = standard
2 = currency
3 = percentage

Replace “?” with Organization Name

******************************************
Show Scorecard Node Ownership by User
******************************************
select sn_root.scorecardnodename as Scorecard, sn_root.scorecardnodeid as ScorecardID, sn.scorecardnodename as Node, sn.scorecardnodeid as NodeID, u.username, u.userid
from scorecardnodeownerusers snou inner join users u on (snou.userid=u.userid) inner join scorecardnodes sn on (snou.scorecardnodeid = sn.scorecardnodeid) inner join scorecardnodes sn_root on (sn.scorecardrootnodeid = sn_root.scorecardnodeid and sn_root.nodetype=0)
where u.userid = ?

Replace “?” with user ID number

******************************************
Show Scorecard Node Ownership by Scorecard Node
******************************************
select sn_root.scorecardnodename as Scorecard, sn_root.scorecardnodeid as ScorecardID, sn.scorecardnodename as Node, sn.scorecardnodeid as NodeID, u.username, u.userid
from scorecardnodeownerusers snou inner join users u on (snou.userid=u.userid) inner join scorecardnodes sn on (snou.scorecardnodeid = sn.scorecardnodeid) inner join scorecardnodes sn_root on (sn.scorecardrootnodeid = sn_root.scorecardnodeid and sn_root.nodetype=0)
where sn.scorecardnodeid = ?

Replace “?” with scorecard node’s ID number

******************************************
Show Metric Update Assignments by User
******************************************
select sn_root.scorecardnodename as Scorecard, sn_root.scorecardnodeid as ScorecardID, sn.scorecardnodename as Node, sn.scorecardnodeid as NodeID, u.username, u.userid
from metricupdateusers muu inner join users u on (muu.userid=u.userid) inner join scorecardnodes sn on (muu.scorecardnodeid = sn.scorecardnodeid) inner join scorecardnodes sn_root on (sn.scorecardrootnodeid = sn_root.scorecardnodeid and sn_root.nodetype=0)
where u.userid=?

Replace “?” with user ID number

******************************************
Show Metric Update Assignments by Scorecard Node (Metric)
******************************************
select sn_root.scorecardnodename as Scorecard, sn_root.scorecardnodeid as ScorecardID, sn.scorecardnodename as Node, sn.scorecardnodeid as NodeID, u.username, u.userid
from metricupdateusers muu inner join users u on (muu.userid=u.userid) inner join scorecardnodes sn on (muu.scorecardnodeid = sn.scorecardnodeid) inner join scorecardnodes sn_root on (sn.scorecardrootnodeid = sn_root.scorecardnodeid and sn_root.nodetype=0)
where sn.scorecardnodeid=?

Replace “?” with scorecard node (metric) ID number

******************************************
Show Metric Update Assignments by Scorecard
******************************************
select sn_root.scorecardnodename as Scorecard, sn_root.scorecardnodeid as ScorecardID, sn.scorecardnodename as Node, sn.scorecardnodeid as NodeID, u.username, u.userid
from metricupdateusers muu inner join users u on (muu.userid=u.userid) inner join scorecardnodes sn on (muu.scorecardnodeid = sn.scorecardnodeid) inner join scorecardnodes sn_root on (sn.scorecardrootnodeid = sn_root.scorecardnodeid and sn_root.nodetype=0)
where sn_root.scorecardnodeid=?

Replace “?” with scorecard root node’s ID number

******************************************
Show calendars
******************************************
select calendarid, calendarname, standardcalendartype
from calendars
order by sortorder

******************************************
Show calendar periods
******************************************
select cp.calendarperiodid, cp.startdate, cp.enddate, cp.periodalias, cp.calendarid
from calendarperiods cp inner join calendars c on (c.calendarid = cp.calendarid)
where c.calendarname=’?’
order by cp.startdate

Replace “?” with calendar name (ex: Monthly)

******************************************
Show User Login History
******************************************
select lh.loginhistorydate, lh.loginhistoryip, lh.loginhistoryhost, lh.loginhistoryaction, lh.loginhistoryresult, lh.userid, u.username
from loginhistory lh left join users u on (lh.userid=u.userid)
where lh.user > 0

**************************************************************
Display Actual Values for particular metric
*************************************************************
select o.organizationnodename as Organization, sn_root.scorecardnodename as Scorecard, sn_m.scorecardnodeid as MetricID, sn_m.scorecardnodename as Metric, t1.value as Value1, cp1.periodalias, c.calendarname
from organizationnodes o inner join scorecardnodes sn_root on (o.organizationnodeid=sn_root.organizationnodeid and sn_root.nodetype=0) inner join scorecardnodes sn_m on (sn_m.scorecardrootnodeid=sn_root.scorecardnodeid and sn_m.nodetype=4) inner join calendars c on (sn_m.calendarid=c.calendarid) inner join calendarperiods cp1 on (cp1.calendarid=c.calendarid) inner join timeseriespoints t1 on (sn_m.seriesid = t1.seriesid and t1.valuedate = cp1.enddate)
where sn_m.scorecardnodeid=?
order by o.leftbound, sn_m.leftbound, cp1.startdate

Replace “?” with scorecard node (metric) ID number

***************************************************************
Display all Actual and Threshold Data for a particular metric
***************************************************************
select sn.scorecardnodeid, sn.scorecardnodename, t.valuedate, st.name as threshold_name, t.value
from scorecardnodes sn inner join series s on (sn.scorecardnodeid = s.scorecardnodeid) left join scoringthresholds st on (s.scoringthresholdid=st.scoringthresholdid) inner join timeseriespoints t on (t.seriesid = s.seriesid)
where sn.scorecardnodeid=1613
order by sn.leftbound, t.valuedate, st.sortorder

Replace “?” with scorecard node (metric) ID number

Explanation:
- Records that have (blank) for “threshold_name” are actual values
- Records that have (blank) for “valuedate” are DEFAULT threshold values

***************************************************************
Display all Initiative data for an organization
***************************************************************
select i2.type, i2.title, i2.startdate, i2.enddate, i2.completiondate, i2.archivedate, i2.budget, i2.expended, i2.percentcomplete, i2.description
from organizationnodes o inner join initiatives i1 on ( i1.organizationnodeid = o.organizationnodeid and i1.initiativeid = i1.initiativerootnodeid and i1.leftbound = 1 ) inner join initiatives i2 on (i2.initiativerootnodeid=i1.initiativeid)
where o.organizationnodeid = ?
order by i1.sortorder, i2.leftbound

Replace “?” with an organization id

***************************************************************
Audit History of Measures
***************************************************************
select
o.organizationnodename as org_name,
h.objectid as measure_id,
h.objectname1 as measure_name,
h.actiondate as action_date,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(h.actiondescriptionkey,“common.deleted”,“Deleted”),“common.moved”,“Moved”),“common.changed”,“Changed”),“common.created”,“Created”),“Changed.defaultThreshold”,“Changed Default Threshold”),”“,”“),“scorecards.overview.owner.added”,“Added Owner”),“scorecards.overview.owner.removed”,“Removed Owner”),“scorecards.overview.updater.added”,“Added Updater”),“scorecards.overview.updater.removed”,“Removed Updater”) as action,
u.username as user
from
history h
inner join scorecardnodes sn on (h.objectid=sn.scorecardnodeid and sn.nodetype=4 and h.objecttype=‘SN’)
inner join scorecardnodes snroot on (sn.scorecardrootnodeid=snroot.scorecardnodeid and snroot.nodetype=0)
inner join organizationnodes o on (o.organizationnodeid=snroot.organizationnodeid)
left join users u on (h.modifieruserid=u.userid)
where
h.objecttype=‘SN’
and h.actiondate > ’2018-01-01’
order by
o.leftbound,
sn.leftbound,
h.actiondate

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment