January 14, 2010 at 3:23 pm
Hello experts,
I am using reporting server to know who is running is running which report and which report ran most etc. I have one report that shows number of executions done by user. Now I want to expand that report to also include particular report executed by user.
Previously I was using only executionlog table to get user executions as below.
SELECT
COUNT(E.UserName) AS Total_Exec
, E.UserName
FROM ExecutionLog AS E
GROUP BY E.UserName
But now I also wanted report name so I am joining catalog table with executionlog.
SELECT
E.UserName
, C.Name AS Report_Name
, E.ReportID
, C.ItemID
, E.Parameters
FROM ExecutionLog AS E
LEFT OUTER JOIN dbo.Catalog AS C --INNER JOIN dbo.Catalog AS C
ON C.ItemID = E.ReportID
and then I am calculating number of report executions by user etc. (code is not included because its not relevant to question).
When I am using left outer join I am getting reportids that does not match with itemid of catalog table. That means executionlog has report executions that are not on the server (sounds weird :doze:). By checking parameter field for rows with NULL reportname, itemid I can see that they are legitimate parameters of some reports on server.
Please give me some explanation for this reason. Does modifying and redeploying changes the ItemID in catalog table? If yes then that explains few null rows but not all.
January 15, 2010 at 9:07 am
I just ran a quick test and re-deploying does NOT change the ItemID. Now if you deleted a report and then re-deployed you would likely get this behavior.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 15, 2010 at 9:21 am
Yes Jack you are right. I figured out that today morning. Reason for null entries is that only.
I generally deploy the report from visual studio to Reporting Server in Reports folder. and then I move them in their particular folder. Move operation doesn't allow me to overwrite and gives me "There is at least one item in [folder name] that has the same name as one of the items you are attempting to move to that location" error. So I have to delete the original report and move that and that is causing this problem. and I am not getting actual report executions because of that.
Is the any way to turn on/off file overwrite property for the folder in reporting server?. That will solve my problem.
January 15, 2010 at 9:24 am
Why are you deploying to one folder and then moving to another? Why aren't you deploying directly to the necessary folder?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 15, 2010 at 9:38 am
One reason to do is we have many folders (around 15) so instead of changing TargetReportFolder property everytime I thought deploying everything at one place and moving them will be more convenient. And second reason is I do final testing of reports when it is in "Reports" folder (only developers has an access for that folder) and then if everything looks good, I move them.
If there is no way to change the overwrite property then I guess I have to change the way of deploying. :doze:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply