October 19, 2016 at 9:42 am
I started with the following BUT IT's NOT GOOD ENOUGH. Please help me!!
This is suppose to show all SSRS reports, and the first and last time run, and who ran it last, with a few exceptions;
Jake and Brandy are the ones who write reports and sometimes run reports, So I tried to exclude them but than I loose a few reports that have only been run by them.
So what I need to do now that I haven't figured how. Is Exclude them UNLESS they were the only ones to run the report.
I'm thinking that it needs another CTE ???
;WITH MaxA AS (
SELECT
ReportName= c.Name collate SQL_Latin1_General_CP1_CI_AS
,RptPath= SUBSTRING(c.path,0,LEN(c.path)+2-CHARINDEX('/',REVERSE(c.path)))
,FirstDateStart = CONVERT(DATE,MIN(l.TimeStart),120)
,LastDateStart= CONVERT(DATE,MAX(l.TimeStart),120)
,LastTimeStart= CONVERT(TIME(0),MAX(l.TimeStart),108)
,NmbrTimesRun= COUNT(c.Name)
,MaxDT= MAX(l.TimeStart)
FROM ReportServer.dbo.Catalog c
LEFT JOIN ReportServer.dbo.ExecutionLog l ON c.itemid=l.reportid
WHERE COALESCE(c.Name collate SQL_Latin1_General_CP1_CI_AS,'') <> ''
AND Intermediate IS NOT NULL
AND RIGHT(COALESCE(l.UserName,'12345678'),(LEN(COALESCE(l.username,'12345678'))-7)) NOT IN ('JakeF','BrandyD')
GROUP BY
c.Name
,c.Path
)
SELECT
ReportName= c.ReportName
,RptPath= c.RptPath
,FirstDateStart = c.FirstDateStart
,LastDateStart= c.LastDateStart
,LastTimeStart= c.LastTimeStart
,LastPersonRun= RIGHT(COALESCE(l.UserName,'12345678'),(LEN(COALESCE(l.username,'12345678'))-7))
,NmbrTimesRun= c.NmbrTimesRun
FROM MaxA c
LEFT JOIN ReportServer.dbo.ExecutionLog l ON c.MaxDT=l.TimeStart
WHERE c.RptPath = '/'
October 19, 2016 at 10:59 am
I think this will work without any CTEs. We use Crystal Reports, so I didn't have a ReportServer database available to check things like the collation and data types and lengths, so you may have to play around with the numbers.
SELECT
ReportName= c.Name collate SQL_Latin1_General_CP1_CI_AS
,RptPath= SUBSTRING(c.path,0,LEN(c.path)+2-CHARINDEX('/',REVERSE(c.path)))
,FirstDateStart = CONVERT(DATE,MIN(l.TimeStart),120)
,LastDateStart= CONVERT(DATE,MAX(l.TimeStart),120)
,LastTimeStart= CONVERT(TIME(0),MAX(l.TimeStart),108)
,NmbrTimesRun= COUNT(c.Name)
,MaxDT= MAX(l.TimeStart)
, LastUserRun= CAST(SUBSTRING(ISNULL(
MAX(CAST(l.TimeStart AS BINARY(8)) + CAST(CASE WHEN l.UserName NOT IN ('DOMAIN\JakeF','DOMAIN\BrandyD') THEN l.UserName END AS BINARY(50)))
,MAX(CAST(l.TimeStart AS BINARY(8)) + CAST(l.UserName AS BINARY(50)))
), 16, 50) AS VARCHAR(100))
FROM ReportServer.dbo.Catalog c
LEFT JOIN ReportServer.dbo.ExecutionLog l ON c.itemid=l.reportid
WHERE c.Name <> '' -- Get rid of the COALESCE. It doesn't gain you anything and it prevents a seek.
AND Intermediate IS NOT NULL
GROUP BY
c.Name
,c.Path
It calculates the last user (not JakeF and BrandyD) by concatenating the time as binary and the username and finding the max of that value and does the same for all users (including JakeF and BrandyD) which it uses if there are no other users who have run the report, then pulls the name out of that binary string. A simple MAX on the username won't work, because that will pull out the records based on alphabetical order of their username instead when they last ran the report.
Since the domain name is a fixed length, there is no need to calculate where it ends in the string. If you're using NVARCHAR instead of VARCHAR, each of the characters will take TWO bytes instead of one, so you'll need to adjust the substring parameters accordingly.
Also, I noticed that you are specifying the collation in several cases. WHY???? You're not comparing them to anything, so why does the collation matter? I think the only place it might matter is the only place where you didn't specify it: the GROUP BY clause.
Finally, the first condition in your WHERE clause is unnecessarily complex and prevents the query optimizer from using a seek. Any comparison except for IS NULL will automatically exclude NULL values. There is no reason to convert it to a value that you then exclude ('').
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2016 at 11:50 am
Thanks very much Drew.
In regards to my collation usage, and the COALESCE in the WHERE clause you are so right !! This was written Months ago and I didn't even think to look at those things.
I am a little confused about your use of BINARY conversion, and when I run it, the results for that column are all BLANKS. If I modify the SUBSTRING param from 16 to 3 I get stuff like this ¥±éQT
Does the datetime need to be converted to varchar first?
October 19, 2016 at 1:49 pm
Budd (10/19/2016)
Thanks very much Drew.In regards to my collation usage, and the COALESCE in the WHERE clause you are so right !! This was written Months ago and I didn't even think to look at those things.
I am a little confused about your use of BINARY conversion, and when I run it, the results for that column are all BLANKS. If I modify the SUBSTRING param from 16 to 3 I get stuff like this ¥±éQT
Does the datetime need to be converted to varchar first?
These are not blanks. The grid view in SSMS is just displaying them as blanks, because the first character in the binary string is 0x00 which it is incorrectly interpreting as the end of the string. If you switch to text mode instead of grid mode, you will see that. (Alternatively, you could convert the datetime to BINARY(6) instead of BINARY(8).)
The reason for including the date in the binary string is because that is what you want to use to determine the last (max) value. By changing the SUBSTRING parameters, you're pulling out the part of the binary string that represents the date (although you only gave five of the six characters). If you convert it to a date rather than VARCHAR you will see that it represents '2016-02-19 15:57:13'. When working with binary strings, you need to know exactly where everything begins and ends to avoid converting data to the wrong data type.
I converted to binary, because that is what was used for the article where I found this technique. I assume that it's because it's faster than converting to VARCHAR. Everything is already stored in binary format, so there's really no conversion necessary to just read the data in the binary format. That's also why I used BINARY(8) rather than BINARY(6).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2016 at 2:12 pm
If you do decide to convert the dates to VARCHAR rather than BINARY, you need to convert it in a format that will sort correctly, such as YYYY-MM-DD hh:mm:ss.
From several tests, it appears that both perform about the same, so it's likely that the author chose the binary string, because he didn't need to worry about which format to use for the conversion.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply