We help IT Professionals succeed at work.

SSRS Reports

aneilg
aneilg asked
on
463 Views
Last Modified: 2016-05-19
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
Comment
Watch Question

Sr. BI  Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
perfect, thanks a lot.
Jason YousefSr. BI  Developer

Commented:
Anytime...let me know if you need more help...
Also look at the native VIEWS that comes in the reportserver DB

[ReportServer].[dbo].[ExecutionLog]
[ReportServer].[dbo].[ExecutionLog2]
[ReportServer].[dbo].[ExecutionLog3]

Author

Commented:
thanks
marrowyungSenior Technical architecture (Data)

Commented:
sorry,

what is that script for ?

I am having SSRS slow from time to time and later on we focus on SQL queries as we can only restart SSRS, anyway to see what make SSRS web server slow ?
marrowyungSenior Technical architecture (Data)

Commented:
this link : http://scrubs.codeplex.com/ said"

"SSRS provides robust logging in the Execution Log, but no management metrics in the box. You'd have to develop your own DW, SSIS procedures and metrics for reporting. Not anymore!
"

so without any development effort, we can't make use of it?

by running the script above, I got this by running v 1.2:

Msg 137, Level 15, State 2, Line 86
Must declare the scalar variable "@DataSourceID".

Open in new window


should I run this one too ?

 create proc dbo.rpt_ITReportsSummary

 (

     @DataSourceID uniqueidentifier = null

 )

 as

  

Open in new window


this one just not part of  v 1.2

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.