troubleshooting Question

SSRS Reports

Avatar of aneilg
aneilgFlag for United Kingdom of Great Britain and Northern Ireland asked on
SSRS
6 Comments1 Solution465 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Jason Yousef
Sr. BI Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros