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

Replace 'Organization Name' with an Organization name

'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='organization name'
order by sn.leftbound

'datatype' explanation:
1 = standard
2 = currency
3 = percentage

Replace 'Organization name' with an 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 in single inverted commas)


******************************************
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.userid > 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.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 > '2020-01-01'
order by
o.leftbound,
sn.leftbound,
h.actiondate

Replace '2020-01-01' with a date in the same format


***************************************************************
Display the number of time a user has signed on since a date
***************************************************************

select
  u.userid,
  u.username,
  count(*) as num_logins
from
  loginhistory lh
  left join users u on (lh.userid=u.userid)
where
  lh.userid > 0
  and loginhistoryaction = 'login'
  and loginhistoryresult = 'success'
  and loginhistorydate > '2020-01-01'
group by
  u.userid,
  u.username

Replace '2020-01-01' with a date in the same format


***************************************************************
Dates and times users log in since a specified date
***************************************************************

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.userid > 0
  and loginhistorydate > '2020-01-01'

Replace '2020-01-01' with a date in the same format