Open one of the stock Dynamics GP reports in Report Builder. If you've never done that before,follow this guide.

Renaming

Step one when we modify a report is renaming. This is very important - If you were to call and ask for assistance on SSRS reports, or if you were to upgrade, the first thing that would happen is that all the stock reports will get overwritten. And your changes will be lost.

So... let's rename. In Report Builder click on File > Save As and give the report a new name. It's our convention to put the word Modified after the report, like you see below.

 If you'd like to follow along with us, we're in the TWO company on GP2015. We're modifying the Financials Trial Balance Detail report, which is now called Trial Balance Detail Modified.

If you run the report with the parameters shown below you'll get data.

Note the space between the Orig. Master Name field and the Debit field. For our first modification we'll be adding the distribution reference field in that area

 Adding a field

 Click in the grid area, and find the detail area. We cover Report Builder basics in Part 1, if you need a refresher. Find the empty cell to the right of [ORMSTRNM] and hover, you'll get the field chooser icon. Click on the icon to get the field list, and choose the REFRENCE.

 Delete the label that was auto-created, and re-add the label two cells lower. Just click in the field and type.

  Run the report and admire your handiwork.

 Create an Expression

Now we'll create an expression using the Expression Builder.

Remember that there are (basically) three types of fields

• Labels: Just plain text that you type into the report cell
• Fields: Database fields that we add using the method detailed above
• Expressions: These can be any mix of plain text, fields, and more complicated formulas and functions.

Right click on the detail cell that has the [ORMSTRNM] field, choose the expression builder 

 Edit the expression to be the way you see it below. When editing fields, it's convenient to click on the Fields tab and double click on the field that you want.

 Run the report and admire your work.

 Add a Logo

 Next, we'll add a logo. On this report, we need to make some space to add a logo. Click on the three fields at the top of the report and resize them as shown.

From the top menu, click on Insert and then Image

Next, draw a square in the top right corner. When you release the cursor the Image Properties dialog will open

Click on Import and navigate to an image field and select it. Click OK.

Nice work!

 Default Parameters

We'll need to open this report dozens of times to make these modifications, it would be a lot easier if we didn't have to select the parameters every time. We rarely default the parameters in production, but we're trying to speed up our work here.

In the left hand column, expand Parameters and click on double click on I_tHistoryYear.

Click on Default Values and add a 0 as shown.

Double click on Year, and add '2016' as shown

This next one is trickier. We need to default the starting and ending account, but since it's a drop down we need to match the exact value of the drop down list. There are a couple of ways to do this... but for now just copy the text below and create a default in the Start Account and End Account fields. Copy the text inside the quote marks

'000-4100-00                                                       '

We left 'Sort By' empty, but all the rest should be defaulted

Conditional Formatting

Next, we'll demonstrate conditional formatting. We're going to make the Credit column Red, if the value of the field is greater than 500.

First, in the detail row, right click on the Credit field and choose Text Box Properties 

 Choose the Font tab, then click on the expression symbol next to the Color dropdown.

 Edit the formula to be as shown below. To do this, I typed in the function

IIF( , , )

And then placed my cursor between the commas and chose the values from the chooser below.

 The IIF function is a common VBA function and it works in Excel, also. We'll not cover that here, but you can research it easily. While you're at it, research a few of the functions, see what they do. I've pictured the IIF function below

 Special Fields

Last, were going to look at special fields. From the menu across the top, click on Insert and then Text Box.

Draw a text box in the bottom left corner of the report, outside of the Matrix. Right click in the text box, choose Expression

 In the Category list choose Date & Time, then double click FormatDateTime. Notice that it appears in the top part. Also notice the Example in the bottom right, we're going to copy that.

Click on Built-in Fields, then double click on Execution Time and you'll have the Expression below

Last, type in

, DateFormat.ShortDate)

as shown below, add

"Report Date: " &

at the beginning.

 Close and run, admire your work

 Is there something else that you'd like to learn? Ask us in the comment area below, and we'll get on it!