Video Screencast Help

Using Custom SQL in ServiceDesk Report Builder and Still Use Parameters!!!

Created: 25 Feb 2011 | 5 comments
Language Translations
jvenuto's picture
+2 2 Votes
Login to vote

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.

  1. Write out the basic SQL in SQL Studio:
    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.

  2. Use the SQL to create it as a view in the Ensemble database. In this case I called the view "test"

  3. In order to make this view visible to the report builder you will need to create a profile definition in the ServiceDesk.
    1. Go to Admin-Data-Lists/Profiles in the process manager C

    2. Click the green plus sign and chose Add Profile Definition (Existing Table)
    3. In the "Add Profile Definition" choose the following
      1. Reference Type-"Workflow Process"
      2. ProfileDefinition Name-"Whatever you want to call it" in my case I called it "Test"
      3. Table Name-"Whatever you called the view in the database" in my case "test"

      4. Hit the "Go" button. Select ID filed will appear. This is where you choose SessionID.
      5. Once SessionID is chosen all of the columns in your report will appear. I chose all of my fields but that isn't always necessary.

      6. Hit "Generate" and you now have exposed the view to the report builder.
      7. Start a new report as you normally would. Choose "Select Data Source" "Default"
      8. Add "Process Management" to the report
      9. At the very bottom of the "Process Management" list on the left hand side you will see "Workflow Profile". Choose that.

      10. In the workflow profile popup window under the "Editor Data" dropdown you should see the "test" profile you created in step C.
      11. Check the "Editable At Runtime" box so your users can choose parameters based on any column in the view.

      12. Build you report

That's it. Hit me up if you are confused about any part.

Comments 5 CommentsJump to latest comment

monvaq's picture

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.

0
Login to vote
jvenuto's picture

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.

0
Login to vote
jvenuto's picture

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.

0
Login to vote
monvaq's picture

Thank you for the advice. Taking the alias out of the query work just fine.

Best Regards

0
Login to vote
snm1502's picture

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...

0
Login to vote