This report is for all companies. All companies have bank accounts, therefore, all companies need to know how much money is in the bank.

Every manager and business owner needs to see how much cash is in the bank. Usually, this person is not the one who is responsible for reconciling the bank accounts to the bank, so I would like to add the last reconciliation date (not the date it was actually reconciled, but the date it was reconciled through).

We realize there is a very nice Business Analyzer report that shows the cash position compared to previous periods. We really like this report, but it does not show us when the bank account was last reconciled, nor does it give us a breakdown of how much cash is in each bank account.

For this report, we choose to use SQL Report Builder 3.0, so we will access this report in Microsoft Dynamics GP Business Analyzer.

There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.

Non-technical matter

Let's get started!

Let's prepare and connect to the data

Know what you want. We've already completed this step, as shown in the following screenshot:

Tip

As always, we must know what we want our end result to look like before we start. Draw it on a paper or perform a mock-up in Excel. Starting with the end in mind will save you many, many hours of time. We'll say this over and over again because it's that important! The preceding screenshot is what we want our report to look like.

Let's make a connection to the GP data:

1. We'll start by opening SQL Server Report Builder 3.x (it should have been downloaded for you in the Technical Matter steps).
2. When you first open Report Builder, the New Report or Dataset window will open. If it does not open, click on the Report Builder icon in the top-left corner of the ribbon and select New to open it:
   
3. In the New Report or Dataset window, select New Report, and then select Table or Matrix Wizard.
4. The next screen is to choose a dataset. The Create a dataset option is marked by default. Click on Next.
5. The next screen is to choose a connection to a data source. Click on the New button, and the Data Source Properties window will open.
6. Give this data source the name BankBalance, keep the connection type Microsoft SQL Server, and click on the Build button:
   
7. Then, the Connections Properties window will open.
8. Enter the name of your Microsoft SQL Server, your GP user ID as the User Name along with the GP password, and the database that this report is for. Then, click on OK to close the Connection Properties window:
   
9. Click on OK to close the Data Source Properties window.
10. Click on Next in the New Table or Matrix window.
11. On the Enter Data Source Credentials window, enter your Network or AD login and password, and click on OK.

    Tip

    If you select Use Windows Authentication on the Connections Properties window, you will not be prompted to enter your credentials again.

12. We are now prompted to Design a query. Fortunately for us, all the information we need is included in a view that Microsoft includes with Dynamics GP, so it is just a matter of selecting the correct view. Click on the plus sign next to Views in the Database View pane, find CM00500, and select it by checking the box:
    
13. Now that we've selected the view, we want to make sure that only active bank accounts appear on our report, so we'll need to enter a filter. Click on the add filter icon, and the first field will show up in the Applied filters pane:
    
14. Now we'll need to edit the filter, as we want to filter on a different field. Click on the field name, in this case it is CHEKBKID, and a drop-down list will appear:
    
15. Select INACTIVE and the drop-down list will be closed. Make sure the Operator value remains as is, and click on the Value field and enter a 0. Here, "0" means active and "1" means inactive. Click on Next and the Arrange fields window appears. Click on Next to close the New Table or Matrix window:
    

Let's build the Report:

1. Drag DSCRIPTN to Row groups and both CURRBLNC and Last_Reconciled_Date to the Values area. CURRBLNC in the Values area will appear with Sum() on it and that is fine. Click on Next:
   
2. On the Choose the layout window, change nothing, leaving Show subtotals and grand totals and Expand/collapse groups checked. Leave the Blocked, subtotal below option marked. Then, click on Next.
3. The Choose a style option is about fonts and color schemes. Feel free to change it for your report. We're leaving the default of Ocean selected. Then, click on Finish.
4. Time to save your work. Click on the Save icon in the top-left corner. For now, save it as you would do with an Excel file.

Let's edit and format the report:

1. Now that we've connected the data and started building, let's take a look at what the report looks like so we know what formatting we need or want to do. In the top-left corner, click on the Run button. When prompted, enter your GP user ID and password:
   
2. Your report should look like the following screenshot:
   
3. There is a lot wrong with this report: we have no title, the column headers are not easy to read, the fields are not formatted correctly, and the date does not appear unless we open the account by clicking on the + sign. To fix these items, click on Design at the top-left corner, and we'll be back to the pasteboard (or the report design area).
4. Where it reads Click to add title, click on it and enter Bank Balances and press the Tab key:
   
5. Double-click on the DSCRIPTN column header and enter Account and press the Tab key:
   
6. Click on the CURRBLNC column header and enter Balance. Click on the Last Reconciled Date column header and remove the word date at the end.
7. Save the file as a precaution by navigating to File | Save.
8. Click on the row between DESCRIPTN and Total, right-click and delete row. It should now look like the following screenshot:
   
9. Right-click on the Balance field in the body of the report and choose Text Box Properties:
   
10. In the Text Box Properties window, select Number, and then select Currency from the Category section. We want 1000 separator and our negative numbers to have ( ) around them. Click on OK:
    
11. Right-click on Last Reconciled Date in the body and select Text Box Properties.
12. In the Text Box Properties window, select Number, and then select Date from the Category section. Select the date format you like; we prefer XX/XX/XX. Click on OK:
    
13. Right-click on CURRBLNC on the total line and repeat the preceding steps where you right-clicked and chose Text Box Properties, formatting as a currency.
14. Save the file as a precaution by navigating to File | Save.

In theory, the report is complete. You may want to resize the fields in the body, depending on where you choose to deploy the report. We chose to make our columns wider and the print date and time larger and bold. We resized our fields by clicking on the table and then clicking on the line between the columns; resizing as we would in Excel. We changed the font of the print date by clicking on the [&Execution Time] field in the bottom-right corner of the report and then selected font options in the ribbon.

Format for use in Business Analyzer (GP reporting tool):

1. Run the report again, as we did earlier, to determine what remains to be formatted:
   
2. To format this report to display in the Business Analyzer tool or Dynamics GP home page, we'll first need to resize the report. Click on the Design button in the top-left corner to pull up the designer window again.
3. Open up Property Pages in Report Builder. You'll find the icon in the Properties pane on the right-hand side of the screen:
   
4. We set up our page as landscape with a width of 7.1in and a height of 4.1in, with a margin of only 0.5in on all sides. Failing to make adjustments like these will make the report appear very tiny and unreadable in Business Analyzer:
   
5. This report is now ready to upload to your ReportServer, where you have your other Dynamics GP SSRS reports deployed:
   

   Tip

   Refer to your Microsoft GP manuals and/or partner on how to deploy these reports. Keep in mind this book is about the information, not the tools.

Okay, so why is this report a big deal? Simple: it tells you that you have cash and on which date this information was confirmed as accurate. Would you drive a car without knowing how much gas you have in it?