V_QUERY_KPIVALUE
Provides the ability to query the values for a given KPI.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| KPIINSTANCEID | uniqueidentifier | KPI instance ID | |
| VALUE | decimal(20, 5) | Value | |
| VALUEMONEY | money | yes | Value (money) |
| VALUENUMBER | bigint | yes | Value (number) |
| VALUEPERCENT | decimal(20, 5) | yes | Value (percent) |
| ASOFDATE | datetime | As-of date | |
| 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 |
| CURRENCYID | varchar(36) | yes | Currency ID |
| CURRENCYIDASGUID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 12:58:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_KPIVALUE AS
with XMLNAMESPACES ('bb_appfx_dataforms' as dfi)
select
KPIINSTANCEHISTORY.ID,
KPIINSTANCEHISTORY.KPIINSTANCEID,
KPIINSTANCEHISTORY.VALUE,
convert(money, case KPICATALOG.GOALTYPECODE when 0 then KPIINSTANCEHISTORY.VALUE else null end) as VALUEMONEY,
convert(bigint, case KPICATALOG.GOALTYPECODE when 1 then KPIINSTANCEHISTORY.VALUE else null end) as VALUENUMBER,
case KPICATALOG.GOALTYPECODE when 0 then KPIINSTANCEHISTORY.VALUE else null end as VALUEPERCENT,
KPIINSTANCEHISTORY.ASOFDATE,
KPIINSTANCEHISTORY.DATEADDED,
KPIINSTANCEHISTORY.DATECHANGED,
ADDEDBY.APPLICATIONNAME ADDEDBY_APPLICATION,
ADDEDBY.USERNAME ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME CHANGEDBY_USERNAME,
KPIINSTANCEHISTORY.TSLONG,
KPIINSTANCE.PARAMETERSXML.value('data(/dfi:DataFormItem/dfi:Values/dfi:fv[@ID="CURRENCYID"]/dfi:Value)[1]', 'varchar(36)') as CURRENCYID,
KPIINSTANCE.PARAMETERSXML.value('data(/dfi:DataFormItem/dfi:Values/dfi:fv[@ID="CURRENCYID"]/dfi:Value)[1]', 'uniqueidentifier') as CURRENCYIDASGUID
from KPIINSTANCEHISTORY
left join dbo.KPIINSTANCE on KPIINSTANCEHISTORY.KPIINSTANCEID = KPIINSTANCE.ID
left join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
left join dbo.CHANGEAGENT ADDEDBY on KPIINSTANCEHISTORY.ADDEDBYID = ADDEDBY.ID
left join dbo.CHANGEAGENT CHANGEDBY on KPIINSTANCEHISTORY.CHANGEDBYID = CHANGEDBY.ID;