V_QUERY_REPORTDATARETRIEVALOBJECTS

Provides support for querying on report data retrieval objects.

Fields

Field	Field Type	Null	Description
REPORTID	uniqueidentifier		Report ID
OBJECTNAME	nvarchar(max)	yes	Object name
OBJECTTYPE	varchar(20)	yes	Object type

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  8/2/2010 7:06:50 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.7.1654.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REPORTDATARETRIEVALOBJECTS AS



with xmlnamespaces ('bb_appfx_report' as tns, 'bb_appfx_commontypes' as common)
select 
    R.ID as REPORTID,
    report.dataretrievalobjects.value('@ObjectName', 'nvarchar(max)') as OBJECTNAME,
    case report.dataretrievalobjects.value('@ObjectType', 'nvarchar(21)')
        when 'SQLView' then 'View'
        when 'SQLStoredProc' then 'Stored procedure'
        when 'SQLFunction' then 'Function'
        when 'SQLTableValueFunction' then 'Table-value function'
        when 'SQLTable' then 'Table'
    end as OBJECTTYPE

  /*#EXTENSION*/

from dbo.REPORTCATALOG as R
  cross apply R.REPORTSPECXML.nodes('tns:ReportSpec/tns:DataRetrieval/tns:CreateSQL') as report(dataretrievalobjects)