Custom Reports in FIM2010R2

---

Introduction

Creating custom reports in FIM2010 R2 with SSRS and SCSM is not something that got a lot of documentation out there. So in this article we will see  how to make a simple "Group by user" report with SSRS.

With the same concept and dataset, you should be able to extend this even more.

This document was originally published at http://www.iamblogg.com/fim2010r2-custom-reports/ and has been reproduced here to allow the community to correct any inaccuracies or provide other enhancements before updating the original version of this topic 

Create the first dataset for users and groups

Its a good idea to create a new folder where you put all your custom reports. Create a folder named Custom Reports.

http://www.iamblogg.com/wp-content/uploads/2014/06/0.png

First we have to create a new dataset to retrieve all users group memberships. For this we`r gonna use the built-in Reporting Builder.

http://www.iamblogg.com/wp-content/uploads/2014/06/1.png

Choose the DWDataMart as connection.

http://www.iamblogg.com/wp-content/uploads/2014/06/2.png

http://www.iamblogg.com/wp-content/uploads/2014/06/3.png

Now you should see all the views, like this

http://www.iamblogg.com/wp-content/uploads/2014/06/4.png

Start by selecting the following attributes from FIMEntityDimvw: EntityDimKey FIMDisplayName FIMEntityDimKey

http://www.iamblogg.com/wp-content/uploads/2014/06/5.png

Select the following attributes from FIMGroupDimvw: FIMDisplayName FIMGroupAccountName FIMGroupDimKey FIMGroupType

If you need more information from the Group objects in your report you can choose more attributes.

http://www.iamblogg.com/wp-content/uploads/2014/06/6.png

Select the following attributes from FIMGroupHasComputedMembersFactsvw: CreatedDate DateKey DeletedDate FIMCreatedDate FIMCreatedRequestID FIMDeletedDate FIMDeletedRequestID FImGroupDimKey FImGroupHasComputedMembers_FIMEntityDimKey

http://www.iamblogg.com/wp-content/uploads/2014/06/7.png

Select the following from FIMPersonDimvw: FIMPersonAccountName FIMPersonDepartment If you need more information from the Person objects in your report you can choose more attributes.

http://www.iamblogg.com/wp-content/uploads/2014/06/8.png

Hit Auto Detect to de-select auto assignment.

http://www.iamblogg.com/wp-content/uploads/2014/06/9.png

Click to create a new Join criteria.

http://www.iamblogg.com/wp-content/uploads/2014/06/10.png

Do the following joins

http://www.iamblogg.com/wp-content/uploads/2014/06/11.png

Filter out all distribution groups.

http://www.iamblogg.com/wp-content/uploads/2014/06/12.png

Try running the report to see if there is any results :)

http://www.iamblogg.com/wp-content/uploads/2014/06/13.png

Yeah!

http://www.iamblogg.com/wp-content/uploads/2014/06/14.png

Filters out all person <-> group relationship that is no longer active. (Member left the group).

http://www.iamblogg.com/wp-content/uploads/2014/06/15.png

Save the dataset in Custom Reports folder

http://www.iamblogg.com/wp-content/uploads/2014/06/16.png

Create the second dataset for all departments

We gonna use this to make a parameter in the report so that it is possible to filter by Users Departments.

http://www.iamblogg.com/wp-content/uploads/2014/06/17.png

Select the following attributes from FIMEntityDimvw: EntityDimKey

http://www.iamblogg.com/wp-content/uploads/2014/06/19.png

Select the following attributes from FIMPersonDimvw: FIMPersonAccountName FIMPersonDepartment

http://www.iamblogg.com/wp-content/uploads/2014/06/20.png

Select Count on EntityDimKey and FImPersonAccountName and Grouped By FIMPersonDepartment

http://www.iamblogg.com/wp-content/uploads/2014/06/21.png

Do the following joins

http://www.iamblogg.com/wp-content/uploads/2014/06/22.png

You should see something like this, only with your departments. (If your users have the Department attribute assigned in FIM).

http://www.iamblogg.com/wp-content/uploads/2014/06/23.png

Now save it to the Custom Reports folder.

http://www.iamblogg.com/wp-content/uploads/2014/06/24.png

Create the actual report

Lets start by creating it through the Table or Matrix Wizard

http://www.iamblogg.com/wp-content/uploads/2014/06/25.png

Browse and select the first dataset we made. (AllActiveSECGroupMemberships)

http://www.iamblogg.com/wp-content/uploads/2014/06/26.png

Select FIMPersonAccountName as Row groups, and FIMGroupDimvw_FIMDisplayName as Values.

http://www.iamblogg.com/wp-content/uploads/2014/06/27.png

Lets just continue with the defaults.

http://www.iamblogg.com/wp-content/uploads/2014/06/28.png

Lets just continue with the defaults.

http://www.iamblogg.com/wp-content/uploads/2014/06/29.png

The defaults should look like this.

http://www.iamblogg.com/wp-content/uploads/2014/06/30.png

If we try to run the report, it looks like this.

http://www.iamblogg.com/wp-content/uploads/2014/06/31.png

Lets add a dropdown menu with all Departments

Right click on Dataset (left) and Add Dataset.

http://www.iamblogg.com/wp-content/uploads/2014/06/32.png

Browse and select the AllDepartments dataset we created in step two.

http://www.iamblogg.com/wp-content/uploads/2014/06/33.png

Right click Parameters and select Add Parameter. This will create a drop down with departments.

http://www.iamblogg.com/wp-content/uploads/2014/06/34.png

Give it a reasonable name and prompt.

http://www.iamblogg.com/wp-content/uploads/2014/06/35.png

Go to Available Values and do the following

http://www.iamblogg.com/wp-content/uploads/2014/06/36.png

We need to make the parameter as a filter on the dataset. Right click AllActiveSECGropMemberships dataset select Dataset Properties.

http://www.iamblogg.com/wp-content/uploads/2014/06/37.png

Select Filters and do the following

http://www.iamblogg.com/wp-content/uploads/2014/06/38.png

Run your report!

http://www.iamblogg.com/wp-content/uploads/2014/06/39.png

End results

http://www.iamblogg.com/wp-content/uploads/2014/06/40.png

With just a little bit of brushing! http://www.iamblogg.com/wp-content/uploads/2014/06/41.png