In this blog I am going to talk about one of the Reporting options available in Microsoft Dynamics GP: SSRS reports. If you own GP then SSRS comes for free. There are over 260 existing SSRS reports that come with GP. Users can modify these Reports and create new Reports which can be imported back in GP. This blog explains in-depth how to get started with GP report customization using SSRS.
What is SSRS?
SSRS stands for SQL Server Reporting Services. It is a server that hosts reports written using the Report Builder tool. These reports are accessible using a secure web browser. Reports are highly customizable, and some decent reports can be built using advanced techniques in SQL. SSRS Reports can be exported and saved to different formats such as excel, pdf, etc. There are good security features that are built in to SSRS.
How to Deploy GP Reports using SSRS
Make sure GP is installed and SSRS is configured and hosted. Existing SSRS reports reside inside GP and need to be deployed as a step during installation. Alternatively, SSRS can be deployed later using the Reporting Tools Set Up window.
Tools -> Setup -> System -> Reporting Tools Setup
Select Report Server Mode as ‘Native’. Report Server URL can be obtained from the Reporting Services Configuration Manager. Go to start and type Reporting Services Configuration Manager, go to Report manager URL and copy the URLs field.
Back to Reporting Tools Set up, Click ‘Deploy Reports’. You will get the following screen, say Yes.
You may get this error, to overcome it login to GP ‘As an Administrator’
You may get the following error in which case go to the “ C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer” and locate the web.config file and add maxRequestLength = “20690” on the httpRuntime section.
After successful installation when you browse to Report manager Url, you will see folders for each company and within them are SSRS reports.
Customize an Existing Report
Existing GP Reports can be customized using SSRS to meet business needs. Customization can vary from just a few field modifications, layout, or advanced changes. Existing Reports can also be used as a Baseline to build on top of when creating new reports.
Here, I am going to show how you can customize Customer Information Report on Customer Card.
Sales -> Cards -> Customer, Select a Customer. Go to Available Reports -> Customer Information
Here is the existing Customer Information Report.
To edit this, go back to the SSRS browser that shows the list of reports. Browse to the report that you want to modify. There are 2 ways in which you can open Report Builder. One option is to start it using the top menu or on the Report-> right click and choose option Edit in Report Builder.
Report Builder
Report Builder is a lightweight tool that lets us build and modify SSRS reports. If you don’t have it installed on your computer then it gets downloaded and installed instantly when you try to open it. Say yes for installation and proceed to viewing the report in Report Builder.
Left side menu has nodes for Data Sources and Datasets which contain connection information and provides data to the report. Data fields can be easily dragged and dropped on the form to create new fields. We are going to make a quick change in the report by coloring the Customer Name field with Red background.
After saving the changes, Customer Name now appears with Red background.
Create a New SSRS Report
Creating a new report and then importing it back into GP is also possible. Using Report Builder, I am going to show you how to create a new report.
Browse to Report Server page on the browser and click Report Builder from the top menu.
Select ‘New Report’ and pick Table and Matrix Wizard. Next step is to create a new Dataset.
Next step is to create a new Data Connection and connect it to the GP database. Then we need to make a query to pull out data from the database. There are a few options to choose data. Data can be chosen from SQL tables, Views or Stored Procedure. If you are comfortable with SQL script, then you can also use the option for ‘Edit as Text.’ You can drag more than one table into the designer and make a relationship between them. Select Fields and let the designer build the query underneath. Or you can write your own SQL queries.
Now that our data is ready, the next step is to make it presentable on the GUI side. Since we picked the type of report as Table or Matrix, there will already be a table, we just have to drag and drop the data fields. To keep it simple, I will only change the Title and drag a couple of columns to the table. This is how our Report will look in Report Builder.
Next step is to import it in GP. For that go back in GP to Customer Card -> Available Reports -> Assign Report.
Pick the newly-created Report from the list in the left and bring it over to the Right side Grid. Click Ok.
Now the new Report is available in the ‘Available Reports’ menu.
Run the report and it opens up in the Browser.
Report Security
Reporting Services uses role-based security to grant user access to a report server. On a new report server installation, only users who are members of the local Administrators group have permissions to report server content and operations. To make the report server available to other users, you must create role assignments that map user or group accounts to a predefined role that specifies a collection of tasks.
There are two types of roles:
- Item-level roles are used to view, add, and manage report server content, subscriptions, report processing, and report history. Item-level role assignments are defined on the root node (the Home folder) or on specific folders or items farther down the hierarchy.
- System-level roles grant access to site-wide operations that are not bound to any specific item. Examples include using Report Builder and using shared schedules.
Another way to add security to SSRS reports is to capture the AD user and add rules and logic to the report to put security around it. This will work on individual AD user credentials.
Now you should understand how to create and edit an SSRS report in Dynamics GP. If you need more information on all the benefits available with Dynamics GP and SSRS reports, get in touch with KTL today. One of our team members will get back to you shortly.
