SSRS Reports

i am following this example online. That creates a report.

but i cannot figure out, how the parameter @DataSourceID is being used.

I use this one to provide the values for the next query's @DataSourceID parameter:

create proc dbo.rpt_ListReportingDataSources

as

 

/*

----------------------------------------------------------------------

Version:    1.0

Date:        14/05/2008

----------------------------------------------------------------------

*/

 

set nocount on

 

select    cast(null as uniqueidentifier) as DataSourceID

    , 'All' as DataSourceName

union all

select    ItemID

    , [Name]

from    ReportServer.dbo.Catalog dscat with (nolock)

where    [Type] = 5

 

go

------------------------

This one lists who last ran each report and when:

create proc dbo.rpt_ITReportsSummary

(

    @DataSourceID uniqueidentifier = null

)

as

 

/*

----------------------------------------------------------------------

Version:    1.2

Date:        14/05/2008

Purpose:    List all reports with who last ran each one and when.

----------------------------------------------------------------------

*/

 

set nocount on

 

select    r.Path as FullName

    , r.Name as [Name]

    , dscat.[Name] as DataSource

    , runcount.RunCount

    , runcount.FirstRun

    , lastrun.LastRun

    , lastrun.LastRunBy

    , mostrun.MostRunBy

    , r.ItemID as ReportID

from    ReportServer.dbo.Catalog r with (nolock)

    inner join ReportServer.dbo.DataSource ds with (nolock) on ds.ItemID = r.ItemID

    inner join ReportServer.dbo.Catalog dscat with (nolock) on dscat.ItemID = ds.Link

    left join

    (

    select    ReportID

        , count(*) as RunCount

        , min(TimeStart) as FirstRun

    from    ReportServer.dbo.ExecutionLog with (nolock)

    where    UserName <> '<domain-name>\davidwimbush'

    group by ReportID

    ) runcount on runcount.ReportID = r.ItemID

    left join

    (

    select    ReportID

        , TimeStart as LastRun

        , UserName as LastRunBy

        , row_number() over (partition by ReportID order by ReportID asc,TimeStart desc) as SeqNo

    from    ReportServer.dbo.ExecutionLog with (nolock)

    where    UserName <> '<domain-name>\davidwimbush'

    ) lastrun on lastrun.ReportID = r.ItemID

    left join

    (

    select    ReportID

        , UserName as MostRunBy

        , count(*) as UserRunCount

        , row_number() over (partition by ReportID order by ReportID asc, count(*) desc) as SeqNo

    from    ReportServer.dbo.ExecutionLog with (nolock)

    where    UserName <> '<domain-name>\davidwimbush'

    group by ReportID

        , UserName

    ) mostrun on mostrun.ReportID = r.ItemID

where    r.Type = 2 -- Report

    and (@DataSourceID is null or ds.Link = @DataSourceID)

    and isnull(lastrun.SeqNo,1) = 1

    and isnull(mostrun.SeqNo,1) = 1

order by r.Name

 

go