SSRS Date Format Issue

Formatting a date can be a common requirement in SSRS reports. Though, we have few built-in methods to play around, it is best if you can do it using format options within the textbox properties. I am just going to demystify something in it.

How to get there;
Right click the text box > Select text box properties > select Number tab > select Date

Now you see this;

Now you can select any format you need.. and that’s the short and sweet way.

Anyway, as I highlighted, I noticed two separate formats which look likes same.
(Do you see what I see?). Now what does this means?

First one shows date as “1/12/2012”
While
Second one shows date as “01/12/2012”

Two different formats, though little unclear.

Plug-in concerns: synchronous or asynchronous, transactional or not …

When developing plug-in we have to make few main decisions based on the requirement. Most fundamental thought comes to our mind is whether we need to do in Pre stage or Post stage, which is usually not hard.

Synchronous or Asynchronous

Then we need to decide, whether it is a synchronous or asynchronous. In my opinion, unless there is very specific reason, it’s good to work synchronous, which is real time. Meaning is once synchronous process is started, user can’t proceed to next step till it finishes. Best fruit is you know if there is any error sooner than later. You will simply see an exception thrown if something is wrong.

Yet, there are specific scenarios you will be forced to go for an asynchronous ones. Best example I encountered is some batch processes that needs a long time to complete. Asynchronous process works in the background, allowing user to proceed to next step. I have done very successful batch processes in asynchronous mode that took 5 to 10 hours to complete.

Problem with asynchronous execution is, you don’t know if an error occur because it’s happening in the background. You may need to check system jobs to see if there are errors.

Transactional or Not

When we discuss about the synchronous and asynchronous modes, we are also forced to discuss about transactions. All asynchronous plug-ins are none-transactional, which means.. if plug-in fails in some point, all the operations happened till that failure are valid. Suppose my batch process needs to process 100 records in one go. If it fails in 61st record due to an data issue, first 60 records have already processed correctly.. Only remaining 40 are un-processed. That’s because asynchronous plug-ins are none-transactional.

In fact, my golden rule for asynchronous ones is use a proper tracing mechanism inside the plugin code. So you are able to see what went wrong after the execution, in case of a failure. Otherwise, how do you know what processed and what not? Knowing Point of failure is important here.

It is best to work synchronous plug-ins for important calculations of payment & etc. for example. Since its best to have nothing as the outcome than having half of the steps… (Ex: Paying the commission for a sale and not doing the sale actually could be horrible than happening nothing.) Fruit of synchronous plug-ins is (with one exception…a special case) they are transactional. That means, if it fails in some point all the previous operations would be reversed. Outcome is 100% or nothing. No need to worry about the point of failure.

Importance of Pre-validation

Let’s talk about the “special case”.. if you register the synchronous plug-in in the pre-validation, it is none transactional. Please refer this article, which explains it really well. It sounds like, the transaction starts in some point and Pre-validation stage occurs before that.

Please note if you write a Pre-Validation Plugin for Create message you will not have the Id since record is not yet created, you need to deal with other fields those available in create.

http://mscrmtools.blogspot.com.au/2011/01/crm-2011-plugins-welcome-to.html

How to throw exception while keeping the changes

Depending on the situation, transactional operations could be annoying too. Recently I wanted to write a plug-in that needs to throw an error to show a message to user, but I didn’t want to roll back what I did so far. Since I need to throw the exception, I have to go for a synchronous plug-in definitely, but it rollbacks everything as soon as they through the exception. Only solution was to register it in pre-validation. (Synchronous in pre-validation is not transactional.. this is the special case we discussed)

So these are some concerns about the plug-in designing.

Update the same entity on Deactivation

This is the code snippet to de activate an entity and it’s straightforward;

SetStateRequest setStateRequest = new SetStateRequest()
{
    EntityMoniker = new EntityReference
    {
        Id = _accountid,
        LogicalName = "account"
    },
    State = new OptionSetValue(1),
    Status = new OptionSetValue(2)
};
crmService.Execute(setStateRequest);

Now, we will see the tricky part. Sometimes we are asked to update some fields in an entity when deactivated. For example, you are required to modify the field values before deactivating an account. Problem is, once you deactivate it, you are not allowed to modify the values. Hence, you need to do it before deactivation. For this we need to register a plug-in for pre- stage of account for state change. (register two steps; setState and setState Dynamic Entity). Below is the code;

public class AccountDeactivation : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
    IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

    try
    {
        IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
        IOrganizationService crmService = factory.CreateOrganizationService(context.UserId);
        Entities.OrgOrionDatamodel orgSvcContext = new Entities.OrgOrionDatamodel(crmService);

        if ((context.InputParameters.Contains("EntityMoniker")) && (context.InputParameters["EntityMoniker"] is EntityReference))
        {
            var targetEntity = (EntityReference)context.InputParameters["EntityMoniker"];
            var state = (OptionSetValue)context.InputParameters["State"];
            var status = (OptionSetValue)context.InputParameters["Status"];

            if (targetEntity.LogicalName != "account")
            { return; }

            if ((state.Value == 1) && (status.Value == 2)) //Identify when the Deactivation is occuring
            {
                //Logic here
            }
        }
    }
    catch (Exception ex)
    {
        throw new InvalidPluginExecutionException("[" + ex.Message + "]" + ex.StackTrace, ex);
    }
    finally
    {
        context = null;
    }
}
}

This plug-in fires on (actually before) deactivation of account. Why?
To fire this below conditions should be met;
- State change should occur
- Resulting values of state and status should be 1 and 2 respectively.
(in fact, this satisfies only when deactivation). Since we register the plug-in in pre stage we still can modify the entity itself if needed.

Note:
This code is working fine for account and many other entities including custom entities. Please check if you need to implement this to entities with complex state and status values such as lead and opportunity. Change the values of conditions (state and status) to pick the correct occurrence you are interested in.

Related Posts;
Sample Plug-in: State change

Code Snippet: Retrieve data with oData query

Just though of sharing a code snippet on retrieving data via oData query.

RetrieveOfficeLocationsByAccount: function()
{
    var _serverUrl = Xrm.Page.context.getServerUrl();
    var _accountID = Xrm.Page.data.entity.getId();
    var _oDataEndpointUrl = _serverUrl + "/XRMServices/2011/OrganizationData.svc/";
    var _offices = RetriveOfficesCollection(_oDataEndpointUrl, _accountID);

    if (_offices != null) 
    {
        alert(_offices.results.length);

        for (var j = 0; j < _offices.length; j++) 
        {
            if (_offices[j].new_Location != null) 
            {
                alert(_offices[j].new_Location.Value);
            }
        }
    }
}

RetriveOfficeCollection: function(_oDataEndpointUrl, _accountID) 
{
    var _offices = null;
    _oDataEndpointUrl += "new_officeSet?$select=new_name,new_Location&$filter=new_AccountId/Id eq (guid'" + _accountID + "')&$orderby=new_name";
    var service = getRequestObject();

    if (service != null) {
        service.open("GET", _oDataEndpointUrl, false);
        service.setRequestHeader("X-Requested-Width", "XMLHttpRequest");
        service.setRequestHeader("Accept", "application/json, text/javascript, */*");
        service.send(null);

        var requestResults = eval('(' + service.responseText + ')').d;
        if (requestResults != null && requestResults.results.length > 0) {
            _offices = requestResults;
        }
    }
    return _offices;
}

getRequestObject: function () 
{
    if (window.XMLHttpRequest) 
    {
        return new window.XMLHttpRequest;
    }
    else 
    {
        try 
        {
            return new ActiveXObject("MSXML2.XMLHTTP.3.0");
        }
        catch (ex) 
        {
            return null;
        }
    }
}

There are two important notes about this code.

If you build the query correctly, you should be able to browse through your browser and see the list of records. It’s always good to check your query this way before proceeding, which will save a lot of time.

Other one is a note of caution. If you check the field names in the code, you may realize they are the schema names! (You see capital letters!.. example: new_Location not new_location). Usually, we use Name (which is all low case in CRM 2011) in most of the codes including Plug-ins.

Using same plug-in for all Create, Update and Delete events

In some cases, we may need to execute same plug-in for Create, Update and Delete. Is this possible?

Yep, you don’t need to write many plug-ins if it’s the same logic. Only thing, we need to have two different ways of triggering the logic in same code. If you need to refresh the knowledge, check Create, Update and Delete code snippets.

You can write a one code accommodating both needs. Different is, in Create/Update we get the target entity in the context while in Delete we get only the reference.

try
{
  context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

  // Create, Update
  
  if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
  {
     TargetEnt = (Entity)context.InputParameters["Target"];

     //Logic here
     //Read Guid like this; 
     //Guid _officeid = (Guid)TargetEnt.Attributes["new_officeid"];

  }

  // Delete

  if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is EntityReference)
  {
     EntityReference EntityRef = (EntityReference)context.InputParameters["Target"];

     //Logic here
     //Read Entity reference like this; 
     //EntityReference _officeidRef = (EntityReference)context.InputParameters["Target"];

  }
  
}

(*I suppose we use a custom entity called new_office in this example)

Hope this is Clear…  register this all Create, Update and Delete steps.

Note:
In Create/Update section always get field values by retrieve method (passing Guid), rather than reading via target entity. If you can remember, though we get all fields in the context for Create, we get only updated fields in Update. Remember?

Sample custom workflow

We will see how you do a custom workflow. We all know how to do things with a normal workflow. Still in some instances, built in wizard of workflows are not able to cater our need. In that case we need to do a code to be executed as a part of the workflow.  

Pl check below code. This shows you a skeleton of a workflow code. If you notice, what we really do is reading some attributes from the context and do whatever we need to do. In fact, idea is quite similar to construction of a plug-ins.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Activities;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Workflow;

namespace CustomWFtest
{
 public class SetAccountOffice : CodeActivity
 {
  protected override void Execute(CodeActivityContext executionContext)
  {
    IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();

    if (context == null)
    {
        throw new InvalidPluginExecutionException("Failed to retrieve workflow context.");
    }

    IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
    IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
    OrgOrionDatamodel orgSvcContext = new OrgOrionDatamodel(service);

    try
    {
        throw new Exception("testing exception .. primary ID is : " + context.PrimaryEntityId);
       
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        context = null;
    }
  }
 }
}

Context gives you some attributes from the triggering point of the workflow. Our sample just throw an exception with primary Id. Use the intellisense to check what other attributes we got in the context and decide how you go with it.

Check below illustration to know what other references we use in this class library project (same as plug-in). Microsoft.Xrm.sdk.workflow dll is the new one we need to add. As same as plug-in development, you must sign the assembly (right click the project > properties > Signing > create .snk file).

Once you do the code, build the dll and register through the plug-in registration tool.

Then you can start workflow wizard to do the needful.  Now, within the wizard, you can see our newly registered DLL with custom workflow name as under “Add Step”. Now you can easily add it as a step.

You can see, in our sample, the exception with primary ID of the record. (go to system jobs and check)

If you wisely study you may realize context we used within the code contains the data relevant to this triggering entity and etc. Hope you understand “context” is the link in between the workflow and your code. Once you understand this, sky is the limit for actions you can do within the code.

Test Plug-in execution using trace

One of the pervious posts,  we discussed few ways of testing how plug-in is executing and whether expected values are being retrieved to the right variables and etc.

Anyway, recently I found tracing as one of the other effective way of testing a plug-in.
What is done here is usage of tracer object (use Microsoft.Xrm.Sdk) to trace any value within the flow.

Below code will give an idea how it’s being done in your code. Please have a good look at the throw of exception line within the catch block. See how the traced information has been passed with the exception.

using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.ServiceModel;

namespace ABCandComSol
{
public class AccountQualifier : IPlugin 
{
  public void Execute(IServiceProvider serviceProvider)
  {

    ITracingService tracer = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

    try
    {
        //Any information you need to trace
        // ex;
        //tracer.Trace("Account:{0} and Guid:{1}", _account.Name, _account.accountid);
    }
    catch (Exception ex)
    {
        tracer.Trace(ex.Message);
        throw new InvalidPluginExecutionException("[" + ex.Message + "]" + ex.StackTrace, ex);
    }
    finally
    {
    }

  }
}
}

Once plug-in is failed all the details you put in the tracer is viewable.  So positive side is, you don’t need to remove any code once code starts working fine. In future, if client say something failed, you got all the information you need in hand!

How to see the trace values

Synchronous Plug-in: Once exception is thrown, check information, you may see all the tracer information within error information.

Asynchronous Plug-in: If plug-in fails, a system jobs will show relevant failed entry. Once you open it, you may see all the tracer information. Further reading here.

Note
In fact, if you want to test something for a working code, you may need to forcefully throw an exception.

Issue in setFocus in forms of CRM 2011

After spending some frustrating time I found a native CRM 2011 issue. When I try to set focus to a lookup field onload of form, it just doesnt work.

What I observed was it jumps to the expected field, but switches back to some other field in the top of the form in no time.

Then I found below fix.. thought of sharing here;

function onLoad() {
   window.setTimeout(focusFromField,100)
}

function focusFromField() {
  var _Control = Xrm.Page.ui.controls.get("new_officeid");
  _Control.setFocus();
}

Also learned this issue is fixed in CRM 2013.

Resolution for issue of wrong totals in tables grouped with filters in SSRS

Previous post I explained the fruit you can have by using filters when grouping table columns. Still, it can be frustrating if you need subtotals for any numeric values in table. What really happens is totals ignore the filters. (Just behave like there is no filter!)

Suppose we add group by some field, but only needs values for given two countries. (i.e. France and Belgium). Filter will be seen as below;

So we also need to get sub totals for groups (suppose, field name as revenueVal). So what we usually do is put below method in the total row;

SUM(revenueVal)

As I explained, issue is this will sum up all the revenues of all the countries not just for filtered ones.

This is the resolution.
Define another calculated field in the DataSet.

And add the same filter criteria as an expression;

Expression will be looked like this… hope you understand the simple logic. We are replacing zero for all the rows which do not meet the criteria.

* Note: If you are going to use the totals in Matrix (instead of a table) please use “nothing” instead of “0” in above expression.

Now we are using our own calculated field for calculation; 

SUM(revenueVal_Filtered)

Now this subtotal adheres our criteria.

Filter data of Table grouping in SSRS

Table grouping is one of the most frequently used techniques in SSRS reports in practice. They can be either easy or very complex. One cool thing we can do in grouping, is putting filters. Fruit is we can have groups for our desired records only.

Most cases we need to use two conditions together as “OR” or “AND” logical join.

This is a good example of using AND condition.
Logic here is Country is France AND Sector is Banking.

Now see the OR condition which is little tricky.
Logic here is Country is either France OR Belgium.

Caution: Using filters for grouping is fun, but there is a known issue of it, if you need to get totals of some numeric columns. Simply SUM() function ignores the filters. There is a workaround for this which I will explain in a later post.

Issue when passing same parameters to more than one data set in SSRS

In SSRS, it is allowed to pass same set of parameters to more than one DataSet which is great. Still it can drag you to troubles if you don’t do it correctly. Below is one error message you may hit with;

An error has occurred during report processing.

The Value expression for the query parameter XXXX contains an error. The expression that references the parameter XXXX does not exists in the Parameters collection. Letters in the name of  parameters must use the correct case.

 

I too encountered this and couldn’t solve for hours. Ultimately, I found this has nothing to do with anything else but the order of the parameters being read in the DataSets. Usually, one may not realise this since we assign it by name.. BUT NOT ENOUGH..  make the same order in all the DataSets using the arrow above..

hmm.. simple.

Caution: And if this error encountered, you may need to delete the parameters and set them again from the scratch.

Run SSRS report against specific CRM record

When we write SSRS reports to Dynamics CRM, it may need to write a report against opened record. For example, order report against selected account. Consider my example for custom entity called office. (Schema name: new_office).

Our usual select statement which returns all the offices;

SELECT
new_name as OfficeName,
new_code as OfficeCode
Filterednew_office

Special query to return the current record specific data;

SELECT    
CRMAF_Filterednew_office.new_name as OfficeName, 
CRMAF_Filterednew_office.new_code as OfficeCode
FROM  Filterednew_office as CRMAF_Filterednew_office

Prefix “CRMAF_Filtered” seems to be doing the filtering. What you really have to do is do your query this way and configure the report so that you can run from opened form of selected entity type. (In my example its Office) Please make sure you select below option so that report is ready to be run from opened record.

By the way, if the query I need to write is complex I read the Guid of current record to a variable so that I am free to use it for any other complex queries without any issue as below;

DECLARE @OfficeId uniqueidentifier

SELECT @OfficeId =CRMAF_Filterednew_office.new_officeid
FROM  Filterednew_office as CRMAF_Filterednew_office

REST Functions from XrmServiceToolkit

Now we are in a situation, that REST calls should do the basic client side functions.

In fact, XrmServiceToolkit (http://xrmservicetoolkit.codeplex.com/) becomes handy. So it’s a library you need to add to your CRM form consist of three files as below;

XrmServiceToolKit website provides some sample coding. Yet, I thought of making them bit more user-friendly and simple as below;

function Retrieve() {
    var re;
    XrmServiceToolkit.Rest.Retrieve(
              "{EE81D2A9-E28E-E311-96DF-D89D6765B238}",
              "AccountSet",
              null, null,
              function (result) {
                  re = result;
                  alert("success");
              },
              function (error) {
                  alert("failed");
              },
              false
          );
    //debugger;
    alert(re.Name);
    alert(re.AccountId);
}

function Delete() {
    XrmServiceToolkit.Rest.Delete(
                "{32815A55-19AF-E311-BF0E-D89D6765B238}",
                "AccountSet",
                function () {
                    alert("successfully deleted");
                },
                function (error) {
                    alert("failed to delete");
                },
                false
            );
}

function Update() {

    var account = {};
    account.Name = "SO and Company A1";
    account.Address1_AddressTypeCode = { Value: 3 }; //Address 1: Address Type = Primary
    account.Address1_City = "Wentworthville";
    account.Address1_Line1 = "153 Dunmore Stret";

    XrmServiceToolkit.Rest.Update(
        "{EE81D2A9-E28E-E311-96DF-D89D6765B238}",
        account,
        "AccountSet",
        function () {
            alert("successfully Updated");
        },
        function (error) {
            alert("failed to Update");
        },
        false
    )

}

function Create() {
    var account = {};
    account.Name = "SO and Company B1";
    account.Address1_AddressTypeCode = { Value: 3 }; //Address 1: Address Type = Primary
    account.Address1_City = "Wentworthville B";
    account.Address1_Line1 = "153 Dunmore Stret B";

    XrmServiceToolkit.Rest.Create(
                    account,
                    "AccountSet",
                    function (result) {
                        accountId = result.AccountId;
                        alert("successfully Created. Acc ID : " + result.AccountId);
                    },
                    function (error) {
                        alert("failed to Create Account");
                    },
                    false
    );

}

Hope this is helpful.

Query to check duplicate field values

Sometimes you need to check if we got duplicate values for a given field. This is a script, we can use to check them.

Suppose I have new_RegNumber field in Account Entity which needs to be unique. So below query will show me if there are any ducplicates by mistake.

SELECT A1.new_RegNumber
FROM Account as A1
WHERE A1.new_RegNumber is not null
GROUP BY A1.new_RegNumber
HAVING (SELECT COUNT(A2.new_RegNumber) FROM  Account A2 WHERE A2.new_RegNumber = A1.new_RegNumber)>1

Hope this is useful.

Concerns in passing a parameter to a sub report

In most cases we need to pass parameters to sub reports in SSRS. If parameter is not available in certain scenarios it can raise issues. I think it’s always good to do the queries in below manner when it comes to datasets in sub report.

In my example I am passing a company name as a parameter which is not mandatory one. Typically my data set returns a Brand name.  In this case, if parameter is null, relevant data fields in the sub report will show a label called “Brand not available”.

IF (@companyName is NOT NULL)
BEGIN

    SELECT new_brandname as BrandName
    FROM ....
    WHERE new_companyName = @companyName  

END

ELSE

BEGIN

   SELECT 'Brand Name not Available' as BrandName

END

A simple trick.