V_QUERY_REPORT
Provides support for querying on Report metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| RDLFILENAME | nvarchar(max) | yes | RDL filename |
| FOLDER | nvarchar(max) | yes | Folder |
| LINKEDREPORTID | uniqueidentifier | yes | Linked report |
| HELPKEY | nvarchar(max) | yes | Help key |
| DATASOURCERELATIVEPATH | nvarchar(max) | yes | Data source relative path |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| REPORTSPECXML | xml | Report spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| 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 |
| SPECUINAME | nvarchar(100) | Name override | |
| UINAME | nvarchar(100) | Display name | |
| NAMERESOURCEKEY | nvarchar(max) | yes | |
| MARTKEY | nvarchar(max) | yes | |
| HIDEFROMREPORTEXPLORER | int | 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_REPORT AS
with xmlnamespaces ('bb_appfx_report' as tns, 'bb_appfx_commontypes' as common)
select
R.NAME,
R.DESCRIPTION,
R.REPORTSPECXML.value('(tns:ReportSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
R.REPORTSPECXML.value('(tns:ReportSpec/tns:RDLFileName)[1]', 'nvarchar(max)') as RDLFILENAME,
R.REPORTSPECXML.value('(tns:ReportSpec/tns:Folder)[1]', 'nvarchar(max)') as FOLDER,
R.REPORTSPECXML.value('(tns:ReportSpec/tns:LinkedReport/@BaseReportID)[1]', 'uniqueidentifier') as LINKEDREPORTID,
R.REPORTSPECXML.value('(tns:ReportSpec/tns:HelpKey)[1]', 'nvarchar(max)') as HELPKEY,
R.REPORTSPECXML.value('(tns:ReportSpec/tns:DataSource/tns:DataSourceRelativePath)[1]', 'nvarchar(max)') as DATASOURCERELATIVEPATH,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(R.REPORTSPECXML.query('tns:ReportSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.REPORTSPECXML.query('tns:ReportSpec/common:InstalledProductList')) as INSTALLED,
R.REPORTSPECXML,
case R.REPORTSPECXML.exist('tns:ReportSpec/tns:ResourceFile')
when 1 then
R.REPORTSPECXML.value('(tns:ReportSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
R.REPORTSPECXML.value('(tns:ReportSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
R.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
R.DATEADDED,
R.DATECHANGED,
R.TSLONG,
R.SPECUINAME,
R.UINAME,
R.REPORTSPECXML.value('(tns:ReportSpec/@NameResourceKey)[1]', 'nvarchar(max)') as NAMERESOURCEKEY,
R.REPORTSPECXML.value('(tns:ReportSpec/@MartKey)[1]', 'nvarchar(max)') as MARTKEY,
coalesce(R.REPORTSPECXML.value('(tns:ReportSpec/@HideFromReportExplorer)[1]', 'bit'), 0) as HIDEFROMREPORTEXPLORER
/*#EXTENSION*/
from dbo.REPORTCATALOG as R
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = R.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = R.CHANGEDBYID