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