There are a ton of ways to get data for an SSRS report. You can use the query builder and import and join the tables your self. You can write a stored procedure. You can write a view, or use one of the supplied views that come with Dynamics GP. This article will explore (step by step) how to create an SSRS report using Report Models.

Hang on, here we go!

We found two ways to get started using Report Models, this seems to be the easier way:

Open SSRS. Click on Report Models, then TWO. Hover over TWO_SalesOrderProcessing and click the drop down arrow. Click Load in Report Builder

 This will open Report Builder, and will add this report model as a Data Source

 Right click on DataSets and choose Add DataSet. Choose 'Use a dataset embedded in my report' and then choose TWO_SalesOrderProcessing from the Data Source dropdown.

Click on Query Designer.

 In the Entities area, scroll down and choose Sales Line Item

The Fields area will change to show the Sales Line Item fields. Start to select the fields that we need for this report, they'll mostly be at the top:

SOP Type, SOP Number, Item Number and Description, Total Qty, Total Extended Cost and Price, Total Unit Cost and Price, Customer Number.

Just to have fun with the functionality, click the lookup immediately to the right of Entities and look up Customer Name. Then double click on it in the Fields window to add it. Do the same for GL Posting Date.

 Click OK

In the left menu, click on parameters and edit them to be as shown. Be sure to use the @ in the name and the [brackets] in the value.

 Click OK to close the window so that the parameters will save.

Re-open the dataset window, and click on Filters, add filters as shown below. When adding the GL Posting Date parameter, click on the [fx] symbol.

The parameters can be selected as shown below. in the Values window double click on the parameter name to bring it into the Set Expression window, then click OK 

Click OK

In the left menu expand the parameters and double click on the Start_GL_Post_Date parameter. Change the data type to Date/Time

 Add a default date as shown below

 Click OK to close the window

In the top menu, add a Matrix as shown below

 Add Customer_Number to the Row Groups area, then add all the rest except Customer Name to the Values area

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!