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:
e. Specify the default value of the report parameter as per below snapshot:
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:
g. Set the Visibility expression for the table associated with the datasource “getResourcesNotAccessed”.
h. Set the Visibility expression for other table associated with the datasource “getResourcesAccessed”.
I. Now your report is ready to use. Bingo!!
You must be logged in to post a comment.