V_QUERY_KPIINSTANCE
Provides the ability to query KPIs.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(255) | Name | |
| DESCRIPTION | nvarchar(1000) | Description | |
| GOALVALUE | decimal(19, 5) | Goal value | |
| GOALVALUEMONEY | money | yes | Goal value (money) |
| GOALVALUENUMBER | bigint | yes | Goal value (number) |
| GOALVALUEPERCENT | decimal(19, 5) | yes | Goal value (percent) |
| GOALAIMTYPE | varchar(20) | Goal aim type | |
| YELLOWTARGET | decimal(20, 4) | Yellow target | |
| YELLOWTARGETMONEY | money | yes | Yellow target (money) |
| YELLOWTARGETNUMBER | bigint | yes | Yellow target (number) |
| YELLOWTARGETPERCENT | decimal(20, 4) | yes | Yellow target (percent) |
| GREENTARGET | decimal(20, 4) | Green target | |
| GREENTARGETMONEY | money | yes | Green target (money) |
| GREENTARGETNUMBER | bigint | yes | Green target (number) |
| GREENTARGETPERCENT | decimal(20, 4) | yes | Green target (percent) |
| KPICATALOGID | uniqueidentifier | KPI catalog ID | |
| KPICATALOGNAME | nvarchar(255) | yes | KPI type |
| GOALTYPE | nvarchar(7) | yes | Goal type |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| TSLONG | bigint | yes | Timestamp value |
| CONTEXTRECORDTYPE | nvarchar(50) | yes | Context record type |
| CONTEXTRECORDID | nvarchar(100) | Context record ID | |
| CURRENCYID | varchar(36) | yes | Currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:20:43 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_KPIINSTANCE AS
with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
select KPIINSTANCE.ID,
KPIINSTANCE.NAME,
KPIINSTANCE.DESCRIPTION,
KPIINSTANCE.GOALVALUE,
convert(money, case KPICATALOG.GOALTYPECODE
when 0 then KPIINSTANCE.GOALVALUE
else null
end) GOALVALUEMONEY,
convert(bigint, case KPICATALOG.GOALTYPECODE
when 1 then KPIINSTANCE.GOALVALUE
else null
end) GOALVALUENUMBER,
case KPICATALOG.GOALTYPECODE
when 2 then KPIINSTANCE.GOALVALUE
else null
end GOALVALUEPERCENT,
case KPIINSTANCE.GOALAIMTYPECODE
when 0 then 'High values are good'
else 'Low values are good'
end GOALAIMTYPE,
case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDLOWVALUE
else GOALDOWNWARDHIGHVALUE
end YELLOWTARGET,
convert(money, case KPICATALOG.GOALTYPECODE
when 0 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDLOWVALUE
else GOALDOWNWARDHIGHVALUE
end
else null
end) YELLOWTARGETMONEY,
convert(bigint, case KPICATALOG.GOALTYPECODE
when 1 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDLOWVALUE
else GOALDOWNWARDHIGHVALUE
end
else null
end) YELLOWTARGETNUMBER,
case KPICATALOG.GOALTYPECODE
when 2 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDLOWVALUE
else GOALDOWNWARDHIGHVALUE
end
else null
end YELLOWTARGETPERCENT,
case KPIINSTANCE.GOALAIMTYPECODE when 0 then GOALUPWARDMIDVALUE else GOALDOWNWARDMIDVALUE end GREENTARGET,
convert(money, case KPICATALOG.GOALTYPECODE
when 0 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDMIDVALUE
else GOALDOWNWARDMIDVALUE
end
end) GREENTARGETMONEY,
convert(bigint, case KPICATALOG.GOALTYPECODE
when 1 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDMIDVALUE
else GOALDOWNWARDMIDVALUE
end
end) GREENTARGETNUMBER,
case KPICATALOG.GOALTYPECODE
when 2 then case KPIINSTANCE.GOALAIMTYPECODE
when 0 then GOALUPWARDMIDVALUE
else GOALDOWNWARDMIDVALUE
end
end GREENTARGETPERCENT,
KPIINSTANCE.KPICATALOGID,
KPICATALOG.NAME KPICATALOGNAME,
KPICATALOG.GOALTYPE,
KPIINSTANCE.DATEADDED,
KPIINSTANCE.DATECHANGED,
ADDEDBY.APPLICATIONNAME ADDEDBY_APPLICATION,
ADDEDBY.USERNAME ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME CHANGEDBY_USERNAME,
KPIINSTANCE.TSLONG,
RECORDTYPE.NAME [CONTEXTRECORDTYPE],
KPIINSTANCE.CONTEXTRECORDID,
KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CURRENCYID"]/DFI:Value)[1]','varchar(36)') as CURRENCYID
from dbo.KPIINSTANCE
left join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
left join dbo.RECORDTYPE on KPICATALOG.CONTEXTRECORDTYPEID = RECORDTYPE.ID
left join dbo.CHANGEAGENT ADDEDBY on KPIINSTANCE.ADDEDBYID = ADDEDBY.ID
left join dbo.CHANGEAGENT CHANGEDBY on KPIINSTANCE.CHANGEDBYID = CHANGEDBY.ID;