This beginner's level tutorial will walk you through creating an SSRS using one of the standard views that ship with Dynamics GP. A 'view' is a SQL object that gathers together the tables needed to see something. We're going to demo using the SalesLineItems view, which brings in the SOP100 (sales header) table, SOP10200 (sales lines), the RM00101 (customer Master) table, and others. It will allow us to see everything that web want concerning Sales Orders.  

OK? Here we go!

Start by opening Report Builder (we're using the version that ships with SQL 2014). The way we open it is to open Reporting Services itself and clicking on the Report Builder button on the tool bar (but there are other ways)

Choose New Report and Table or Matrix Wizard

Choose Create a DataSet

Choose Browse, then DataSources, then the company data source. In our case, that'll be GPTWO. 

In the Design a Query window, expand Views and scroll down to SalesLineItems. Without checking it, expand SalesLineItems by clicking on the plus symbol.

Carefully check the fields that we want in the report: SOP Type, SOP Number, Item Number and Description, Customer Number and Name, Qty, Item Price and Cost, Extended Price and Cost, and GL Posting Date.

In the Applied Filter area, add 'SOP Type like Invoice' and 'GL Posting Date is more than or equal to' and then check the parameter box.

  In the Arrange fields window, add Customer Number to the Row Groups area, and all the other fields except Customer Name to the Values area.

 In the Choose the layout form, uncheck 'Expand/collapse groups'

 Choose Next, then Finish, and you'll get something link the below.

 In the Report Data area, expand Datasets and right click on DataSet1. Choose Properties. Choose Parameters. Edit the parameters to be as shown.

 Choose 'query from the left menu. Scroll down in the text area and edit the WHERE clause to be as shown.

 Click OK, go back to the main design surface.

In the left menu expand parameters. Delete the GLPostingDate parameter.

 Double Click on the Start_GL_Post_Date parameter. Click on Default Values from the left menu. Choose Specify values, Click Add, and add a date. For the TWO compay, we'll put 1/1/2016 because that'll give us good data.

Note that it a live production company we would not use default dates but in development it's handy because they allow us to quickly run the report and we're going to toggle back and forth quite a few times. Having to type those dates in every time just makes the process take longer.

 Add a default date of 1/31/2016 for the End_GL_Post_Date param. (again, these dates work for the TWO company)

Run the report, you should see something like the below. Now, we're going to make it prettier.

 Go back to design and click in the grid, this will activate the borders, as shown below. Adjust the width of the Customer Number, item number, and item description fields. Toggle back and forth between Design and Display modes to get the best width.

 Get the border again, and right click on the border above SOP Type, delete it.

Right click in the quantity field and choose Text Box Properties

Format the Quantity field as shown

 Format all the other numeric fields with two decimal places, as shown

 The finished report should look something like this:

 Choose save from the top menu. Navigate to TWO/Sales and name your report.

Open up SSRS and admire your work!