In this post we will add some new features to report 1

• Work Details
• URL to Account name (opens the Account in CRM)
• URL to Case number (opens the Case in CRM)
• Account filter – Use this feature to schedule automatic email delivery of reports with the customer’s data

Download .RDL File

Work Details

Open ListOfCasesByCustomer.rdl file
Place the cursor in the raw where you wish to insert new row
Right click and select Insert Row>Inside Group (either above or below the selected row)

A new text box with four cells is created
Select the whole row (4 cells) and select Merge Cells
Move the mouse over the right upper corner of the text box which opens list of attributes selected from database. Select the description attribute

The description attribute is where QoS-IT stores the work details when case is resolved and closed
You can add text to the left of the attribute. You can modify the properties of the text box, such as adding border or background color.
The end result will look like this image:

Using CRM_URL Parameter to Drill-Through to CRM Account or Case Entities

Right click on the customer text box (textbox3) and select Text Box Properties
Select Action>Go to URL
Click the Expression button (fx)
Set the expression for Hyperlink to:
=Parameters!CRM_URL.Value & "?ID={"& Fields!customerid.Value.tostring() &"}&OTC=1"

Enclose text within quotation marks. Append variable and text using & symbol.
Each CRM entity has unique Object Type Code (OTC). The OTC defines what entity the hyperlink should link to. The OTC for the Account entity is1. The OTC for Case (incident) entity is 112

In a similar way you can open Case using this expression:
=Parameters!CRM_URL.Value & "?ID={"& Fields!incidentid.Value.tostring() &"}&OTC=112"

If you wish to open a case or account from report printed as PDF file, construct the URL for Account:
=Parameters!CRM_URL.Value &"/sfa/accts/edit.aspx?id={"& Fields!customerid.Value.ToString &"}&OTC=1"
and for Case:
=Parameters!CRM_URL.Value & "/cs/cases/edit.aspx?id={"& Fields!incidentid.Value.tostring &"}OTC=112
"
The expression will construct similar URL to the URL of Account in CRM (press F11 to view URL)
(refer to image)

Parameters!CRM_URL.Value  stores the URL of CRM Server http://qositdemo:5555/QOSIT
(In the Report Data window, click to expand the Parameters folder and click to open @CRM_URL. Enter the CRM location in the Default Values option.
The Account ID F50CB514-5B10-DF11-AD09-00155D0BAA06
Is retrieved from Fields!customerid.Value.ToString
(The attribute value is stored at Fields!customerid.Value while the .ToString will convert the value to a string

Account filter

Open the Query Designer
Insert a WHERE command to the SQL Query between the FROM and ORDER BY SQL commands

WHERE (CRMAF_FilteredIncident.customeridname = @CRMAF_FilteredCustomer)

This will add CRMAF (CRM Advance Find) filter to the Customer entity (Filtered Customer is a merged filtered table of Account & Contact)