some sql commands

this commands for :


performance of employee

SELECT     s.actualvalue / 100000 AS actualvalue, s.actualclosedate, s.new_productsubcategoryname, s.owneridname, ss.targetmoney / 100000 AS targetmoney,
                      ss.goalowneridname, ss.title
FROM         (SELECT     actualvalue, actualclosedate, new_productsubcategoryname, owneridname
                       FROM          FilteredOpportunity) AS s RIGHT OUTER JOIN
                          (SELECT     targetmoney, goalowneridname, title
                            FROM          FilteredGoal
                            WHERE      (title NOT LIKE 'Overall')) AS ss ON s.new_productsubcategoryname = ss.title AND s.owneridname = ss.goalowneridname


employee performance on product:

SELECT     owneridname, new_productsubcategoryname, estimatedclosedate, estimatedvalue / 100000 AS estimatedvalue, customeridname
FROM         FilteredOpportunity


employee performance on region wise

SELECT     s.actualamt / 100000 AS actualamt, s.owneridname, s.actualclosedate, s.statuscode, ss.targetamt / 100000 AS targetamt, ss.goalowneridname, b.fullname,
                      b.businessunitidname
FROM         (SELECT     fullname, businessunitidname
                       FROM          FilteredSystemUser) AS b LEFT OUTER JOIN
                          (SELECT     SUM(actualvalue) AS actualamt, owneridname, actualclosedate, statuscode
                            FROM          FilteredOpportunity
                            GROUP BY owneridname, actualclosedate, statuscode) AS s ON s.owneridname = b.fullname RIGHT OUTER JOIN
                          (SELECT     SUM(targetmoney) AS targetamt, goalowneridname
                            FROM          FilteredGoal
                            WHERE      (title NOT LIKE 'Overall')
                            GROUP BY goalowneridname) AS ss ON s.owneridname = ss.goalowneridname


product on region wise:

SELECT     actualamt / 100000 AS actualamt, owneridname, actualclosedate, statuscode, targetamt / 100000 AS targetamt, goalowneridname, title, fullname,
                      businessunitidname
FROM         (SELECT     s.actualamt, s.owneridname, s.actualclosedate, s.statuscode, ss.targetamt, ss.goalowneridname, ss.title, b.fullname, b.businessunitidname
                       FROM          (SELECT     fullname, businessunitidname
                                               FROM          FilteredSystemUser
                                               WHERE      (fullname IS NOT NULL)) AS b LEFT OUTER JOIN
                                                  (SELECT     SUM(actualvalue) AS actualamt, owneridname, actualclosedate, statuscode
                                                    FROM          FilteredOpportunity
                                                    GROUP BY owneridname, actualclosedate, statuscode) AS s ON s.owneridname = b.fullname RIGHT OUTER JOIN
                                                  (SELECT     SUM(targetmoney) AS targetamt, goalowneridname, title
                                                    FROM          FilteredGoal
                                                    WHERE      (title NOT LIKE 'Overall')
                                                    GROUP BY goalowneridname, title) AS ss ON s.owneridname = ss.goalowneridname) AS n
WHERE     (fullname IS NOT NULL)

crm comunity

about crm information

https://community.dynamics.com/?lc=1033

damodaram.kar@gmail.com, g1.

using crm parameters in ssrs

in the CRM we have some parameters:
called as Query parameters:

http://msdn.microsoft.com/en-us/library/gg309583.aspx

here we will get the parameters.

how we will see the parameters.

first create a Report in the CRM.

then click on edit. one window will open, in actions we have download report option.
down load the report.
it will down load with .rdl format.
open in BI. you will able to see the CRM parameters.

how to use this parameters:
if you want your the CRM_CurrencySymbol.
drag the parameter to the price table box.

if you want to use the CRM_URL
then
go to the perticular tab field.
go to tablex properities.
there in actions we have one option: go to URL
select that and give like:

=Parameters!CRM_URL.Value & "?ID={"&Fields!new_cooldrinkid.Value.ToString()&"}&LogicalName=new_cooldrink"

for syntax and more details:

http://nishantrana.wordpress.com/2010/07/27/using-crm_url-report-parameter/

here we will get the details how to use this parameter.

ssrs embedded into crm 2011
http://a33ik.blogspot.co.uk/2012/05/embed-context-report-to-left-navigation.html 

Query in LINQ pad

How to query in LINQ

query:


from a in new_cooldrinkSet
select new { cooldrink= a.new_name }


refer:

http://blogs.msdn.com/b/crm/archive/2011/01/11/a-better-way-to-learn-linq-to-crm-linqpad-plugin-for-ms-crm-2011-is-available.aspx

http://www.linqpad.net/

http://mscrmdev.blogspot.in/2012/01/crm-2011-linq-all-columns-vs-selected.html

http://stackoverflow.com/questions/6331562/dynamics-crm-2011-filtering-linq-query-with-outer-joins

http://stackoverflow.com/questions/11436835/how-to-query-using-linq-formattedvalues-in-dynamics-crm-2011

Where Condition:


from a in new_cooldrinkSet
where a.new_name.StartsWith("t")
select new { cooldrink= a.new_name }

create 3d charts

For creating 3D charts:
1. create chart
2. export chat
3. open in visual studio.
4. give the <Area3DStyle Enable3D=True">
in 


</AxisX>
<Area3DStyle Enable3D="True" LightStyle="Realistic"  WallWidth="5" IsRightAngleAxes="true" /> 
</ChartArea>


5. then import it.


check the links
http://niiranen.eu/crm/2010/10/turn-the-flat-dynamics-crm-2011-charts-into-3d/


imp link:
http://ms-crm-2011-beta.blogspot.in/2011/04/how-to-pass-parameters-from-one-plugin.html

PF information

How to get new PF region numbers:
http://59.180.233.229/estt_search/est_search.php

here we will give the establishment code:
for ex: Old pf account number:

KN/46294/21199

in this KN is region.

46294 is establishment code
 21199 is account number.

in above like check the establishment code.
you will get the new code.

like : PY/BOM, office details.

then go to below link:
http://epfoservices.in/epfo/member_balance/member_balance_office_select.php

here select the state.
click on search for establish code.

below you will get the office details.

select your office and give your account details.
PY/BOM/46294 /   /21199

name and mobile number. you will get the balance amount.

update the child entity record

Senario

if we need to update a actual date field in Opportunityproduct entity with opportunity entity actual date field data.

here we need to give a field level mapping from opportunity to opportunityproduct.

other wise we need to write a plug in for create a record in opportunity product also.

create a record in opportunity product.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xrm;
using System.Diagnostics;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Windows.Browser;
using System.Net;
using System.IO;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Discovery;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Client;
namespace Kryptos.Actual.Est.dates
{
    public class Opportunitycreate:IPlugin
    {
        public DateTime actualdate;
        public DateTime estimateddate;

        public IOrganizationService service;
        public IPluginExecutionContext context;

        public void Execute(IServiceProvider serviceProvider)
        {


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

            Entity entity;

            // Check if the input parameters property bag contains a target
            // of the create operation and that target is of type Entity.
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {

                // Obtain the target business entity from the input parameters.
                entity = (Entity)context.InputParameters["Target"];

                // Verify that the entity represents a contact.
                if (entity.LogicalName != "new_opportunityitproduct")
                {
                    return;
                }
            }
            else
            {
                return;
            }

            try
            {
                IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                Entity entity1 = (Entity)context.InputParameters["Target"];

                if (context.MessageName == "Create")
                {
                    //Money priceamt = (Money)entity1["new_price"];

                    //int timeonitems = Convert.ToInt32(entity1["estimatedvalue"]);
                    EntityReference var1 = (EntityReference)entity1["new_opportunityid"];


                    ColumnSet cols = new ColumnSet(true);

                    var contact1 = service.Retrieve("opportunity", var1.Id, cols);

                    if (contact1.Attributes.Keys.Contains("actualclosedate") == false)
                    {
                        //totalestrevenue = priceamt;
                        entity1["new_actualclosedate"] = null;

                        //actualdate = new DateTime();
                    }


                    else
                    {
                        // int timeofitems = Convert.ToInt32(contact1["new_price"]);
                        //Money estimatedamt = (Money)contact1["estimatedvalue"]; //

                        actualdate = (DateTime)contact1["actualclosedate"];

                        // totaltime = timeofitems + timeonitems;

                       // totalestrevenue = new Money() { Value = (estimatedamt.Value + priceamt.Value) };//estimatedamt + priceamt;
                    }

                    if (contact1.Attributes.Keys.Contains("estimatedclosedate") == false)
                    {

                        entity1["new_estimatedclosedate"] = null;
                    }
                    else
                    {
                        estimateddate = (DateTime)contact1["estimatedclosedate"];
                    }

                    if (actualdate == null && estimateddate == null)
                    {
                        service.Update(entity1);
                    }

                   // contact1["estimatedvalue"] = totalestrevenue;
                    if (actualdate != null)
                    {
                        if (actualdate.ToShortDateString() != "1/1/0001")
                        {
                            entity1["new_actualclosedate"] = actualdate;
                        }
                    }
                    else
                    {
                        entity1["new_actualclosedate"] = null;
                    }
                    if (estimateddate != null)
                    {
                        if (estimateddate.ToShortDateString() != "1/1/0001")
                        {
                            entity1["new_estimatedclosedate"] = estimateddate;
                        }
                    }

                    else
                    {
                        entity1["new_estimatedclosedate"] = null;
                    }
                  
                    service.Update(entity1);
                }




            }

            catch (FaultException<OrganizationServiceFault> ex)
            {
                throw new InvalidPluginExecutionException("An error occurred in the plug-in.", ex);
            }

        }

    }
}

Update a record in Opportunity:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Xrm;

using System.Diagnostics;

using System.ServiceModel;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Query;

using System.Windows.Browser;

using System.Net;

using System.IO;

using System.ServiceModel.Description;

using Microsoft.Xrm.Sdk.Discovery;

using Microsoft.Xrm.Sdk.Messages;

using Microsoft.Crm.Sdk.Messages;

using Microsoft.Xrm.Sdk.Client;

namespace Kryptos.Actual.Est.dates

{

   public class dateonupdate:IPlugin

    {

        public Nullable<DateTime> actualdate1;

        public Nullable<DateTime> estimateddate1; 

        public IOrganizationService service;

        public IPluginExecutionContext context;

        public void Execute(IServiceProvider serviceProvider)

        {

            IPluginExecutionContext context = (IPluginExecutionContext)

            serviceProvider.GetService(typeof(IPluginExecutionContext));

            Entity entity;

            // Check if the input parameters property bag contains a target

            // of the create operation and that target is of type Entity.

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)

            {

                if (context.Depth > 1)

                {

                    return;

                }

                // Obtain the target business entity from the input parameters.

                entity = (Entity)context.InputParameters["Target"];

                // Verify that the entity represents a contact.

                if (entity.LogicalName != "opportunity")

                {

                    return;

                }

            }

            else

            {

                return;

            }

            try

            {

                IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                Entity entity1 = (Entity)context.InputParameters["Target"];

                

                string fetchquery = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +

  "<entity name='new_opportunityitproduct'>" +

    "<attribute name='new_opportunityitproductid' />" +

    "<attribute name='new_name' />" +

    "<attribute name='new_category' />" +

    "<attribute name='new_subcategory' />" +

    "<attribute name='new_productservice' />" +

    "<attribute name='new_price' />" +

    "<attribute name='new_actualclosedate' />" +

    "<attribute name='new_estimatedclosedate' />" +

    "<attribute name='createdon' />" +

    "<order attribute='new_name' descending='false' />" +

    "<filter type='and'>" +

      "<condition attribute='new_opportunityid' operator='eq' uitype='opportunity' value='" + entity1.Id + "' />" +

    "</filter>" +

  "</entity>" +

"</fetch>";

                RetrieveMultipleRequest req = new RetrieveMultipleRequest();

                FetchExpression fetch = new FetchExpression(fetchquery);

                req.Query = fetch;

                RetrieveMultipleResponse resp = (RetrieveMultipleResponse)service.Execute(req);

                //oipt.Id = leadids;

                EntityCollection col = resp.EntityCollection;

                EntityReference opp = new EntityReference();

                opp.Id = entity.Id;

                opp.LogicalName = entity.LogicalName;

                Entity opp_product = new Entity();

                opp_product.LogicalName = "new_opportunityitproduct";

                if (entity1.Attributes.Keys.Contains("actualclosedate") == false)

                {

                    //actualdate1 = new System.DateTime();

                    //actualdate1 = DateTime.MinValue;

                    actualdate1 = null; 

                   

                }

                else

                {

                    actualdate1 = (DateTime)entity1["actualclosedate"];

                }

                if (entity1.Attributes.Keys.Contains("estimatedclosedate") == false)

                {

                    //estimateddate1 = DateTime.MinValue;

                    estimateddate1 = null;

                }

                else

                {

                    estimateddate1 = (DateTime)entity1["estimatedclosedate"];

                }

               

                foreach (var c in col.Entities)

                {

                    if (actualdate1 != null)

                    {

                        opp_product["new_actualclosedate"] = actualdate1;

                        //if (actualdate1.ToShortDateString() != "1/1/0001")

                        //{

                        //    opp_product["new_actualclosedate"] = actualdate1;

                        //}

                    }

                    if (estimateddate1 != null)

                    {

                        opp_product["new_estimatedclosedate"] = estimateddate1;

                        //if (estimateddate1.ToShortDateString() != "1/1/0001")

                        //{

                        //    opp_product["new_estimatedclosedate"] = estimateddate1;

                        //}

                    }

                    //opp_product["new_price"] = (Money)c["new_price"];//new Money() { Value = 0 };

                    //opp_product["new_opportunityid"] = 

                    opp_product.Id = c.Id;

                    opp_product.LogicalName = c.LogicalName;

                    service.Update(opp_product);

                    

                }           

                    

                

                

            }

            catch (FaultException<OrganizationServiceFault> ex)

            {

                throw new InvalidPluginExecutionException("An error occurred in the plug-in." +ex.InnerException.Message);

            }

        }

    }

}

Assign Date Field null:

Nullable<DateTime> actualdate1;

 actualdate1=null;

or  actualdate1.value=null;