For those that would love to use your own SQL with parameters but still need to use the report builder in ServiceDesk here is how I figured it out. I will use a basic report for educational purposes. I needed a change management report that would list product, when it was opened, implementation time and status. The kicker was they wanted it grouped by week. The report builder as far as I know will only to days and hours but not weeks of the year. This is easy enough by using datepart in the SQL but then you lose the ability to use parameter in the report. Here is what I did.
SELECT DISTINCT DATEPART(wk, RP.ProcessStarted) as 'week', RP.ReportLogProcessID, RP.ReportProcessID, RP.ProcessStarted, RP.Result, RP.SessionID, RP.ProcessViewerPageID, SD.priority, SD.scheduled_date, SD.change_title, SD.r_pproduct FROM dbo.ReportProcess RP INNER JOIN dbo.ServiceDeskChangeManagement SD WITH (NOLOCK) ON SD.process_id = RP.ReportProcessID
Easy enough. Make sure you include sessionID in you code. This is important.
That's it. Hit me up if you are confused about any part.
Very useful information. And still works for version 8.1 RU3.
Thanks,
QuietLeni,
I had this problem, but it was because there were fields in the table with null values.
Gave an update and the problem was corrected.
Regards,
Guys,
This is great stuff! however, when I try adding the Profile Definition, I get the following:
This happens for ALL SQL Server objects I try this with (Tables and Views). What is going wrong?
I guess, this should work... I got some similar instructions in another post as well for a different request and there also it worked for other users...
http://www.symantec.com/connect/forums/last-posted-date-reporting-service-desk
I would be testing it in sometime next week... will post back if it works for me or not...
Thank you for the advice. Taking the alias out of the query work just fine.
Best Regards
Thinking about it more...Make sure you are not aliasing the column names in the select statement when building the view. That caused one of the issues when I was doing this for the first time. In other words, dont have an AS in the select statement. Use the report builder to alias the column names.
Send me the query you tried to use and I will try to reproduce. I believe I got this error before but I dont recall what it was I did to fix it.
Hello,
I've followed your instructions and all the steps work as described but when I want to execute the report to see the results I get the message workflowprofile (broken) and No data to Display. I've tested this many times and review the steps over and over and I still get no results on my custumized reports. What can I do to make this work?
Thank you in Advanced,
Best Regards.