In the movie Jerry Maguire, the signature line is "Show me the money!" It's repeated over and over again. Every business owner asks a very similar question, "Where did the money go?", and they ask it over and over again.
This time we are the company in need of a report. We are a business services consulting and software sales organization.
One of the owners wants to know where the cash is being spent. The owner feels this will assist him in keeping close tabs on expenditures as they are occurring rather than at the end of the period.
We will produce a simple list subtotaled by who was paid and how much they were paid. Monitoring this report on a regular basis (for example, weekly) could be confusing if you base it on a transaction date (that is, post something for today with a GL posting date of last week and it would miss the report altogether). So, we will monitor this information by the actual date it was posted. Also, since it is cash that is being monitored, we will display incoming cash as well.
For this report, we will use Microsoft SQL Report Builder as our tool. This will allow us to deploy the report in Microsoft SharePoint, which allows our owner to access it.
There are two parts to these steps. The first part is technical, containing steps that involve work on 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.
If you are not experienced in working on Microsoft SQL Server, request your IT Department or Microsoft GP partner to assist you. You may corrupt your data and completely break your system if you are not careful. You should also have the SQL Server system administrator password.
Create the following view in SQL Server, using SQL Server Management Studio.
For directions on creating a view, follow the technical matter steps of the first report in Chapter 2, Business Intelligence for the General Ledger.
This is the kind of view that you can literally run your business on. It's worth the effort to see how cool this is:
CREATE VIEW view_BI_BR_TRX AS SELECT dbo.GL00105.ACTNUMST AS 'Account_Number', dbo.GL00100.ACTDESCR AS 'Account_Name', dbo.CM00100.CHEKBKID AS 'Checkbook_ID', dbo.CM00100.CURRBLNC AS 'Current_Bank_Balance', dbo.GL20000.JRNENTRY AS 'Journal_Number', dbo.GL20000.TRXDATE AS 'Transaction_Date', YEAR(dbo.GL20000.TRXDATE) AS 'Transaction_Year', MONTH(dbo.GL20000.TRXDATE) AS 'Transaction_Month', CASE dbo.GL20000.SERIES WHEN 2 THEN dbo.GL20000.REFRENCE ELSE dbo.GL20000.ORMSTRNM END AS 'Source_Name', dbo.GL20000.DEBITAMT AS 'Debit', dbo.GL20000.CRDTAMNT AS 'Credit', dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT AS 'Net', CASE WHEN (dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT) < 0 THEN 'Decrease' ELSE 'Increase' END AS Effect, CASE dbo.GL20000.SERIES WHEN 2 THEN 'Financial' WHEN 3 THEN 'Sales' WHEN 4 THEN 'Purchasing' WHEN 5 THEN 'Inventory' WHEN 6 THEN 'Payroll' WHEN 7 THEN 'Project' ELSE 'Other' END AS Series, dbo.GL20000.ORPSTDDT AS 'Date_Posted', dbo.GL20000.SOURCDOC FROM dbo.GL20000 INNER JOIN dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN dbo.CM00100 ON dbo.GL00105.ACTINDX = dbo.CM00100.ACTINDX WHERE (dbo.GL20000.VOIDED = 0) AND (dbo.GL20000.SOURCDOC <> 'BBF') UNION SELECT dbo.GL00105.ACTNUMST AS 'Account_Number', dbo.GL00100.ACTDESCR AS 'Account_Name', dbo.CM00100.CHEKBKID AS 'Checkbook_ID', dbo.CM00100.CURRBLNC AS 'Current_Bank_Balance', dbo.GL30000.JRNENTRY AS 'Journal_Number', dbo.GL30000.TRXDATE AS 'Transaction_Date', YEAR(dbo.GL30000.TRXDATE) AS 'Transaction_Year', MONTH(dbo.GL30000.TRXDATE) AS 'Transaction_Month', CASE dbo.GL30000.SERIES WHEN 2 THEN dbo.GL30000.REFRENCE ELSE dbo.GL30000.ORMSTRNM END AS 'Source_Name', dbo.GL30000.DEBITAMT AS 'Debit', dbo.GL30000.CRDTAMNT AS 'Credit', dbo.GL30000.DEBITAMT - dbo.GL30000.CRDTAMNT AS 'Net', CASE WHEN (dbo.GL30000.DEBITAMT - dbo.GL30000.CRDTAMNT) < 0 THEN 'Decrease' ELSE 'Increase' END AS Effect, CASE dbo.GL30000.SERIES WHEN 2 THEN 'Financial' WHEN 3 THEN 'Sales' WHEN 4 THEN 'Purchasing' WHEN 5 THEN 'Inventory' WHEN 6 THEN 'Payroll' WHEN 7 THEN 'Project' ELSE 'Other' END AS Series, dbo.GL30000.ORPSTDDT AS 'Date_Posted', dbo.GL30000.SOURCDOC FROM dbo.GL30000 INNER JOIN dbo.GL00100 ON dbo.GL30000.ACTINDX = dbo.GL00100.ACTINDX INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN dbo.CM00100 ON dbo.GL00105.ACTINDX = dbo.CM00100.ACTINDX WHERE (dbo.GL30000.VOIDED = 0) AND (dbo.GL30000.SOURCDOC <> 'BBF') GO GRANT SELECT ON view_BI_BR_TRX TO DYNGRP
See the technical portion of the first report (Balance sheet dashboard) in Chapter 2, Business Intelligence for the General Ledger for more details on creating a view and granting security.
Let's prepare and connect to the data:
Know what you want. We've already completed this step, as shown in the following screenshot:
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 result 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 shows, how we want our report to look like.
Let's make a connection to the GP data. Flip back a few pages to the previous report to see the next few steps with screenshots.
Let's build the report:
This view will display Source_Name as the reference if the transaction originated in the financial series. If the transaction originated in any other series, it will display the originating master name (that is Vendor, Customer, or Employee).
Let's edit and format the report:
Let's add a parameter:
Let's add some finishing touches:
Cash In and Out posted since
. Make sure to leave space after since
, and then click on the outside of the title. We just hit the spacebar a couple of times. We also used the handles around the title, dragging it to the width of the report.Building this report with the Fabrikam database will yield all kinds of strange dates. Change the year in the dates to a year two or three in the future (for example, in 2014, change it to 2016) and run, then change the year to one more in the future (in our example, 2016 becomes 2017) and run. You'll see the data change in the report.
With the lower portion looking like the following:
Here are some additional areas you may want to change: