SqlServer Reporting Services – Page 4

Good resource for SQL Server Reporting Services

Hi,

Please check out this wonderful article.

http://www.ssw.com.au/ssw/standards/rules/rulesToBetterSQLReportingServices.aspx

Bye..

Query for finding Team and it’s member in MS DYNAMICS CRM

Hi,

We can use this query to retrieve different teams and the users belonging to that team within Microsoft Dyanmics CRM

Select FT.name TeamName,FSU.Fullname UserName, FSU.Systemuserid from
dbo.FilteredSystemUser FSU
inner Join
dbo.FilteredTeamMembership FTM
on FSU.Systemuserid = FTM.Systemuserid
Inner join dbo.FilteredTeam FT
on FT.TeamID = FTM.TeamID
ORDER BY FT.NAME

Bye

How to – Find shared records in CRM

To find all the records(say lead) shared with any particular user we can make use of following query

It will return us all records(lead) shared with the user directly( i.e. through Action–>Sharing and user)

select fl.subject

from
PrincipalObjectAccess poa , FilteredLead fl, FilteredSystemUser fsu
where
poa.ObjectTypeCode = 4
and poa.ObjectId = fl.leadid
and poa.PrincipalId = fsu.systemuserid
and fsu.domainname=SYSTEM_USER

// fsu.fullname=’name of user’

And to get the records shared with the user indirectly (i.e. through Action–>Sharing and Team (user belongs to that team)

select fl.subject
from
FilteredLead fl, PrincipalObjectAccess poa, FilteredTeam ft
where
poa.ObjectTypeCode = 4

and poa.ObjectId =fl.leadid
and poa.PrincipalId = ft.teamid and

fl.owneridname not in (select fullname from filteredsystemuser
where domainname=SYSTEM_USER)
and
ft.teamid in (select ft.teamid from filteredteammembership fm
, filteredsystemuser fsu, filteredteam ft
where fm.systemuserid=fsu.systemuserid and
ft.teamid=fm.teamid
and fsu.domainname=SYSTEM_USER
)

Bye

How to – Use Left and CharIndex in Oracle

Well i was given the task to get the email id’s of the user which was stored in one of our oracle db table.

But the problem was that we wanted that part of emailid which appears before ‘@’ .

Well coming from SQL Server background i thought it could be acheived using CharIndex and Left Function.

Let’s see what they do

Select Left(‘abcdef’,3)

-> abc

and

select charindex(‘c’,’abcde’)

-> 3

But than as expected there were no functions like charindex and left in Oracle.

After searching i finally managed to found the solution

Inplace of CharIndex we have instr function

select instr(‘ab’,’b’) from dual;

->2

and for left and also right we have

SUBSTR (`ABCDEF’,-5); //Right(..)

SUBSTR (`ABCDEF’,1,5); // Left(…

So finally the query was

substr(emailid,1,Instr(Emailid,’@’)-1)

-1 is used otherwise @ will also come along

And one more thing, to extract username portion from login name i.e nishantr1 from

abccompany\nishantr1 we could write something as following

SUBSTRING(loginname ,charindex(‘\’,loginname)+1, len(loginname)) for sql server.

Bye

Creating Subscriptions on the SQL Reporting Server 2000

Follow the following steps for creating subscriptions
1. Log into the reporting server: http://crmServer/reports
2. Click on _MSCRM Subscriptions Folder
3. Click on the report for which the subscription needs to be created
4. Click on the Subscriptions Tab
5. Click on New Subscription
6. Under Report Delivery Options, choose ‘Report Server File Share’
7. Provide details for the File Name as mentioned below
8. Provide the path of the folder where you want to save this subscription. This folder should be in the root directory of the reporting server. The person who is creating the subscription should have Read/Write Access to this folder.
For Ex: In the development server, a folder called ‘SubscribedReports’ has been created in the C drive. If a person called DomainName\XYZ’ is creating the subscription, then DomainName\XYZ’ should have write access to the ‘Subscribed Reports’ folder. The path to be provided in the reporting server subscription page would be ‘\\.’
9. Choose the render format : PDF format
10. Provide the Username/Password.
11. Under Subscription Processing Options, click on the button ‘Select Schedule’
12. Choose the scheduling date and time and then click on OK – Scheduling Every Monday at 10.00AM for all the reports
13. Under Report Parameter Values, choose the parameters that would be applicable for the specified subscription.
14. When all of the above have been completed, click on OK.

That’s all

Finding all the opportunities shared with the user in CRM

To find the opportunity record shared with a particular user in Microsoft Dynamics Crm

select o.name as OpportunityName,p.AccessRightsMask from opportunityBase o
left join PrincipalObjectAccess
p on p.objectid=o.opportunityid left join systemuser u
on p.principalid=u.systemuserid where u.fullname=’nishant r’

or u.domainname=SYSTEM_USER

AccessRightsMask values and what they mean

AppendAccess 4 – Specifies the right to append the specified object to another object.
AppendToAccess 8 – Specifies the right to append another object to the specified object.
AssignAccess 0x80 -Specifies the right to assign the specified object to another security principal.
CreateAccess 0x10 -Specifies the right to create an instance of the object type.
DeleteAccess 0x20 -Specifies the right to delete the specified object.
ReadAccess 1 -Specifies the right to read the specified type of object.
ShareAccess 0x40 -Specifies the right to share the specified object.
WriteAccess 2 – Specifies the right to update (write to) the specified object.