Getting Started with Microsoft Report Builder

In this Article (Click to Jump to the Topic)

Permissions

 

Before getting started, make sure you have permissions to access and edit SQL Server Reporting Services.  SSRS is accessed via a web browser, preferably Internet Explorer or Microsoft Edge.

The URL for SSRS is typically something like this: http://servername/reports

You will also need read, write, and execute (or full sysadmin) permissions in SQL Server Management  Studio.

Using SQL Server Management Studio for the First Time

 

When using SSMS for the first time, it is recommended that you navigate to Tools > Options and:

  • Enable Line Numbers

  • Uncheck the box that says Prevent Saving Changes that Require Table Recreation

line numbers2.jpg
prevent2.jpg

Create a Stored Procedure

 

Write a stored procedure to pull back the data set that you will use in your report.  For a MinistryPlatform report, include parameters for DomainID, UserID, and PageID.  These are required.

 

You may add additional parameters as needed for the report, such as SelectionID, ProgramID, StartDate, and EndDate.

Use a naming convention that matches existing report stored procedures so that you can easily find it later, such as:

  • report_page_description_my_church_name

  • report_my_church_name_page_description

You can clone an existing stored procedure and rename it.  It is not recommended that you modify an existing stored procedure, as the updates can get overwritten with future software releases.

Stored Procedure.jpg

Using Microsoft Report Builder

 

Open Microsoft Report Builder.  You can select Blank Report from the options shown.

Report Builder.jpg

Connect to the report server by clicking the "Connect" link in the lower lefthand corner of Report Builder.

Connect to Report Server.jpg

Once connected, you can add your data source.  Right click on Add Data Source.

Add Data Source.jpg

If you are properly connected to the report server, you may see your MPReportsDS data source listed.  If so, click on it and click OK.

Connect to Data Source.jpg

Now you can add your Datasets.  Right click on Datasets and click Add Dataset.

Add Dataset.jpg

Your Dataset is the stored procedure you wrote earlier.  Give your dataset a name, select your Data source, select Query type of Stored Procedure, and select your stored procedure from the  list.

choose dataset.jpg

Build Your Report

 

Now you are ready to begin building your report.  Tables are popular ways to display information.  You can click Insert > New Table at the top of Report Builder.

insert table.jpg

Click to drop your table in the white space, which represents your report.  

 

Title and Execution Time are default fields in Report Builder.  You may remove or change them if you would like.

New Table.jpg

You can drag fields from your dataset to the gray header boxes in your table.  You can insert additional columns by right-clicking on the top gray area above your column headers.

Insert Columns.jpg

Once you have your report looking the way you want, you can save it to your computer.

Publish Your Report

 

Now it's time to publish your report in SQL Server Reporting Services (SSRS).  Open up a web browser, preferably Internet Explorer or Microsoft Edge, and navigate to SSRS.

You can upload the report anywhere, but it is recommended that you upload it to a custom folder or to the MP Reports folder.  Simply click on the up arrow, then navigate to your saved report to upload it. 

Upload Report.jpg

Find your report and right click the "..." at the top right, then select Manage.

SSRS Manage Report.jpg

Connect to a data source.  Typically this will be the MPReportsDS data source. 

Set Data Source.jpg

Configure the parameters.  Typically the DomainID, UserID, and PageID parameters will be hidden and pre-populated.  

 

The DomainID will be your Domain GUID, which you can find in your Domain record in MinistryPlatform.  You can use the UserID of a user who has the Administrator security role in MinistryPlatform, and you can use the PageID of the Contacts page if this report is not page-specific.  It will not affect on which pages you can add the report in the platform.

 

You may use other reports for reference on how to configure the parameters for your report.

Configure Parameters.jpg

Add Your Report to MinistryPlatform

 

Create a report record in MinistryPlatform.  Give the report a name; this doesn't have to be the same as what the report is named in SSRS.  Define the file path, which will include the name of the report in SSRS.

*Note: It gets a little confusing because the name of the stored procedure usually doesn't match the SSRS report name or the name of the report as defined in the report record.

Create Report Record in MP.jpg

On the permitted pages sub-page, add the pages where you want the report to be available.

permitted pages.jpg

Finally, add the report to a security role. Oftentimes, it's fine to add it to the Basic Reports security role, but that is not absolutely necessary.

Add Report to Security Role.jpg

Congratulations!  You successfully created and published a report.