Tuesday 26 February 2013

Null Values in SSRS Parameters


we have option to give parameter as blank or null
by enabling the features


CRMAF in ssrs



select filteredsalesorder.ordernumber,filteredsalesorder.name,filteredsalesorder.createdon,filteredsalesorder.quoteid,filteredsalesorder.totalamount,
filteredaccount.address1_line1,filteredaccount.address1_line2,filteredaccount.address1_line3,filteredaccount.name,filteredaccount.address1_city,filteredaccount.address1_country,
filteredaccount.address1_stateorprovince,filteredaccount.address1_postalcode,
filteredbusinessunit.address2_line1,filteredbusinessunit.address2_line2,filteredbusinessunit.address2_line3,filteredbusinessunit.address2_city,
filteredbusinessunit.address2_stateorprovince,filteredbusinessunit.address2_postalcode,filteredbusinessunit.address2_country
 from filteredsalesorder,filteredbusinessunit,filteredaccount
where (filteredsalesorder.customerid = filteredaccount.accountid) and (filteredsalesorder.jmh_store=filteredbusinessunit.businessunitid)

select * from filteredsalesorder as CRMAF_filteredsalesorder,filteredbusinessunit,filteredaccount
where (CRMAF_filteredsalesorder.customerid = filteredaccount.accountid) and (CRMAF_filteredsalesorder.jmh_store=filteredbusinessunit.businessunitid)

select CRMAF_filteredsalesorder.ordernumber,CRMAF_filteredsalesorder.name,CRMAF_filteredsalesorder.createdon,CRMAF_filteredsalesorder.quoteid,CRMAF_filteredsalesorder.totalamount,
filteredaccount.address1_line1,filteredaccount.address1_line2,filteredaccount.address1_line3,filteredaccount.name,filteredaccount.address1_city,filteredaccount.address1_country,
filteredaccount.address1_stateorprovince,filteredaccount.address1_postalcode,
filteredbusinessunit.address2_line1,filteredbusinessunit.address2_line2,filteredbusinessunit.address2_line3,filteredbusinessunit.address2_city,
filteredbusinessunit.address2_stateorprovince,filteredbusinessunit.address2_postalcode,filteredbusinessunit.address2_country
 from filteredsalesorder as CRMAF_filteredsalesorder ,filteredbusinessunit,filteredaccount
where (CRMAF_filteredsalesorder.customerid = filteredaccount.accountid) and (CRMAF_filteredsalesorder.jmh_store=filteredbusinessunit.businessunitid)


Monday 25 February 2013

Filter dataset records based on another dataset data in ssrs


Senario:
we have two datasets.
dataset1 and dataset2.
based on dataset1 values we need to filter dataset2 data.

Doing thourgh FetchXml.

First create the Dataset1 with enableprefiltering="1".
Ex:


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="salesorder" enableprefiltering="1">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="statuscode" />
    <attribute name="totalamount" />
    <attribute name="createdon" />
    <attribute name="ordernumber" />
    <attribute name="quoteid" />
    <attribute name="salesorderid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="name" operator="not-null" />
    </filter>
    <link-entity name="account" from="accountid" to="customerid" alias="aa">
      <attribute name="name" />
      <attribute name="address1_name" />
      <attribute name="address1_line1" />
      <attribute name="address1_line2" />
      <attribute name="address1_line3" />
      <attribute name="address1_city" />
      <attribute name="address1_stateorprovince" />
      <attribute name="address1_postalcode" />
      <attribute name="address1_country" />
      <filter type="and">
        <condition attribute="name" operator="not-null" />
      </filter>
    </link-entity>
    <link-entity name="businessunit" from="businessunitid" to="jmh_store" alias="ab">
      <attribute name="address2_name" />
      <attribute name="address2_line1" />
      <attribute name="address2_line2" />
      <attribute name="address2_line3" />
      <attribute name="address2_city" />
      <attribute name="address2_stateorprovince" />
      <attribute name="address2_postalcode" />
      <attribute name="address2_country" />
      <filter type="and">
        <condition attribute="name" operator="not-null" />
      </filter>
    </link-entity>
  </entity>
</fetch>

the about code will join "order", "Account","businessunit" tables.

next :
dataset2:


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="salesorderdetail">
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />   
    <attribute name="baseamount" />
    <attribute name="jmh_orderamount" />    
    <attribute name="salesorderid" />
    <attribute name="salesorderdetailid" />
    <order attribute="productid" descending="false" />
    <filter type="and">
      <condition attribute="quantity" operator="not-null" />
    </filter>
  </entity>
</fetch>

now you need to get the data in dataset2 relating of dataset1;

create parameters 










Give the values as default values.
Use this filter and create another dataset3 for the Salesorder.
after that create another parameter



so using 3 parameter
create filter on dataset2.






u will get the correct details.

Wednesday 20 February 2013

Date Validation in crm 2011 using javascript


Validate two date fields using javascript.


function ValidateDates()
{

var startdate = Xrm.Page.getAttribute("jmh_invoicedate").getValue();
var dueby = Xrm.Page.getAttribute("jmh_daterecieved").getValue();

if(startdate != null && dueby != null)
{
if(dueby.setHours(0,0,0,0) < startdate.setHours(0,0,0,0))
{
alert("Recieved Date must be greater than or equal to Invoice Date");
var pass = Xrm.Page.data.entity.attributes.get("jmh_daterecieved");
pass.setValue(null);
//event.returnValue = false;
//return false;
}
}
}


Tuesday 5 February 2013

xrmtoolkit

Open a Email Form in crm 2011 with selected records details

Senario :

select a record in crm 2011 grid view and click a ribbon button.
open the E-mail form with the selected record details as body(description).

for this first create on custom field in E-mail activity with "new_body"

create a javascript like this:


function emailopen(ids) {

    // var selectedid = ids;
    var entityname = 'new_cooldrink';
 
    resultXml = RetrieveEntityById(entityname,ids, 'new_name');
    var parameters = {};
    parameters["subject"] = 'This is Subject';
    parameters["new_body"] = resultXml.toString();
    Xrm.Utility.openEntityForm("email", null, parameters);

}
//dont change any thing (for reference)
function RetrieveEntityById(prmEntityName, prmEntityId, prmEntityColumns) {
    var resultXml, errorCount, msg, xmlHttpRequest, arrayEntityColumns, xmlEntityColumns;
    arrayEntityColumns = prmEntityColumns.split(",");
    for (var i = 0; i < arrayEntityColumns.length; i++) {
        xmlEntityColumns += "<q1:Attribute>" + arrayEntityColumns[i] + "</q1:Attribute>";
    }
    var authenticationHeader = Xrm.Page.context.getAuthenticationHeader();
    //Prepare the SOAP message.
    var xml = "<?xml version='1.0' encoding='utf-8'?>" +
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'" +
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'" +
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>" +
    authenticationHeader +
    "<soap:Body>" +
    "<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" +
    "<entityName>" + prmEntityName + "</entityName>" +
    "<id>" + prmEntityId + "</id>" +
    "<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>" +
    "<q1:Attributes>" +
    xmlEntityColumns +
   "</q1:Attributes>" +
    "</columnSet>" +
    "</Retrieve></soap:Body></soap:Envelope>";
    //call function to create Soap Request to ms crm webservice
    xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    resultXml = xmlHttpRequest.responseXML;
    var errorCount = resultXml.selectNodes('//error').length;
    if (errorCount != 0) {
        var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
        alert("Error Message : " + msg);
    }
    else {
        return resultXml;
    }
}

create a web resource and paste this code:

after that create ribbon button:


then write a javascript code in E-mail onload event:


function emailonload()
{

 if (Xrm.Page.data.entity.attributes.get('test_body').getValue()!= null)
     {

    var selectedid = Xrm.Page.data.entity.attributes.get('new_body').getValue();
    var entityname = 'new_cooldrink';
    //var columns = 'new_name','new_productcount';
    var x = null;
    var y = null;
    var resultXml = RetrieveEntityById(entityname, selectedid, 'new_name,new_productcount');
    if (resultXml != null && resultXml.selectSingleNode('//q1:new_name') != null) {
        x = resultXml.selectSingleNode('//q1:new_name').nodeTypedValue; //.getAttribute("name");    
    }
    if (resultXml != null && resultXml.selectSingleNode('//q1:new_productcount') != null) {
        y = resultXml.selectSingleNode('//q1:new_productcount').nodeTypedValue; //.getAttribute("name");      
    }
    //var table = "<table border='1' bgcolor='#00FF00'><tr><td> Name </td> <td> Test </td> </tr><tr><td>" + x + "</td><td>" + y + "</td></tr></table>";
    var z = x + y;
    Xrm.Page.data.entity.attributes.get('new_body').setValue(z);
}
}

function xmlToString(resultXml) {
    if ($(resultXml).find('q1\\:Attributes').length != 0) {
        response = $(resultXml).find('q1\\:Attributes').eq(0);
    } else {
        response = $(resultXml).find('Attributes').eq(0); ; //Chrome 24.0.1312.52 could not find node by the previous code
    }
    var result = ((typeof window.CrmEncodeDecode != 'undefined') ? window.CrmEncodeDecode.CrmXmlDecode(response.text()) : crmXmlDecode(response.text()));

    return result;
}


function RetrieveEntityById(prmEntityName, prmEntityId, prmEntityColumns) {
    var resultXml, errorCount, msg, xmlHttpRequest, arrayEntityColumns, xmlEntityColumns;
    arrayEntityColumns = prmEntityColumns.split(",");
    for (var i = 0; i < arrayEntityColumns.length; i++) {
        xmlEntityColumns += "<q1:Attribute>" + arrayEntityColumns[i] + "</q1:Attribute>";
    }
    var authenticationHeader = Xrm.Page.context.getAuthenticationHeader();
    //Prepare the SOAP message.
    var xml = "<?xml version='1.0' encoding='utf-8'?>" +
    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'" +
    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'" +
    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>" +
    authenticationHeader +
    "<soap:Body>" +
    "<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" +
    "<entityName>" + prmEntityName + "</entityName>" +
    "<id>" + prmEntityId + "</id>" +
    "<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>" +
    "<q1:Attributes>" +
    xmlEntityColumns +
   "</q1:Attributes>" +
    "</columnSet>" +
    "</Retrieve></soap:Body></soap:Envelope>";
    //call function to create Soap Request to ms crm webservice
    xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    resultXml = xmlHttpRequest.responseXML;
    var errorCount = resultXml.selectNodes('//error').length;
    if (errorCount != 0) {
        var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
        alert("Error Message : " + msg);
    }
    else {
        return resultXml;
    }
}

for this code execution we need to add the
Jquery.js
Json2.js
XrmServiceToolkit.js in E-mail form.



launch a report in Ribbon button click crm 2011

Launch a Report in Ribbon Button click

First create a Report in ssrs or report area in crm 2011

if you created in SSRS. deploy in crm.
Get the GUID of the report

how to get guid:
open the report and press F11.

then write the below code in javascript:


// JScript source code
function openreport(id) {
   
    var errorMessage = "Context is not available.";
    var context;
    if (typeof GetGlobalContext != "undefined") {
        context = GetGlobalContext();
    }
    else {
        if (typeof Xrm != "undefined") {

            context = Xrm.Page.context;
        }
        else {
            alert(errorMessage);
            return;
        }
    }
    var str = id;
//    alert(id);
//     var strId = str.replace("{", "");
//alert("hi");
var orgUrl = context.getServerUrl();
var currEntityObjTypeCode= Xrm.Page.context.getQueryStringParameters().etc
//alert(currEntityObjTypeCode);
//alert(orgUrl);
var reportUrl = orgUrl + "/crmreports/viewer/viewer.aspx?action=run&context=records&helpID=OrderAcknowledgement.rdl&id=%7b20a4c6e9-8f6f-e211-957a-

00155d000b45%7d&records=" + encodeURIComponent(  id ) +"&recordstype="+currEntityObjTypeCode;
//alert(reportUrl);
window.open(reportUrl);//, "_blank", "width=900px,height=600px,resizable=1");
   //return reportUrl;
  }



Change in code :

chage the id parameter with your Report guid.

create a web resource and put this code as jscript.









Sunday 3 February 2013

Connect CRM 2011 Online with SSRS

Connecting CRM 2011 online with SSRS.

in online service we have 2 choices.
1. CRM 2011 Online
2. CRM 2011 Office 365 (includes Free Trail Version)

CRM 2011 Online connection.

http://garethtuckercrm.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/

based on the above link we will get.
in credentials we need to give the Online account and password.

CRM 2011 Office 365.

first we need to create a report in crm 2011 and
click on edit,
Actions-->Download.

in SSRS, take add existing item and add it.
do the manipulation again import into crm 2011.

if you want to use fetchxml for On primse version.
give the connection string like:
http://crmserver/organization

in credential take the window authentication.