Post reply

Reports SSRS

  • March 7, 2011 at 2:58 am

    #235611

    Hi All

    I have a report already that shows the users, which report they ran using the below code:

    SELECT c.Name AS [Report Name], e.InstanceName, e.UserName, e.Status, e.Format, e.Parameters, e.TimeStart, e.TimeEnd, e.TimeDataRetrieval, e.TimeProcessing,

    e.TimeRendering, c.Path

    FROM ExecutionLog AS e INNER JOIN

    Catalog AS c ON e.ReportID = c.ItemID

    WHERE (e.UserName IN (@user))

    ORDER BY e.TimeStart DESC

    However, my issue is that it shows both whether the user has run the report just to view and also it shows if they create a snapshot. What I am trying to do is differentiate between the 2. I need to know which one was a snapshot and which was just the report was ran.

    Can anyone advise?

    Thanks

    R

  • March 7, 2011 at 6:10 am

    #1295277

    Hi there,

    you should get that from the Source-Column in the ExecutionLog-Table.

    When you create a Snapshot you will get an entry in the Source-Column: 4 = History and the Format will be NULL.

    That should do it

  • March 7, 2011 at 6:35 am

    #1295297

    Thanks, that gets me a little further, but it shows me a 4 if the users has either created a snapshot and viewed the snapshot. I just need to know if they have created it not viewed an exsisting one as well.

    Any advise?

  • March 7, 2011 at 6:38 am

    #1295299

    If you view data from a snapshot you get source: 3 - Snapshot and the Format should be "RPL"

  • March 7, 2011 at 7:03 am

    #1295311

    Thats strange Ive tested it a couple of times and it shows me '4' for either creating a snapshot or Viewing an exsiting one.

    But I managed to get round it, as you said the format will be RPL if snapshot is viewed, and it is blank if a new snapshot is created. SO I have done a calculated field and tested it and its working fine.

    ....except I was looking at other users and now I have found someone who has a source 3 on a report they ran, but I have no idea what it means!

  • March 7, 2011 at 7:11 am

    #1295318

    Ah I figured it out. source number 3 is if the report has been exported.

    You dont know by any chance how to show which format it was exported in ??

    Thanks

    R

  • March 7, 2011 at 7:16 am

    #1295322

    Take a look in the definition of the View ExecutionLog2

    CASE(Source)

    WHEN 1 THEN 'Live'

    WHEN 2 THEN 'Cache'

    WHEN 3 THEN 'Snapshot'

    WHEN 4 THEN 'History'

    WHEN 5 THEN 'AdHoc'

    WHEN 6 THEN 'Session'

    WHEN 7 THEN 'Rdce'

    ELSE 'Unknown'

    END AS Source

    However I found this interessting comment in the view ExecutionLog

    CASE([Source])

    WHEN 6 THEN 3

    ELSE [Source]

    END AS Source,

    -- Session source doesn't exist in yukon, mark source as snapshot

    -- for in-session requests

    Maybe thats why you do not see the difference?

    In the ExecutionLog2 (that is "SQL Server 2008" compatable) you do not have the ReportID that you use to join.

    But you can use the ExecutionLogStorage-Table instead or create a second view with the ReportID included.

  • March 7, 2011 at 7:20 am

    #1295324

    The Export-Format should be shown in the Format Column.

    CSV

    EXCEL

    IMAGE

    MHTML

    PDF

    RPL

    WORD

    XML

  • March 7, 2011 at 7:23 am

    #1295326

    hmmm, well ive done it as a calculated field and it seems to be working fine. So ill see how it goes.

    Thanks for the fomat information and thanks for your help. I got a lot further than I was this morning!

    I was going to post another topic, but I thought I would see if you know first. I now need a report to show if any snapshots or subsctiptions have been deleted and who by. Do yo know if that is possible.?

    Thanks

    R

  • March 7, 2011 at 7:31 am

    #1295329

    sorry, no 😉

  • March 7, 2011 at 7:34 am

    #1295333

    Thansk anyway

    R:-D

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply