ServiceDesk

 View Only

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

Feb 25, 2011 05:43 PM

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.

Statistics
0 Favorited
0 Views
7 Files
0 Shares
0 Downloads
Attachment(s)
jpg file
rbi_01.jpg   63 KB   1 version
Uploaded - Feb 25, 2020
jpg file
rbi-02.jpg   37 KB   1 version
Uploaded - Feb 25, 2020
jpg file
rbi-03.jpg   20 KB   1 version
Uploaded - Feb 25, 2020
jpg file
rbi-04.jpg   33 KB   1 version
Uploaded - Feb 25, 2020
jpg file
rbi-05.jpg   68 KB   1 version
Uploaded - Feb 25, 2020
jpg file
rbi-06.jpg   13 KB   1 version
Uploaded - Feb 25, 2020
docx file
Report Builder Instructions.docx   317 KB   1 version
Uploaded - Feb 25, 2020

Tags and Keywords

Comments

Nov 06, 2017 04:29 PM

Very useful information. And still works for version 8.1 RU3.

Thanks,

Apr 02, 2015 03:17 PM

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,

Feb 27, 2015 06:06 AM

Guys,

This is great stuff! however, when I try adding the Profile Definition, I get the following:

2015-02-27_11h04_22.png

This happens for ALL SQL Server objects I try this with (Tables and Views). What is going wrong?

Aug 07, 2012 07:49 AM

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

Nov 07, 2011 04:55 PM

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

Best Regards

Nov 07, 2011 03:15 PM

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.

Nov 07, 2011 02:02 PM

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.

Nov 07, 2011 11:55 AM

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.

Related Entries and Links

No Related Resource entered.