How to Set an Active Users Report

Posted By Posted by: EPM Partners on April 29, 2014

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

Background: A client requested a report requirement to find out the users that had connected/accessed the system in the last 12 months or had not accessed the system for 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.

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

2. 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

 

3. Create the second dataset called “GetResourcesNotAccessed”.

Fetch the Resources of those who have not accessed the system 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

 

4. Create the report parameter as per the below snapshot:

d

 

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

e

 

6. Add the two tables in the report design mode. The next step is to associate the datasets created in step 2 and 3 then add the fields in both tables. Refer to the snapshot below:

f

 

7.Set the visibility expression for the table associated with the datasource “getResourcesNotAccessed”.

g

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

h

9. Now your report is ready to use.

i


Blog Posted In Blog Posted In: How to, Project Server
Blog Posted In 

Leave a Reply

Your email address will not be published.