e books

http://it-ebooks.info/tag/microsoft/

http://it-ebooks.info/book/515/

http://www.wowebook.be/book/sams-teach-yourself-microsoft-dynamics-crm-2011-in-24-hours/# 

phone call with oppotunity

Query : select op.name,op.customeridname,op.createdon,op.owneridname ,fp.activitytypecodename,fp.new_callbackname,fp.subject from
FilteredOpportunity op,FilteredPhoneCall fp
where op.opportunityid=fp.regardingobjectid

function:


USE [neworg_MSCRM]
GO
/****** Object:  UserDefinedFunction [dbo].[Getopportunitycount]    Script Date: 09/06/2012 11:36:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[Getopportunitycount]
(
-- Add the parameters for the function here
@createdate date,
@uname varchar(50)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE
@ResultVar int

-- Add the T-SQL statements to compute the return value here
set @ResultVar = (select count(*) from FilteredPhoneCall where  (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, createdon))) = @createdate) and (new_callback=1) and (createdbyname = @uname ))
set @ResultVar= (select COUNT(*) from FilteredOpportunity op,FilteredPhoneCall fp where (op.opportunityid=fp.regardingobjectid) and

(op.createdbyname = @uname) and (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, op.createdon))) = @createdate) )

-- Return the result of the function
RETURN @ResultVar

END

query :


IF OBJECT_ID('tempdb..#AllReportees') IS NOT NULL
        DROP TABLE #AllReportees
        CREATE TABLE #AllReportees
(
    createdate Date,
    uname varchar(50),
    ownids uniqueidentifier,
    phoneact varchar(50),
    statuscode varchar(50)
  
)

INSERT INTO #AllReportees
select CONVERT(VARCHAR, createdon, 106) AS date1,createdbyname,ownerid,activityid,statuscodename from FilteredPhoneCall
union
select CONVERT(VARCHAR, createdon, 106) AS date1,createdbyname,ownerid,opportunityid,statuscodename  from FilteredOpportunity



 select *
 ,dbo.Getwrongphonecallcount(dd.createdate,dd.uname) as 'Wrong Calls' ,dbo.Getnoresponsecount(dd.createdate,dd.uname) as ' No Response' , dbo.Getcallbackcount(dd.createdate,dd.uname) as 'Callback',
 dbo.Getopportunitycount( dd.createdate,dd.uname ) as 'Opportunities'
  from (
select  ss.createdate,ss.uname,
 count(distinct fp.activityid) as Phone
   from #AllReportees ss
 left OUTER JOIN FilteredSystemUser AS su  WITH (NOLOCK) ON su.systemuserid=ss.ownids
 left OUTER JOIN  FilteredPhoneCall as fp WITH (NOLOCK) ON  (fp.activityid=ss.phoneact)
 left OUTER JOIN FilteredOpportunity fl WITH (NOLOCK) ON  (fl.opportunityid=ss.phoneact)
 left OUTER JOIN FilteredAppointment AS fa WITH (NOLOCK) ON (fa.activityid=ss.phoneact)
     group by ss.createdate,uname
     ) dd order by dd.uname,dd.createdate

Fetch XML ssrs

pls check the below link

https://community.dynamics.com/product/crm/crmtechnical/b/lostinthoughtsofcrm/archive/2012/04/28/microsoft-dynamics-crm-2011-develop-fetch-xml-based-ssrs-reports-in-visual-studio-2008.aspx?wa=wsignin1.0


crm ref:

http://inogic.blogspot.in/2011/12/import-data-now-allows-you-to-create.html

http://www.magnetismsolutions.com/blog/paul-nieuwelaars-blog/2011/12/18/Run_Fetch_XML_Report_on_Selected_Record_in_Dynamics_CRM_2011.aspx

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 }