Download Report

Sage 300 Construction and
Real Estate
Sage SQL Gateway and
Executive Dashboard
Release Notes
This is a publication of Sage Software, Inc.
Copyright © 2015 Sage Software, Inc. All rights reserved.
Sage, the Sage logos, and the Sage product and service names mentioned herein are registered
trademarks or trademarks of Sage Software, Inc. or its affiliated entities. All other trademarks are the
property of their respective owners.
Your use of this document and the Sage product(s) described herein is governed by the terms and
conditions of the Sage End User License Agreement ("EULA") or other agreement that is provided with or
included in the Sage product. Nothing in this document supplements, modifies or amends those terms and
conditions. Except as expressly stated in those terms and conditions, the information in this document is
provided by Sage "AS IS" and Sage disclaims all express, implied or statutory warranties of any kind,
including but not limited to the warranties of merchantability, fitness for a particular purpose or of noninfringement. No implied license is granted to you under any Sage intellectual property or trade secret right.
Sage reserves the right to revise, supplement or remove information in this document at any time without
notice to you or others.
Last updated: March 25, 2015
Table of Contents
Sage SQL Gateway
1
Requirements
1
Installing
1
Upgrading from a prior version
2
Configuring
2
Issues and solutions
3
Sage Executive Dashboard
4
Installing
4
Upgrading from a prior version
4
Accessing
4
Features
5
Issues and solution
7
Support and assistance
8
Supporting documentation
8
In-product assistance
8
Training
8
Technical assistance
8
Appendix A: Enabling .NET 3.5
Windows 8 and Windows 8.1
Windows Server 2012
9
9
10
Release Notes Page iii
Sage SQL Gateway and Executive Dashboard
Sage SQL Gateway
Sage SQL Gateway takes your existing Sage 300 Construction and Real Estate data in Pervasive format,
duplicates and transforms it ("replicates" it), and stores it in a Microsoft® SQL Server® database called a
data warehouse. You can view the data in special reports, charts, and graphs in the Executive Dashboard,
as explained in the next chapter. In addition, you can set Crystal Reports to run reports using SQL Server
data in the warehouse, thereby taking advantage of SQL server to improve report performance.
If you used an older version of Sage SQL Gateway (version 2.1 or earlier), you will notice that this version
has a new window layout that is more user friendly. Instead of having to access an external help system
for information, text in the windows provides helpful information. You can also hover your mouse over the
fields to view more information.
Requirements
In order to use Sage SQL Gateway version 4, you must be using Sage 300 Construction and Real Estate
version 13.1 revision 5 or later. To determine your Sage 300 Construction and Real Estate version, open
TS-Main or an application and then select Help > About on the menu.
If you are plan to install on machines with the Windows 8, Windows 8.1, or Windows Server 2012
operating system, you must first enable .NET 3.5. See Appendix A for instructions.
For information on the supported configurations and system specifications, see the Appendix of the
Sage SQL Gateway with Executive Dashboard Installation and Reference Guide. Sage SQL Gateway
requires a dedicated instance of SQL Server 2012 or SQL Server 2014. We recommend that you allow the
Sage SQL Gateway installation routine to install and configure a SQL Server Express 2012 instance for
you. If you choose to install a SQL Server instance manually, follow the instructions in the Appendix of the
Sage SQL Gateway with Executive Dashboard Installation and Reference Guide.
NOTE: If you have an older version of Sage SQL Gateway, you must uninstall it completely before
installing version 4. If you have a prerelease version of Sage SQL Gateway, the Executive
Dashboard Add-On, and/or the Executive Dashboard, you must uninstall them completely before
installing version 4.
Installing
For detailed information about installing Sage SQL Gateway, see the Sage SQL Gateway with Executive
Dashboard Installation and Reference Guide.
Release Notes Page 1
Sage SQL Gateway and Executive Dashboard
Sage SQL Gateway
Upgrading from a prior version
If you have a prior version of Sage SQL Gateway, you must completely remove the prior version. This
involves two steps to uninstall the old version and one to install the new version:
1 Open the control panel, select Uninstall a Program, and uninstall Sage SQL Gateway and any
associated programs, such as the Sage Executive Dashboard Add-On.
2 Download and run the Sage SQL Gateway installation file, and then select the Uninstall option. (This
removes the old version's install files.)
3 Either download the installation file again or double-click the executable file (from the Download folder),
and choose the option to install Sage SQL Gateway (the new version).
NOTE: If you do not perform both steps 1 and 2 before performing step 3, the installation may
appear to be successful but you will not have the new version of Sage SQL Gateway.
Configuring
After installing Sage SQL Gateway, select Start > All Programs > Sage > Sage SQL Gateway > Sage
SQL Gateway Administration Tool and log in. We recommend that you answer [Yes] to the prompt to
enable the Sage SQL Gateway Add-On for the Executive Dashboard. You then see the wizard to add a
data warehouse. You specify the type of data transformation during the replication:
l
l
If you choose Modern Views, the equivalent information in the database tables are consolidated to
create tables with new names.
This transformation is required for using the Executive Dashboard.
If you choose Classic Views, the SQL database is an exact replica of the schema in the source
databases.
Follow the steps to add data sources, set up notifications (optional), and perform the first replication, as
described in the Sage SQL Gateway with Executive Dashboard Installation and Reference Guide.
After the data is replicated, use the Security tab of the Sage SQL Gateway Administration window to
control access to the replicated data features. You can create roles, add users and assign them to one or
more roles, and assign features to the roles.
You can also set Sage SQL Gateway to automatically replicate your data on a recurring basis so your
Executive Dashboard reports and any custom reports use the latest Sage 300 Construction and Real
Estate data. Specify the time of the replication on the Options tab of the Sage SQL Gateway
Administration window.
Release Notes Page 2
Sage SQL Gateway and Executive Dashboard
Sage SQL Gateway
Issues and solutions
Partial installs
When you run the Sage SQL Gateway installer, you can choose to install SQL Server without installing
Sage SQL Gateway. If you choose this option and later run the installer to try to install Sage SQL
Gateway, you are only given the Uninstall option. Choose this Uninstall option and then run the installer
again—and choose the option to install Sage SQL Gateway.
Custom descriptions
If your Sage 300 Construction and Real Estate data folders use custom descriptions for any of the fields
shown in the following table, Dashboard reports may not include the data from these fields. In an
application, select File > Company Settings > Custom Descriptions and check whether the value is
different from the default value.
Custom description
Default value
AP Retainage
retainage
AR Adjustment
adjustment
AR Cash Receipt
cash receipt
AR Customer
customer
AR Finance Charge
finance charge
AR Invoice
invoice
TX Tax
tax
If the values for any of these fields are different, follow the steps in the Knowledgebase article titled "How
do I update Sage SQL Gateway so Custom Descriptions are recognized on Executive Dashboard
Reports?"
Release Notes Page 3
Sage SQL Gateway and Executive Dashboard
Sage Executive Dashboard
Before users can use the Executive Dashboard:
l
l
l
l
Their computers must be connected to the SQL Server instance used by Sage SQL Gateway
version 4 with the Executive Dashboard Add-on installed and enabled; see the previous chapter for
information about installing Sage SQL Gateway and enabling the add-on.
Your company's data must have been replicated using the Modern Views data transformation.
Security must be set up in Sage SQL Gateway to enable users with roles to access specific
reports, charts, and graphs in the Executive Dashboard.
The Sage Executive Dashboard program must be installed on the users' computers.
NOTE: Computers with Sage Executive Dashboard do not need to have any Sage 300 Construction
and Real Estate applications installed.
Installing
For detailed information about installing the Executive Dashboard program on a user's computer, see the
Sage SQL Gateway with Executive Dashboard Installation and Reference Guide.
Upgrading from a prior version
If there is a prior version of the Executive Dashboard on a user's computer, you must completely remove
the prior version. This involves two steps to uninstall the old version and one to install the new version:
1 Open the control panel, select Uninstall a Program, and uninstall the Executive Dashboard.
2 Download and run the Executive Dashboard installation file, and then select the Uninstall option. (This
removes the old version's install files.)
3 Either download the installation file again or double-click the executable file (from the Download folder),
and choose the option to install the Executive Dashboard (the new version).
NOTE: If you do not perform both steps 1 and 2 before performing step 3, the installation may
appear to be successful but you will not have the new version of the Executive Dashboard.
Accessing
To access the Executive Dashboard, select Start > All Programs > Sage > Sage Executive
Dashboard, specify the SQL server instance and warehouse name, and log in.
Release Notes Page 4
Sage SQL Gateway and Executive Dashboard
Sage Executive Dashboard
Features
The table on the following pages describes the Executive Dashboard features (reports, charts, and graphs)
that are available. Before you can see any reports, you must be set up as a user with a role that has
access to specific features in Sage SQL Gateway (on the Security tab of the Sage SQL Gateway
Administration window).
Feature
Description
Accounts Payable
AP Open Invoice
Detail
This report shows the amount your company owes a specific vendor in
each 30-day “aging” period. It also includes a column showing the
remaining retainage payable.
AP Open Invoice
Summary Bar Graph
This bar graph shows the dollar amount (not percentage) your
company owes vendors in 30-day increments.
AP Open Invoice
Summary Pie Chart
This pie chart shows the percentage of the total dollar amount owed
vendors for each 30-day time period.
AP Open Invoice
Summary Snapshot
This report shows the dollar amount and percentage that your
company owes vendors in each 30-day “aging” period.
AP Open Invoice
Vendor Summary
This report shows the amount your company owes each of your
vendors in each 30-day “aging” period as well as the remaining
retainage payable.
Accounts Receivable
AR Aging Customer
Summary
This report shows the amount due from each of your customers in
each 30-day “aging” period as well as remaining retainage due.
AR Aging Detail
This report shows the amount due from a specific customer in each
30-day “aging” period as well as remaining retainage due.
AR Aging Summary
Bar Graph
This bar graph shows the dollar amounts past due from your
customers in 30-day increments.
AR Aging Summary
Pie Chart
This pie chart shows the dollar amounts past due from your customers
for each 30-day time period.
AR Aging Summary
Snapshot
This report shows the dollar amount and percentage past due from
your customers in each 30-day “aging” period.
AR Receivable
Aging Trend Percent
This graph shows the trend over the past several months of past due
amounts owed by your customers as percentages of the total owed.
Each line represents a 30-day “aging” period.
Release Notes Page 5
Sage SQL Gateway and Executive Dashboard
Sage Executive Dashboard
Feature
Description
AR Receivable
Trend Value
This graph shows the trend over the past several months of past due
amounts owed by your customers. Each line represents a 30 -day
“aging” period.
Cash Management
CM Bank Balances
This report shows the current bank account register balances (as
posted in Cash Management).
Job Cost
JC Cost Code
Breakdown
This report shows estimated and actual costs for a specific job, broken
out by cost code.
JC Job Cash Flow
Trend
This graph shows the job cash flow trend (based on posted job cost
transactions) for one or more jobs over a specified number of prior
periods.
JC Job Profit
Summary Percent
This graphic shows the in-progress profit percent for jobs as of the
specified date and the prior month as well as an arrow indicating the
change from the prior month.
JC Job Profit
Summary Value
This graphic shows the in-progress profit for jobs as of the specified
date and the prior month as well as an arrow indicating the change
from the prior month.
JC Job Profit Trend
This graph shows the in-progress project profit trend (based on
posted job cost transactions) over the specified number of months.
JC Job Profit Trend
Percent
This graph shows the in-progress project profit trend as a percent
(based on posted job cost transactions) over the specified number of
months.
JC Job Summary
This report shows summary Job information regarding the job, Cost,
billing, over/under and projected profit and margin.
Property Management
PM Aged Receivable
Bar Graph
This bar graph shows the amounts past due from your tenants for
each 30-day “aging” period.
PM Aged Receivable
Detail
This report shows the amount due for each of your properties from
each of your tenants (by lease and by unit) in each 30-day “aging”
period.
Release Notes Page 6
Sage SQL Gateway and Executive Dashboard
Sage Executive Dashboard
Feature
Description
PM Aged Receivable
Pie Chart
This pie chart shows the amounts past due from your tenants for each
30-day “aging” period.
PM Aged Receivable
Summary Snapshot
This report shows the dollar amount and percentage past due from
your tenants in each 30-day “aging” period.
PM Leasing
Summary Snapshot
This report shows the leased status of your properties for the month.
PM Property
Occupancy
Summary Percent
This graphic shows the weighted average occupancy percent as of
the specified date as well as the occupancy percent for the last month
and the trend.
PM Property
Occupancy Trend
This graph shows the occupancy trend as a percent for the various
square footage types over several months.
Issues and solution
When you set parameters (the Company, Payable aging basis, and As of date) in the AP Open Invoice
Summary Snapshot and then click in the report, the AP Open Invoice Vendor Summary displays to show
more details—but it uses the default parameter values. If you want to use the same parameter values that
were used for the AP Open Invoice Summary Snapshot, you must select the parameters in the AP Open
Invoice Vendor Summary report.
Release Notes Page 7
Sage SQL Gateway and Executive Dashboard
Support and assistance
Supporting documentation
For detailed information about how to set up the server and configure Sage SQL Gateway, see the
Sage SQL Gateway with Executive Dashboard Installation and Reference Guide.
In-product assistance
When you are in Sage SQL Gateway, hover over a button or field to see a description of that item in a
hover window.
When you are in the Executive Dashboard:
l
To view a brief description of a report, chart, or graph in the report gallery on the left side of your
window, hover over
l
(the information icon) for a report.
To see detailed information about a report, chart, or graph that is on your dashboard (in the main part
of the window), hover over the report (to see the report's title bar) and then click
(the help icon)
to open the report description. The program displays the description in a separate help window that
can be moved on your screen. To print the report description, click [Print] near the bottom of the
help window.
Training
Sage University offers live and recorded online training sessions to help you get the most out of your
software. To access Sage University, visit http://www.sagesoftwareuniversity.com/.
To learn more about the Sage Executive Dashboard, view the Sage Executive Dashboard playlist on
YouTube, which includes four videos.
Contact your business partner for local training.
Technical assistance
When you need assistance, refer to the How do I get technical support for Sage 300 Construction and
Real Estate? article to determine which service works best for you.
Release Notes Page 8
Sage SQL Gateway and Executive Dashboard
Appendix A: Enabling .NET 3.5
This appendix covers the steps to enable .NET 3.5 for the following operating systems:
l
Windows 8 and Windows 8.1
l
Windows Server 2012
Windows 8 and Windows 8.1
Follow these steps to enable .NET 3.5 before installing Sage SQL Gateway.
1. On the Windows desktop, hover over the upper or lower right edge of the screen and click START.
2. On your keyboard, press WINDOWS + C
3. In the Search box, type Control Panel.
4. Click Control Panel, then click Uninstall a program in the Programs area.
5. On the left side of the Programs and Features window, click Turn Windows features on or off.
6. In the Windows Features window, select:
l
.NET Framework 3.5 (includes .NET 2.0 and 3.0)
l
Windows Communication Foundation HTTP Activation
l
Windows Communication Foundation Non-HTTP Activation
7. Click [OK].
8. If the source files are on the computer, the system installs .NET 3.5. Click [Close] to close the
Windows Features window.
If the source files are not on the computer, select to download the files from Windows update.
Follow the prompts to install .NET 3.5.
NOTE: Your company policy must allow you to download files from the Microsoft website. If you
receive an error message, click Tell me how to solve this problem and review Knowledgebase
article number KB2734782
Release Notes Page 9
Sage SQL Gateway and Executive Dashboard
Appendix A: Enabling .NET 3.5
Windows Server 2012
Follow these steps to enable .NET 3.5 before installing Sage SQL Gateway.
1. On the Windows desktop, hover over the upper or lower right edge of the screen and click START.
2. Open Control Panel > Programs > Uninstall a Program.
3. In the Programs and Features window, click Turn Windows features on or off. The Server
Manager window opens.
4. In the Add Roles and Features Wizard, review the information on the Before you begin page,
and then click [Next].
5. Make selections and click [Next] on the next three pages of the wizard.
6. On the Select Features page of the wizard, select:
l
.NET Framework 3.5 Features
l
.NET Framework 3.5 (includes .NET 2.0 and 3.0)
l
HTTP Activation
l
Non-HTTP Activation
7. If the Add features that are required for HTTP Activation window appears or the Add features
that are required for non-HTTP Activation window appears, click [Add Features].
8. On the bottom of the Select Features page of the wizard, click [Next].
9. Continue to make selections and click [Next] on the next two pages of the wizard.
10. On the Confirm installation selections page of the wizard, verify that the four .NET Framework
3.5 Features are in the list.
11. If the source files are on the computer, click [Install].
If the source files are not on the computer select Specify an alternate source path near the
bottom of the Confirm installation selections page and enter a location for the source files.
12. The system installs .NET 3.5 and displays a progress bar. When you see the Installation
successful message below the progress bar, click [Close].
NOTE: Your company policy must allow you to download files from the Microsoft website. If you
receive an error message, click Tell me how to solve this problem and review Knowledgebase
article number KB2734782
Release Notes Page 10
Sage SQL Gateway and Executive Dashboard