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