Save & Send from Excel issues with Outlook add-in

Add-ins are a great way to add custom functionality to Outlook. I have found that in certain circumstances they can introduce problems that are frustrating. A prime example of this occurs during the following scenario:
• There is add-in loaded in Outlook 2010 that adds a button to the email ribbon of the new message window
• A workbook that is opened yet not saved in Excel is then sent to an email recipient using the File –> Save & Send –> Send Using E-mail –> Send as Attachment
• The email is sent to a recipient
• Close the workbook without pressing Save
• The dialog prompting you to Save the Excel workbook briefly appears on the screen but then is hidden behind the window
• The only way to exit Excel is to close Outlook which brings the Save dialog to the foreground

This occurs even with the most basic add-in created using the Visual Studio 2010 wizard.

1

I have not been able to find a quick fix for this problem and instead have to export the code from the ribbon designer into XML, add the appropriate methods, then re-compile. To do this perform the following steps:

Open up the ribbon designer, right click and choose ‘Export Ribbon to XML’

2

This will create two new files Ribbon.cs and Ribbon.xml. Some instructions are generated at the top of Ribbon.cs which I will summarise below. It should be noted that if you have multiple ribbons in your solution, the next ribbon you click ‘Export Ribbon to XML’ on will only generate an XML file – you need to manually add code to the existing Ribbon.cs file. The code is documented below.

The first thing I tend to do is rename my Ribbon.cs and Ribbon.xml files to be something more meaningful. Visual Studio will prompt you to update the code to reflect this. In the screenshot below I changed Ribbon.cs to SharedRibbonCode.cs and Ribbon.xml to EmailRibbon.xml.

3

Now as per the generated instructions in the cs file, copy the following code to either ThisAddin, ThisWorkbook or ThisDocument. Obviously in this case I copied it to ThisAddin.cs

protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
   return new SharedRibbonCode();
}

In the SharedRibbonCode.cs file ensure the following function appears like so:

public string GetCustomUI(string ribbonID)
{
  return GetResourceText("OutlookAddinProblem.EmailRibbon.xml");
}

If you have more than one ribbon in your solution, change the above code to be a switch/case statement which returns the appropriate XML file based on the current window. For example:

public string GetCustomUI(string ribbonID)
{
      string ribbonXML = String.Empty;

      switch (ribbonID)
      {
        case "Microsoft.Outlook.Mail.Compose":
          ribbonXML = GetResourceText("OutlookAddinProblem.EmailRibbon.xml ");
          break;
        case "Microsoft.Outlook.Explorer":
          ribbonXML = GetResourceText("OutlookAddinProblem.MainWindowRibbon.xml ");
          break;
        case "Microsoft.Outlook.Appointment":
          ribbonXML = GetResourceText("OutlookAddinProblem.AppointmentRibbon.xml ");
          break;

      }
      return ribbonXML;
 }

Next you have to add callback functions in place of your events for the ribbon. As you can see from the screenshot at the top of this post, I have a single button. I right-click on the ComposeEmailRibbon.cs file and select ‘View Code’. The existing code is as follows:

private void button1_Click(object sender, RibbonControlEventArgs e)
{
  System.Windows.Forms.MessageBox.Show("Button Clicked!");
}

Copy that code to SharedRibbonCode.cs in the #region Ribbon Callbacks and change it slightly so it looks like so:

public void button1_Click(Office.IRibbonControl control)
{
  System.Windows.Forms.MessageBox.Show("Button Clicked!");
}

Note that if you have multiple ribbons in your project, you will need to ensure the controls have unique names across the ribbons.

Now that you have transferred the code, remove/delete the old ribbon designer code from the project. In this case I remove the following items:

4

Compile and install your add-in. The mentioned problem no longer exists!

Optional: Adding images. Using the XML file for your ribbon limits the visual design aspects. To add an image, add a getimage property to your XML:

<button id="button1" onAction="button1_Click" label="Click Me" showImage="false" getImage="GetImage" />

This refers to a GetImage function in the C# code:

public Bitmap GetImage(Microsoft.Office.Core.IRibbonControl control)
{
  switch (control.Id)
  {
    case " button1":
      return new Bitmap(Properties.Resources.Image1);
    case " button2":
      return new Bitmap(Properties.Resources.Image2);
    case " button3":
      return new Bitmap(Properties.Resources.Image3);
  }
  return null;
}
Advertisements
Posted in Windows Apps | Leave a comment

Excel 2007 vs 2010 SSAS Cube Functions

Sometimes a function that works perfectly fine in one environment totally fails in another. I was recently building a complex set of cube reports in Excel that caused me no end of grief. By complex I mean using a formula such as a CubeSet containing a TopCount that contains a Sum with a collection of dimensions and dimension sets.

I found that this would work perfectly fine under Excel 2010, and work up to a point in Excel 2007. Now I know Excel 2007 is getting a bit long in the tooth what with the 2013 preview available, but it is still prevalent in the corporate world. Microsoft did a lot of work with the MDX behind the scenes between the two versions, and although they contain much the same in terms of SSAS functions, they work quite differently.

The complex formulas mentioned above are dynamic. By that I mean based upon what the user selects in a collection of combo boxes, the content of the formulas (and consequently the dimensions selections used) change. For example, the user selects a different month from the combo box and the cube formula changes. What I found is that although this works perfectly fine under 2010, it often did not in 2007.

The solution is a simple one and not too much of a workaround. Simply build the full parameter string for your final function in another cell, then pass that cell to the function. For example:

Cell A1 contains =”TopCount([ADimension].Children, 5, Sum(” & Z1 & Q5 & AnotherRange & “), [YourMeasure].[Measure]))”

Cell A2 contains = CubeSet(CubeName, A1, “A Caption”)

That’s it!

What didn’t work in Excel 2007, yet did in 2010 was this:

Cell A2 contains = CubeSet(CubeName, TopCount([ADimension].Children, 5, Sum(” & Z1 & Q5 & AnotherRange & “), [YourMeasure].[Measure])), “A Caption”)

Posted in Business Intelligence | Leave a comment

SSRS Permissions

So you have just done a fresh install of SSRS 2008 R2. When you try to browse to http://yourServerName/reports you recieve this following message:

“YourUserName does not have the required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

However if your run your web browser as an Administrator (Right click on IE and choose “Run as Administrator”), go to the same URL with the same user account, things work fine.

To resolve this all you have to do is set the permissions on the Home/root folder of the report server.

While running in admin mode, just click on the Folder Settings, New Role Assignment, and grant at least Reader permissions. Depending on your situation it might be advisable to grant the Reader permissions to Domain Users.

Posted in Business Intelligence | Leave a comment

AX 2012 SSRS Access

The below steps outline how to resolve the following message which can appear when rendering an SSRS report in Dynamics AX 2012:

You do not have access to the specified report. Contact your Microsoft Dynamics AX administrator. The administrator must assign you to a role that has access to the reports or grant access to report to your current role.

There are a number of different levels of security in AX 2012 and the same goes for the associated SSRS report level security. Access to an SSRS report is controlled via access to its associated menu item.

To amend access, open the AOT (Ctrl + D), browse to Menu Items\Output and locate the menu item for the report in question.

Then open a new AOT window, browse to Security\Privileges, and create a new privilege such as MyReportAccess (You can also amend an existing item). Drag the report menu item from the first AOT window under the Entry Points node of your new privilege.

Lastly, add this new Privilege to a duty or role that is assigned to the user experiencing access issues. They should now have access.

Posted in Dynamics AX | Leave a comment

AX, Enums, and Reporting

The official Microsoft line on accessing the AX database directly via SQL is: don’t do it. To reporting into SSRS you are supposed to use the AX addin with VS 2010, use AOT objects and publish from there. If however you decide to go another route, or just want to do some integration, you will quickly come across the concept of enums.

Enums are represented as integers in the database, but are displayed as meaningul items in the UI. For example Yes might be 1 and No might be 0. That is a very basic example, there are hundreds of enums, some with ten or more options. The values and options are not easily accessible in the database.

So to deal with them you could simply look up their values in the AOT and write a large case statement, but there is a better way. There is a table called SRSAnalysisEnums in the database that contains many of these enum mappings. The catch is that it is not updated automatically or at all if you have not configured SSSRS or the SSAS cubes.

To ensure this table is populated for your use (or to refresh if enum options change), do the following:

First open the AOT and go to the BIGenerator class:

Then execute the populateSRSAnalysisEnums method:

Once that runs, the table will be populated and ready for use.

Posted in Business Intelligence, Dynamics AX | 1 Comment

Sum the Last 30, 60, or 90 Days

A common theme with reporting is to consolidate figures over period ranges. Quite often this is done for year to date, but it is common enough to want to see the last 30, 60, or 90 days from a particular period. To make it easier for report users to get this information, custom calculations can be created in an SSAS cube to appear as measures in their reporting tool of choice.

The example below presumes you are running SQL 2008 R2 and have a server generated time dimension. In my case, the last 30 days is simply equal to the current period.

The picture is self-explanatory, but to do this just open up your cube designer in Visual Studio, switch to the Calculations tab, and press the Calculator button to insert a new calculation. Give it a name, specify where you want it to reside in the hierarchy, and enter the calculation expression. A good finishing touch is to set the format string as this will improve default presentation for the report users.

The Last 30 Days looks like so:

Sum([Time].[Month].CurrentMember,[Measures].[Amount])

The Last 60:

Sum( {[Time].[Month].Lag(1) : [Time].[Month].CurrentMember}, [Measures].[Amount])

The Last 90:

Sum( {[Time].[Month].Lag(90) : [Time].[Month].CurrentMember}, [Measures].[Amount])

Note that the only difference is the duration used for the Lag function.

Microsoft has an excellent MDX function reference which you can use to create a wide variety of custom measures.

Posted in Business Intelligence | Leave a comment

CRM 2011 StringMap Table

Fields in a CRM entity record are often based on a pick list. Should you need to query the SQL database directly to extract information these will come out not as the text based descriptive values, but a code such as 0, 1, or 2. To retrieve the associated descriptive information, a join to the StringMapBase table is required. A rough SQL query might be something like so:

select AttributeName, AttributeValue, Value from StringMapBase
where LangId = 1033 and AttributeName = 'TheNameOfTheAttribute'

This works fine for most items, but some such as statecode are used in multiple entities. A join to the MetaDataSchema.Entity table to get the ObjectTypeCode will filter down the records. If you do not the following will result:



Say you wanted to retrieve the values for statecode for the Opportunity entity, you would write query such as this:

select ObjectTypeCode, EntityId, Name from MetadataSchema.Entity
where Name = 'Opportunity' and OverwriteTime = '1900-01-01'

The reason for the date filter is that CRM maintains a history of changes to the entities. If a customisation solution has been applied to your CRM instance, more than one record will exist for some entities.

Joining the two queries together:

select t1.AttributeName, t1.AttributeValue, t1.Value
from StringMapBase t1
inner join MetadataSchema.Entity t2 on t1.ObjectTypeCode = t2.ObjectTypeCode
where t2.Name = 'Opportunity' and t1.AttributeName = 'statecode' and t2.OverwriteTime = '1900-01-01'

The resulting data set looks much better:



The LangId filter in all the queries is simply filtering for the English language – this is needed for multi-lingual systems.

Posted in CRM | 2 Comments