Friday 18 January 2013

CRM 2011 On-premises – Custom SSRS reports are blank, no data.

Did you ever add “Domain\MachineName$” as a User in Dynamics CRM on-premises? Sounds odd..

Recently I have faced an issue with SSRS reports and Dynamics CRM 2011 on-premises deployment (development ORG) wherein the reports were running well and fetching the expected data in BIDS solution but unable to show the data when I upload this report to Dynamics CRM. There were no errors as such but the report was blank, no data. Additionally, the report was fetching the data when you run a report on SSRS report server. Huh…

To resolve it I had rechecked the complete configuration of this report in BIDS as well as at Report Manager. I had uploaded another report and no luck with that as well. In fact none of my custom reports were able to fetch data.

After doing some search on net I found below thread,

http://social.microsoft.com/Forums/en/crm/thread/6521ee17-c2c9-4401-8a29-c7cde9d8ba24

As specified in this thread, I re-entered to Report Manager and at that time I saw that many reports are deployed and published by user like DOMAIN\MachineName$. Then I added the same person (DOMAIN\MachineName$) as a User in Dynamics CRM and this change had resolved the problem.

Did someone else ever face such issue before? If yes then please feel free to comment on this post with your understanding of the problem and resolution.

Friday 11 January 2013

Dynamics CRM 2011 On-premises – “CRMAF_” does not work for Custom SSRS reports

This is a small post. I hope this will help developers at least by saving a day.

To run a contextual custom SSRS report for Dynamics CRM 2011 on-premises, we need to give an alias with “CRMAF_” keyword to a Filtered View in our SQL query. For instance, you wish to run a custom SSRS report for an Account entity from an entity record, entity list area and reports area then provide an alias as shown in below query.

SELECT 
accountid
FROM
    FilteredAccount AS CRMAF_FilteredAccount


When you upload this a report with above query Dynamics CRM will replace the FROM table name with a sub-query parameter. To know more about “CRMAF_” magic please refer below URL.

http://msdn.microsoft.com/en-us/library/gg328288.aspx#SQLBasedPre_Filtering

In my case when I had uploaded a report using a simple query as specified above, Dynamics CRM did not replace it with a sub-query parameter on my development machine. I had also removed the report record from development machine more than once and re-imported it as afresh report with no luck. However the same RDL file was working well on another CRM on-premises server/machine. After some investigation I came to know that the problem is related to report data source connection string!!!

Below is the connection string that I had specified in my custom report data source properties.

image

Workaround

I had replaced the above connection string with some other server and database name and uploaded it again on development machine. Voila!! This change worked for me and was able to run my report contextually on my development machine.

Did anyone else face such problem? If yes then please feel free to share your understanding about the problem with the solution(s) as comments on this post.

Friday 4 January 2013

CRM 2011 Lookup functionality in a Silverlight Data Grid

In this post I will explain you how to create the CRM lookup control functionality in Silverlight data grid control. There will be a column of lookup data type that allows you to select related entity record as seen in bellow screenshot. Here in this example I am going to create a lookup column of an Account entity.

image

To implement the same look and feel of the lookup column, write the below given code in your xaml file of Silverlight application.

<sdk:DataGridTemplateColumn Width="190" Header ="Account" CellStyle="{StaticResource CRM2011_DataGridCellStyle}">
                <sdk:DataGridTemplateColumn.CellTemplate>
                  <DataTemplate>
                    <Border BorderThickness="1">
                      <StackPanel>
                        <StackPanel.Resources>
                          <Style x:Key="LookupHyperlinkStyle" TargetType="HyperlinkButton">
                            <Setter Property="FontFamily" Value="Fonts/segoeui.ttf#Segoe UI"/>                          
                            <Setter Property="Foreground" Value="Blue"/>
                            <Setter Property="FontSize" Value="11"/>
                            <Setter Property="BorderThickness" Value="1"/>
                            <Setter Property="VerticalAlignment" Value="Bottom"/>
                            <Setter Property="FontWeight" Value="Normal"/>
                            <Setter Property="Padding" Value="3,0,0,2"/>
                          </Style>
                        </StackPanel.Resources>
                        <Grid x:Name="lookupData" Background="White">
                          <Grid.ColumnDefinitions>
                            <ColumnDefinition Width="auto"/>
                            <ColumnDefinition Width="150"/>
                            <ColumnDefinition Width="21"/>
                          </Grid.ColumnDefinitions>
                          <Image Source="{Binding Path=ink_AccountImageUrl}" Height="16" Width="16" Stretch="Fill" Grid.Column="0"></Image>
                          <HyperlinkButton  x:Name="LookupDataName"  Grid.Column="1" Click="LookupDataName_Click"  Style="{StaticResource LookupHyperlinkStyle}" >
                            <HyperlinkButton.Content>
                              <Binding  Mode="TwoWay" Path="ink_Account.Name"></Binding>
                            </HyperlinkButton.Content>
                          </HyperlinkButton>
                          <HyperlinkButton x:Name="OpenLookupData" Grid.Column="2" Click="OpenLookupData_Click" HorizontalAlignment="Right" >
                            <HyperlinkButton.Content>
                              <Image x:Name="ImgLookup" Source="/ExpenseNote;component/Images/LookupButton.png" MouseEnter="ImgLookup_MouseEnter"  MouseLeave="ImgLookup_MouseLeave"  Height="20" Width="21" Stretch="Fill"></Image>
                            </HyperlinkButton.Content>
                          </HyperlinkButton>
                        </Grid>
                      </StackPanel>
                      <Border.BorderBrush>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                          <GradientStop Color="Gray" Offset="0" />
                          <GradientStop Color="Gray" Offset="1" />
                          <GradientStop Color="Gray" Offset="0.105" />                        
                        </LinearGradientBrush>
                      </Border.BorderBrush>
                    </Border>
                  </DataTemplate>
                </sdk:DataGridTemplateColumn.CellTemplate>
              </sdk:DataGridTemplateColumn>


The column consists of mainly three controls:-

image

1) The image control to display account image. This is a web resource in Dynamics CRM.

2) The second control is a hyperlink button (in above example it is named as “LookupDataName”). Once you select a record from account lookup window, this link will help you to open the selected account record, same as we do in Dynamics CRM.

3) The third control is a hyperlink button (in above example it is named as “OpenLookupData”). This button will help you to open a popup window as shown in below screenshot.

image

Now let’s look into the code behind part of Silverlight control.

Below are the two methods that help you to toggle the images on mouse hover and mouse out for lookup hyperlink button (a third control).



private void ImgLookup_MouseLeave(object sender, MouseEventArgs e)
    {
      try
      {
        Uri uri = new Uri("/ExpenseNote;component/Images/LookupButton.png", UriKind.Relative);
        ImageSource imgSource = new BitmapImage(uri);
        ((Image)sender).Source = imgSource;
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }
 
    
 
    private void ImgLookup_MouseEnter(object sender, MouseEventArgs e)
    {
      try
      {
        Uri uri = new Uri("/ExpenseNote;component/Images/lookupbutton_hover.png", UriKind.Relative);
        ImageSource imgSource = new BitmapImage(uri);
        ((Image)sender).Source = imgSource;
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }


Below is the method that will open lookup record window for an Account entity. Replace “_serverUrl” variable with your local variable. If you could see in below code, while opening lookupinfo.aspx page you are also allowed to specify other useful query string parameters like AllowFilterOff, DefaulType (Object Type), DidableQuickFind, DisableViewPicker etc. So, setup them based on your need.



/// <summary>  
    /// Opens account Lookup window 
    /// </summary>  
    private void OpenLookupData_Click(object sender, RoutedEventArgs e)
    {
      try
      {
        //Open Lookup for Account.
        lookUpAccount = _serverUrl + "/_controls/lookup/lookupinfo.aspx?AllowFilterOff=1&DefaultType=1&DisableQuickFind=0&DisableViewPicker=0&LookupStyle=single&ShowNewButton=1&ShowPropButton=1&browse=0&objecttypes=1";
        dynamic ReturnedParams = HtmlPage.Window.Invoke("showModalDialog", lookUpAccount, null, "dialogWidth:600px;dialogHeight:600px");
 
        if (ReturnedParams == null)
        {
          return;
        }
        else
        {
          //Set the selected account
          Guid AccountId = new Guid(ReturnedParams.items[0].id);
          var AccountName = ReturnedParams.items[0].name;
          TheMainViewModel.SelectedExpenseNote.ink_Account.Id = AccountId;
          TheMainViewModel.SelectedExpenseNote.ink_Account.Name = AccountName;
          TheMainViewModel.SelectedExpenseNote.ink_AccountImageUrl = "/ExpenseNote;component/Images/AccountImg.png";
        }
      }
      catch (Exception ex)
      {
 
        throw ex;
      }
    }

If you notice I have implemented MVVM pattern in above code. The MainViewModel is an object of view model class. Its properties are bound with lookup controls. So, we just need setup the account Id, account name and image URL properties of this class. Now, once you select an account record from the popup window, the selected record will be displayed in a grid as shown in below screenshot.

image


Lastly, you wish to open that account record when you click on “test” hyperlink button as seen in below screenshot.

image

For that we need to write below code to open a main.aspx of Dynamics CRM in a popup window using “HtmlPage.PopupWindow” method.



private void LookupDataName_Click(object sender, RoutedEventArgs e)
    {
      try
      {
        if (((ink_expensenote)(ExpenseNotesGrid.SelectedItem)).ink_Account.Id != null)
        {
          Guid lookupId = new Guid(((ink_expensenote)(ExpenseNotesGrid.SelectedItem)).ink_Account.Id.ToString());
          HyperlinkButton link = e.OriginalSource as HyperlinkButton;
          if (link != null)
          {
            HtmlPopupWindowOptions options = new HtmlPopupWindowOptions();
            options.Left = 0;
            options.Top = 0;
            options.Width = 800;
            options.Height = 600;
            options.Menubar = false;
            options.Toolbar = false;
            options.Location = false;
            options.Resizeable = true;
            options.Scrollbars = true;
            //Open a record in new window
            HtmlPage.PopupWindow(new Uri(_serverUrl + "/main.aspx?etc=1&extraqs=%3fetc%3d1%26id%3d%257b" + lookupId.ToString().ToUpper() + "%257d&pagetype=entityrecord"), "_blank", options);
          }
        }
      }
      catch (Exception ex)
      {
        throw ex;
      }
    }

I hope this post will be helpful to developers who want to have lookup type of column in Silverlight data grid control.