How-to series: Create a custom report (part 4)

In this article, I deal with the reporting drill-through and with the report pre-filtering to display the list of elements that are currently in queues.

The purpose of this report is to display a list of items that are currently “waiting” in the Microsoft CRM queues as there is no real report on these elements.

In this episode, I’ll still create a new report from scratch with Microsoft SQL Server Report Builder 2.0 (see Part 3) but I’ll also deal with the drill-through functionality provided by Reporting Services.

Let’s start!

1. Create a new blank report as explained in the previous “how-to” post (Part 3) and create the data source to your Microsoft CRM implementation
2. Create a special report parameter named ‘CRM_URL’ (the name is very important!) with the following properties:

   

   

   

   

3. Create a new dataset and name it ‘DSQueueItems’; it will execute the following SQL query:

   SELECT
       CRMAF_FilteredQueueItem.objectid,
       CRMAF_FilteredQueueItem.objecttypecode,
       CRMAF_FilteredQueueItem.createdon,
       CRMAF_FilteredQueueItem.createdbyname,
       CRMAF_FilteredQueueItem.enteredon,
       CRMAF_FilteredQueueItem.objecttypecodename,
       CRMAF_FilteredQueueItem.title,
       CRMAF_FilteredQueue.name,
       DATEDIFF(DAY,CRMAF_FilteredQueueItem.createdon,CRMAF_FilteredQueueItem.enteredon) AS [delay]
   FROM FilteredQueueItem AS CRMAF_FilteredQueueItem
   INNER JOIN FilteredQueue as CRMAF_FilteredQueue ON CRMAF_FilteredQueueItem.queueid = CRMAF_FilteredQueue.queueid
   WHERE CRMAF_FilteredQueue.queuetypecode = 1
   ORDER BY CRMAF_FilteredQueueItem.enteredon DESC
   

4. Create a new table on your report, based on the new dataset you’ve just created
5. Arrange the fields and display as you like, and select the field(s) on which you want to implement the drill-through functionality; for example:
   • Right-click on the ‘title’ field and select “Text Box Properties…”

• Go to the “Action” part, click on the “Go to URL” option and click on the expression button (Fx) on the right of the URL field

• In the expression definition, type the following code

Here is the code for you to copy & paste:

= Parameters!CRM_URL.Value & "?ID={"& Fields!objectid.Value.ToString() &"}&OTC=" & Fields!objecttypecode.Value

The purpose of this code is to dynamically generate an hyperlink to the CRM address provided by the CRM_URL parameter, and to dynamically provide the selected object ID and type code so the CRM knows exactly which form to launch: task, phone call, case, etc.

• Deploy your new report and verify that it works like it should do…

• Then click on one of the displayed row’s title to verify that you are brought inside Microsoft CRM…

If everything works, you’re done! You might have to change the design but the important thing is to provide an aid to your customer by knowing what is where since when in the queues