V_QUERY_KPI
Provides support for querying on KPI metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(255) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| DETAILEDDESCRIPTION | nvarchar(max) | yes | Detailed description |
| AUTHOR | nvarchar(500) | yes | Author |
| CONTEXTRECORDTYPENAME | nvarchar(50) | yes | Context record type |
| IMPLEMENTATIONTYPE | nvarchar(3) | yes | Implementation type |
| IMPLEMENTATIONDESCRIPTION | nvarchar(258) | yes | Implementation description |
| CONTEXTRECORDIDPARAMETER | nvarchar(255) | Context record ID parameter | |
| UIFOLDER | nvarchar(255) | UI folder | |
| SECURITYUIFOLDER | nvarchar(255) | Security UI folder | |
| GOALTYPE | nvarchar(7) | yes | Goal type |
| GOALRETRIEVALTYPE | nvarchar(9) | yes | Goal retrieval type |
| DECIMALPLACES | tinyint | Decimal places | |
| USETIMEPRECISION | int | yes | Use time precision |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| KPISPECXML | xml | KPI spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| SPNAME | nvarchar(100) | Procedure name | |
| CREATEPROCEDURESQL | nvarchar(max) | yes | Create procedure sql |
| GOALSPNAME | nvarchar(100) | Goal procedure name | |
| ASSEMBLYNAME | nvarchar(100) | Assembly name | |
| CLASSNAME | nvarchar(150) | Class name | |
| FORMUICOMPONENTTYPE | varchar(16) | Form UI component type | |
| FORMCOMPONENTASSEMBLYNAME | nvarchar(max) | yes | Form component assembly name |
| FORMCOMPONENTCLASSNAME | nvarchar(max) | yes | Form component class name |
| WEBUICOMPONENTTYPE | varchar(17) | Web UI component type | |
| WEBUIMODELASSEMBLYNAME | nvarchar(max) | yes | Web UI model assembly name |
| WEBUIMODELCLASSNAME | nvarchar(max) | yes | Web UI model class name |
| WEBUIEXTERNALRESOURCEURL | nvarchar(max) | yes | Web UI external resource url |
| ID | uniqueidentifier | System record ID | |
| 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 |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| HASSITEFILTER | int | yes | |
| NAMERESOURCEKEY | nvarchar(max) | yes | |
| DESCRIPTIONRESOURCEKEY | nvarchar(max) | yes | |
| CURRENCYFIELDID | nvarchar(max) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:30:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_KPI AS
with xmlnamespaces ('bb_appfx_kpi' as tns, 'bb_appfx_commontypes' as common)
select
K.NAME,
K.DESCRIPTION,
K.SPECXML.value('(tns:KpiSpec/tns:DetailedDescription)[1]', 'nvarchar(max)') as DETAILEDDESCRIPTION,
K.SPECXML.value('(tns:KpiSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as CONTEXTRECORDTYPENAME,
K.IMPLEMENTATIONTYPE,
case K.IMPLEMENTATIONTYPECODE
when 1 then 'SP (' + K.SPNAME + ')'
when 0 then 'CLR (' + K.ASSEMBLYNAME + ', ' + K.CLASSNAME + ')'
end as IMPLEMENTATIONDESCRIPTION,
K.CONTEXTRECORDIDPARAMETER,
K.UIFOLDER,
K.SECURITYUIFOLDER,
K.GOALTYPE,
K.GOALRETRIEVALTYPE,
K.DECIMALPLACES,
coalesce(K.SPECXML.value('(tns:KpiSpec/@UseTimePrecision)[1]', 'bit'), 0) as USETIMEPRECISION,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(K.SPECXML.query('tns:KpiSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(K.SPECXML.query('tns:KpiSpec/common:InstalledProductList')) as INSTALLED,
K.SPECXML as KPISPECXML,
case K.SPECXML.exist('tns:KpiSpec/tns:ResourceFile')
when 1 then
K.SPECXML.value('(tns:KpiSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
K.SPECXML.value('(tns:KpiSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
K.SPNAME,
case K.SPECXML.exist('tns:KpiSpec/tns:SPImplementation/common:CreateProcedureSQL')
when 1 then K.SPECXML.value('(tns:KpiSpec/tns:SPImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
else null
end as CREATEPROCEDURESQL,
K.GOALSPNAME,
K.ASSEMBLYNAME,
K.CLASSNAME,
case K.SPECXML.exist('tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:FormUIComponent')
when 1 then
case K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:FormUIComponent/@FormUIComponentType)[1]', 'nvarchar(15)')
when 'CustomComponent' then 'Custom component'
else 'Default UI'
end
else
'None'
end as FORMUICOMPONENTTYPE,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@AssemblyName)[1]', 'nvarchar(max)') as FORMCOMPONENTASSEMBLYNAME,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@ClassName)[1]', 'nvarchar(max)') as FORMCOMPONENTCLASSNAME,
case K.SPECXML.exist('tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:WebUIComponent')
when 1 then
case K.SPECXML.exist('tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource')
when 1 then 'External resource'
else 'Default UI'
end
else
'None'
end as WEBUICOMPONENTTYPE,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:WebUIComponent/common:UIModel/@AssemblyName)[1]', 'nvarchar(max)') as WEBUIMODELASSEMBLYNAME,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:WebUIComponent/common:UIModel/@ClassName)[1]', 'nvarchar(max)') as WEBUIMODELCLASSNAME,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource/@Url)[1]', 'nvarchar(max)') as WEBUIEXTERNALRESOURCEURL,
K.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
K.DATEADDED,
K.DATECHANGED,
K.TSLONG,
coalesce(K.SPECXML.value('(tns:KpiSpec/@HasSiteFilter)[1]', 'bit'), 0) as HASSITEFILTER,
K.SPECXML.value('(tns:KpiSpec/@NameResourceKey)[1]', 'nvarchar(max)') as NAMERESOURCEKEY,
K.SPECXML.value('(tns:KpiSpec/@DescriptionResourceKey)[1]', 'nvarchar(max)') as DESCRIPTIONRESOURCEKEY,
K.SPECXML.value('(tns:KpiSpec/tns:KpiFormDefinition/@CurrencyFieldID)[1]', 'nvarchar(max)') as CURRENCYFIELDID
/*#EXTENSION*/
from dbo.KPICATALOG as K
left outer join dbo.RECORDTYPE as RT on K.CONTEXTRECORDTYPEID = RT.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = K.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = K.CHANGEDBYID