UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Bi Tools To Use With Ms Access, 2007    
 
   
lizba
post Aug 22 2013, 06:06 AM
Post#1



Posts: 237
Joined: 9-February 06
From: South Africa


Hi All
Can someone give me some advice. We use a Microsoft Access 2007 database with a SQL express backend. My bosses have asked me to look into using BI tools so that we can send out reports to our clients which must include different graphs etc of their year's trade. I do not have any experience with BI. Is there something available that would be easy to use and will integrate with the data that is on our database? Does anyone have any recommendations ?
Regards
Liz
Go to the top of the page
 
DanielPineault
post Aug 22 2013, 06:09 AM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Do native Access reports not suffice? What limitation are you trying to overcome?
Go to the top of the page
 
lizba
post Aug 22 2013, 06:19 AM
Post#3



Posts: 237
Joined: 9-February 06
From: South Africa


well I know from when I have tried it in the past that the graphs in Access aren't great. I eventually exported the data to excel and did them there. What we want is a 2 - 3 page report for each client with multiple graphs and analysis on their years trade. It must look professional and not take hours to generate.
Go to the top of the page
 
DanielPineault
post Aug 22 2013, 06:36 AM
Post#4


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Once the initial setup is done, Access report are very fast and effective.
You have other options, create a VBA routine to export the relevant data into Excel and generate the charts there since it is much more powerful and much easier to work with.
You could look into a tool such as Crystal Report
You could also look into SQL Server Reporting Services
But In either case, I'm not sure if they are compatible with the Express version of SQL Server.
Go to the top of the page
 
GroverParkGeorge
post Aug 22 2013, 08:30 AM
Post#5


UA Admin
Posts: 31,203
Joined: 20-June 02
From: Newcastle, WA


Furthering Daniel's suggestion, you can create Excel templates with the graphs and charts in them. Then automate the export of data from the Access database into a workbook instantiated from a template to populate the charts. We did just that for a client. The graphs are not too complicated, but they do work well. The data is stored in SQL Server and entered through the Access interface.
On the template, create a worksheet for the graphs and a worksheet into which the data is exported. The graphs take their data from the data worksheet.
You need to define Ranges for the data for each chart and place the data into those defined ranges.
Go to the top of the page
 
DanielPineault
post Aug 22 2013, 09:39 AM
Post#6


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Great minds think alike George. I've done very similar work.
Create a template, setup the fonts, charts, color, axis, ... all that fun stuff. Then use some code from Access to simply update the chart source data by importing the data and redefining the named ranges upon which the charts are based (they then automatically update to reflect the new data). This way everything is standardized, so you get the same look regardless of the data being processed (client being reviewed). By saving the Excel as a template you ensure that things remain the same week to week, client to client. Otherwise you users could accidentally mess up your base workbook (which you don't want).
Go to the top of the page
 
BananaRepublic
post Aug 22 2013, 10:15 AM
Post#7


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Just to put out one more option -- if you want a workbook with live data connection, you can set up a Data Connection to Access. That mean you don't have to import any data and the connection is automatically refreshed on the open so you always have your <whatever> current when you open the spreadsheet or even by clicking Refresh button.
MMV.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 07:24 PM