• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Dashboard using Access DB

Steve Jobs

New Member
Hello All,

I am working on a project to build dashboard, i looking for a start over.

What i am trying to achieve here is create Excel dashboard using Access DB as the raw data is huge and cannot be retained in excel.

The Dashboard will talk about different metrics such as customer resolution, customer satisfaction, Ticket reopened Region wise, State wise, Month Wise, Week wise choosing Problem Type.

e.g, For East region, Technical Problem type, what was the different Metrics - Customer resolution%, Ticket reopened %, Volume etc.

Any Suggestion to start is mush appreciated. Please Note - All the data will be pulled from Access Database.
 
Hi ,

Can you be a little more specific in your requirements from forum members ?

Your project has the following sections :

1. Data extraction from Access database into Excel worksheet

2. Manipulating extracted data to get all the desired metrics

3. Presenting the derived metrics in the form of a dashboard

In which section do you want help ?

Narayan
 
Hi,

I would need help in -
1. Data Extraction from access to excel sheet
2. manipulating the extracted data to get all the desired metrics

How to go with the extraction of data from access DB <All Data> to excel and accessing the data using different combo boxes of Regions, State, Month , Week, Problem Type etc to present different metrics
 
Hi ,

Somewhat late in the day to be discussing where to start.

If you had uploaded a workbook and the Access database with all the data , it is possible , someone might have helped , but with such general questions , all that can be done is to point you to the innumerable sites that come up in a Google search.

If you enter :

interface Access Excel

in Google , you should get enough results that will point you in the right direction.

Forums are always meant for specific answers , which you may otherwise not find using even Google.

Narayan
 
I have attached the Reporting Structure Workbook,( sheet 1 - Coverpage and sheet2 has the sample data) that have list boxes for selection and Metrics result below.

User will select the parameter, can be one selection or multiple to calculate different metrics, click the Generate Metrics button and result will be displayed under diff metrics header.

How to Generate the result by VBA sql from access DB.
 

Attachments

  • Reporting_Sample.xlsm
    23.1 KB · Views: 31
Well, you didn't specify version of Excel or Access. Nor did you give calculation step for each KPI. It's pretty much impossible for us to help you without all the details. We are not mind reader after all.

Here's some general tips.

If using Excel 2016 or version with PowerQuery. Just use that to connect to Access and query the data. If you need dynamic query, it will be bit tricky as you will need to set up named ranges for each parameter and then reference that in M-Formula in PowerQuery.

There's multitude of ways to set up dashboard from there.
1. Load data directly onto sheet and then use Advanced Filter to extract data needed.
2. Same as above, but use formula instead of Advanced Filter.
3. Load data to Data Model and use Pivot to summarize, using Slicer as control.

VBA can be used, but I'd not recommend it unless you are familiar with coding and can ensure all exceptions are handled appropriately.
 
I am using MS Office 2013.

Metrics Calculations

Volume - Count of Ticket_Number
FCR% - Count of FCR Flag when 1/Total number of ticket_number
Escaltion%- Count of Escalated Flag when 1/Total Number of Ticket_Number
Met SLA% - Count of MET SLA flag when 1/Total Number of Ticket_Number
Reopen% - total of Reopen_Count/Volume


Calculation is easy, the challenge is to connect the Listboxes Selection, retrive data from DB, How to calculate all Metrics per different Variable selection in Listboxes.
 
Back
Top