Active Users report Last Accessed

Environment: Project Server 2010, SQL Server 2008 R2, Business Intelligence Development Studio (BIDS)

Background: Client requested for a report requirement to find out the users those have connected/accessed the system in last 12 months or had not accessed more than 12 months.

Solution: Follow the below steps to develop the SSRS report. Please note we are hitting ProjectServer_Published DB in this report which is not supported by Microsoft. Please use it at your own risk.

a. Create the Shared Data source pointing to Project Server Published DB.

b. Create the report dataset with the name of ‘getResourcesAccessed’

–Fetch the Resources those have accessed in last 12 months.

Select

Res_Name as ResourcesName,

WRES_EMAIL as EmailID,

WRES_LAST_CONNECT_DATE as LastConnect,

RES_TYPE

from

dbo.MSP_RESOURCES

Where RES_TYPE Between 1 AND 19 –Filter the active resource who can logon to PWA

AND (WRES_LAST_CONNECT_DATE > Dateadd(yyyy, -1, getdate()) AND WRES_LAST_CONNECT_DATE is NOT NULL)

Order by WRES_LAST_CONNECT_DATE, RES_NAME

c. Create the second dataset called “GetResourcesNotAccessed”.

–Fetch the Resources those had not accessed in last 12 months.

Select

Res_Name as ResourcesName,

WRES_EMAIL as EmailID,

WRES_LAST_CONNECT_DATE as LastConnect,

RES_TYPE

from

dbo.MSP_RESOURCES

Where RES_TYPE Between 1 AND 19 –Filter the active resource who can logon to PWA

AND (WRES_LAST_CONNECT_DATE <= Dateadd(yyyy, -1, getdate()) OR WRES_LAST_CONNECT_DATE is NULL) Order by WRES_LAST_CONNECT_DATE, RES_NAME

d. Create the Report Parameter as per below snapshot:

d

 

e. Specify the default value of the report parameter as per below snapshot:

e

f. Add the two tables in the report design mode. The next step is to associate the datasets created in step b and c then add the fields in both table. Refer the snapshot below:

f

g. Set the Visibility expression for the table associated with the datasource “getResourcesNotAccessed”.

g

h. Set the Visibility expression for other table associated with the datasource “getResourcesAccessed”.

h

I. Now your report is ready to use. Bingo!!

i

 

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: