For our helpdesk/support/ticketing system we have a screen mounted on the wall with key stats (still a work in progress);

tel_dash

90% of the data is pulling from a SQL database- simple stuff! However, we wanted to pull in the number of unanswered e-mails currently sitting in our support mailbox (circled in red).

Retreiving Data From the Inbox

The first challenge was to find a way to pull the number of e-mails. A small .NET app seemed like the way to go (we could then push the data into SQL and pull it into SSRS).

The first attempt used Microsoft.Office.Interop.Outlook;

var app = new Microsoft.Office.Interop.Outlook.Application();
var ns = app.GetNamespace("MAPI");
ns.Logon(null, null, false, false);
Outlook.Recipient recipient;
recipient = ns.CreateRecipient("Tickett Enterprises Support");
var inbox = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
var subfolder = inbox.Folders[1];
var shared = ns.GetSharedDefaultFolder(recipient ,Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
return shared.Items.Count.ToString();

This was straightforward but assumes the application is being run on a machine with outlook installed and configured with the Inbox setup etc. As our e-mail is provided by Exchange (Office 365) the recommended approach appeared to be utilising the Exchange Web Services Managed API (EWS). Writing this code took considerably longer. I got stuck troubleshooting “Autodiscover service couldn’t be located” error… I decided to point the API directly at the EWS URL but that failed with 401 Unauthorized Exception.

I’m not 100% sure of the cause, but it seems that my windows credentials were either not being passed or were not being recognised/interpreted correctly. Microsoft’s sample code uses

service.UseDefaultCredentials = true;

…but changing this to false fixed the 401. My end code was;

            
try
{
  ExchangeService service = new ExchangeService();
  service.Credentials = new WebCredentials("user@domain", "password", "");
  service.UseDefaultCredentials = false;
  service.Url = new Uri("https://pod51047.outlook.com/ews/exchange.asmx");
  Mailbox mb = new Mailbox("support@domain");
  return Folder.Bind(service, new FolderId(WellKnownFolderName.Inbox, mb)).TotalCount.ToString();
}
  catch (Exception ex)
{
  return ex.Message;
}

A colleague then suggested calling this code directly from SSRS instead of pushing/pulling to/from SQL. So… the .NET project was compiled as a class library and the rest should be easy?

Loading/Calling the DLL in SSRS

I expected this to be straightforward, but let’s face it- it never is! Fortunately, google saved the day.

In my development environment (local machine) I had to;

  • Reference my custom dll (in BIDS / Visual Studio, on the report menu, under report properties, references)
  • Copy my custom dll to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies (This is for Visual Studio 2010, your path may differ slightly)
  • Copy any additional dlls your dll references to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies (for me, this was Microsoft.Exchange.WebServices.dll)
  • Modify C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\RSPreviewPolicy.config (under each Codegroup node, set the PermissionSetName to FullTrust)

This had me up and running! I was hoping the same process would be true for deployment to the reporting service, think again! It turns out SSRS 2012 only supports .net 3.5 and earlier (my code was compiled as 4). Fortunately I was able to recompile my dll in .net 3.5 without any drama.

Then roughly the same process in my production environment (ssrs 2012);

  • Copy my custom dll and dependencies to c:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin (for SQL Server Reporting Services 2012)
  • Modify c:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config (under each Codegroup node, set the PermissionSetName to FullTrust)
Advertisements