Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Posts Tagged ‘Sage ACT SQL Business Intelligence Reporting

Kicking up Business Intelligence Data in Sage ACT! 2012

with 4 comments

Starting with Sage ACT version 2012, you can now create some nifty Excel pivot tables using what is called a Star schema.  The data goes after histories and opportunities. It’s a bit complicated to set up, but once done you can start to do some sophisticated drill downs in Excel to show history records.  Here’s a screen shot of one pivot table I just created.

The setup took longer than creating the table.  Here’s what you need to do to get access to this data in ACT.  Note – this is really geeky so you may want to find your local IT person to help out – or send us an email at pregen@egenconsulting.com and we can quickly set this up for you for a small fee.

Setting Up or Refreshing ACT! Business Intelligence (BI) Data

You need to do this on the machine that is hosting the database with your data.  Using the free SQL Management studio, you logon to SQL Server  and point to the ACT! 2012 database you want to refresh.  Here’s a series of screen shots of what I did to connect to our own database.

The shot above is where I point SQL Management studio at my ACT SQL .  Next I located my database.

I right clicked on the database name and chose NEW QUERY.  That brought up the following empty query box.

Into this box I pasted the following commands.

exec dbo.PRC_BI_Refresh_DIMs;
exec dbo.PRC_BI_Refresh_History_FACT;
exec dbo.PRC_BI_Refresh_Opportunity_FACT;

After they were pasted in, I right clicked in the box and chose EXECUTE.  This went rattling thru my database and created two new views called:

VWX_BI_HistoryView_Full
VWX_BI_OpportunityView_Full

It’s these two views that have the magic.   After the views have been created, you can close SQL Management studio (see below on how to download SQL management studio for free).  You are now ready to look for this information using Excel. The instructions coming up next are for Excel 2007 or 2010.  Email me and I can send you instructions on how to do this on older versions.

Open Excel and click on the data tab.  Next, choose the FROM OTHER SOURCES button and then choose SQL Server.  You will get the following box into which you type in the name of your server.  If you are running this on your own computer and have a database there it would be the name of your machine followed by a \act7.  If it’s on the server, then you put in the server name followed by \act7.  Here’s what it looks like going after a database on a server:

In the case above, since I am going after the data on a machine other than my own, I need to provide a username and password to access the internal data.  You cannot use your normal ACT username and password.  If this was on my own computer, and I was going after a database running on my own computer, I could use the windows authentication.  In the case above though, I want to go after the main database on the server so I am using the ACTREADER login.

The ACTREADER logon  is a great way to get to data without having to know the System Admin password.  It comes free with Sage ACT.  All you have to do, one time only, is run a little program called ACTREADER which lives in the Program Files\ACT\ACT for windows folder. When you run the program it will ask you for a password.  Enter one of your choosing and write it down so you can remember it later.  You can run ACTREADER on your own computer and it will work for databases, like remotes, that live on your computer.  If you want to run it against a server-based database, you need to run the ACTREADER program once on the server, set the password and then the password is set FOR ALL databases on that server.

After entering the server name and username and password, you click NEXT and choose your database from the dropdown.

In the box shown below I am going to choose PEC_Master.

Make sure you check the Connect to a specific table box.  It will display a set of tables.  Scroll down until you see the VWX_BI… tables as per the example below.

Click on one of the BI.. tables and then NEXT and FINISH and then choose PIVOT TABLE REPORT and a location for the table.  Let it default to $A$1.

You now have a blank Pivot Table with fields to the right that you can start dragging and dropping into the Pivot Table Layout.  The first image in this blog article was done using the Histories table.  The one below is using the Opportunities table.

Here’s what I was able to produce in just three clicks: A report of opportunities by state by year. Pretty darn cool if you ask me.

Even better, with another few clicks I was able to change the report to one by sales person and by year.

A little work on my part setting this up created some great tools for drill down reporting.  Once you save these spreadsheets, they remember the query instructions.  All you have to do is click REFRESH and it will pull in up to date data.

Nifty indeed.

Footnote:  Here’s the link to download SQL management Studio Express: http://www.microsoft.com/en-us/download/details.aspx?id=7593

Written by pregen

July 5, 2012 at 10:20 pm

%d bloggers like this: