Want to use SQL Server Reporting Services (SSRS) to create custom dashboards for yourself, your users and your executives? These dashboards can be deployed within GP itself or in a tool such as Business Analyzer which is available on phones and tablets. Here are some simple tips to guide you in the right direction.
Getting the Data
The first step is to get your data and create your query. Here is a GP table cheat sheet for easy reference.
MODULES
CM = Bank Reconciliation
FA = Fixed Assets
GL = General Ledger
HR = Human Resources
IV = Inventory
PM = Payables Management
POP = Purchase Order Processing
RM – Receivables Management
SOP = Sales Order Processing
SY = System/Company
UPR = Payroll
TABLE NUMBERS
00xxx = Card/Master Record
1xxxx = Work Records
2xxxx = Open Records
3xxxx = History Records
4xxxx/5xxxx = Setup Records
Building the Report
Using Report Builder within the SQL Server Reporting Services Website will easily guide you through a drag and drop wizard to create your charts and graphs. Once the data is there, the next step is formatting. Report Builder formatting works very similarly to the Office products that you’re already familiar with, so the learning curve is almost non-existent.
If you are planning to use these charts in the GP home page dashboard, the specific sizing of the chart is very important – use the guideline below to appropriately size them for your homepage.
The Results
Below you will find a sample GP home page dashboard and a sample business analyzer for Windows dashboard.
SAMPLE GP HOME PAGE DASHBOARD
SAMPLE BUSINESS ANALYZER FOR WINDOWS DASHBOARD
If you have any questions or need assistance with creating SSRS reports, feel free to contact us.
Using Common Date Functions in SSRS Reports
When writing any date-driven reports, you will certainly come across the challenge of finding the correct functions for defaulting dates. Below are some common date functions you may need for queries in your SQL Server Reporting Services (SSRS) reports.
NOTE: If you are using these by themselves in Management Studio to test, use “select” in front of the line and either declare your parameter or switch out the @DateParameter with a test date (ex. ‘8/18/2012’). Otherwise, embed them as a part of the query (ex. Where docdate > x) with x being the statement below.
First Day of Current Month – DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0)
Last Day of Current Month – DATEADD(dd,-1,DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0)))
First Day of Next Month – DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0))
Last Day of Previous Month – DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0))
First Day of Current Quarter – DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0)
Last Day of Current Quarter – DATEADD(dd,-1,DATEADD(qq,1,DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0)))
Last Day of Previous Quarter – DATEADD(dd,-1,DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0))
First Day of Current Year – DATEADD(yy, DATEDIFF(yy,0, @DateParameter), 0)
First Day of Previous Year – DATEADD(yy, DATEDIFF(yy, 0,DATEADD(yy, -1, @DateParameter)), 0)
Last Day of Previous Year – DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0, @DateParameter), 0))
This Day Last Year – DATEADD(yy, -1, @DateParameter)
