Resource Forecasting

  • Thread starter Thread starter Prav
  • Start date Start date
P

Prav

Hi I would like some advice how to develop a resource forecasting database to
view resourcing graphs. At the moment i have to copy the 'Task Usage' Hours
(for a year) from my Ms Project File and paste the hours in excel to produce
a graph. Cheers
 
Prav,

Do you know how to export the Project data into Access? I am guessing that
you don't want to use SQL Server, the native database engine.

LDN
 
I would like to have a dedicated Resource Forecasting access database as i
don't want to update the existing project management database and the ms
project. Therefore my idea is to implement a resource forecasting inside the
project management database.
 
Hope that this is not a double post, I was detracted by a telephone call and
breakfast and my connection timed out.

Prav,

The only advice that I can provide is to suggest that you start learning the
MS Project object model. There is a section in Scott Barker's Access 2002
Power Programming (Wiley) that will get you started.

Second, study Michael Alexander's Access 2007 Data Analysis (Sams). It
contains some of the best insight into analytics that I have found in the
past several years.

And if you plan on creating a dashboard/scorecard application then you need
to understand how to handle images in Access. You can use any graphics
package to create the gauges and other dashboard elements. I prefer MS
PhotoDraw myself.

I would be interested in learning more about your project management
database, if you care to describe it here.

HTH.

LDN
 
well we called it as a project management db. Its a simple db that record
every project details that are entered it. It flags when a milestone needs
attention; it produces a cost tracking report and other customised reports.
 
Hi roccogrand could you please point me to the write direction how i can
create the dashboard/scoreboard application. I'm very interested in the
style. cheers
 
Hi Prav,

My app is a question and answer database but the approach should be the same
for any dashboard/scorecard app. I began the app by creating the
computational functionality, in my case I needed to calculate the number of
correct answers, which uses After_Update and OnCurrent events.

I then created the gauges in PhotoDraw (MSPD from now on). I found an image
of a gauge on the Net and I then used MSPD to create a master gauge with
eleven needles. There wasn't any reason to have unit needles.

It was important to create a master gauge because I would later place each
deca-gauge (i.e., gauge = 0, gauge = 10, etc.) exactly on top of each other.
The images had to be the exact same physical size and the background color
had to match the background color of the form exactly. With MSPD and dual
monitors I can easily do this.

I discovered that Jpegs didn't work the way that I wanted. The Jpeg load
events displayed an annoying message. So I decided to use GIFs. I
understand that I could hack the registry to fix the Jpeg problem but I am in
a different state from the app's users and I really hate hacking the registry
if I can help it and talkin someone through the process is out of the
question.

I wanted very detailed images and the default GIF file size didn't have the
resolution that I wanted so I increased the number of pixels after using them
in Access a couple times. Once again, having a master gauge was important.

The next step was to figure out how to use the images. You can load images
into Access or use their paths/filenames. There is lots of information about
this on the Net.

I tried a few sample code sets but I could not get them to work with
calculations. Most of the sample code is designed to allow the user to
display a single image for each record whereas my app needs 11 images for
each record. The calculations in my app determine which image is displayed,
not the record number or primary key.

I decided on using the paths/filenames with Access' Insert Image because the
size of the app with images was 70 MB and I could not email it to the user in
that state, even after compression (it was fine on my side but the file blow
up their system). The final app was less than 5 MB. The GIF images was sent
separately and they were only a few tens of KBs each.

I'll let you digest this message and I will continue after you respond. Now
I want to try understanding how to use a new type of SQL statement so that I
can add three more gauges to my app. It has one gauge and one histgram now
but I have an idea for multiple gauges and I think that a Group By statement
as discussed in Rick Dobson's book will work.

LDN
 
Ok,

I'm back. Took the grandkid to the bookstore and then shopping.

Another operation that I had to figure out was changing the images. One
possile method was to use If Then - Else - End If statements but as Julitta
Korol so clearly describes in Access 2003 Programming by Example with VBA,
XML, and ASP (Wordware Publishing). If Then statements just aren't
appropriate for testing values for dashboards IMHO.

Julitta very clearly describes Select Case statements, which tests each
value in a calculation in the order they appear in code. Even a
nonprogrammer like me can understand the examples in the book.

In my app, I needed the first Case clause to test that the calculated value
is less than 10 and if it is the app needs to display gauge0.gif. If the
calculated value is less than 20, the app needs to display gauge10.gif. And
if the value is less than 30 it displays gauge20.gif, and so on.

As I mentioned last time gauge10.gif has the needle pegged on 10 percent for
10 percent of the questions being correctly answered. And gauge20.gif has
the needle pegged on 20 for 20 percent correct, and so on. If I wanted to, I
would have a hundred gauges and a more granular assessment of performance but
that's not the point of my app.

I actually implemented the gauges last because I couldn't figure out the
image change thing for a long time. The first dashboard element was actually
a histogram.

I didn't want one of the standard bar charts that Access or Excel creates so
I used text boxes and conditional formatting. Each decade value has a
textbox. One is the same color as the background and one is a contrasting
color depending on the calculated value. Low values are shades of red and
orange, values over 70 percent are light green and high values are deep
bright green. The color scheme is like traffic lights I suppose.

Not only do the colors change as the percentage of correct answers increase,
the size of the boxes also get larger. I definitely could not have done this
with out-of-the-box charts.

So why use text boxes you may be asking? The answer is simple, Microsoft
does not provide true graphical elements with Access. I would have preferred
to use circles but the best that I can do is to place a text box on form,
make the record source a calculated value and set the color using conditional
formatting. I can then delete the text in the label. Voila, I have a
colored rectangle when the value is evaluated by the conditional formatting
event.

Any questions yet or should I continue this level of detail?

LDN
 
Prav,

1. I have not searched for or read any books on dashboards but I have been
searching the Net for articles about them for sometime and have found few
useful ones. There is an article in this month's SQL Server magazine by Doug
McDowell that suggests that his company, Solid Quality, has expertise in the
field. But I haven't checked their website yet to learn what they do or if
they have published any books. Will get back to you if you like.

But I do own a couple of Kaplan's books on balanced scorecards.

Also the Access Advisor had an article a couple months back on dashboards.
But the article is really about showing several Access charts on a single
form. For me these are not dashboards, I was using four charts in Excel
windows fifteen years ago to show geologic processes (specifically, magma
generation). It is not a dashboard unless it uses a gauge or a graphical
image, IMHO.

There are some commercial products such as Dundas that you may want to check
out. My company bought their enterprise product recently for a project but
it's not cheap.

2. I know very little about Flash. I have never used any of the
high-powered graphics packages on the market. MSPD has always done what I
needed. I am very sorry that Microsoft abandoned it.

Do you want me to continue describing my app?

What is your application for dashboards?

LDN
 
Can't tell if this was posted so here it is again.

Prav,

1. I have not searched for or read any books on dashboards but I have been
searching the Net for articles about them for sometime and have found few
useful ones. There is an article in this month's SQL Server magazine by Doug
McDowell that suggests that his company, Solid Quality, has expertise in the
field. But I haven't checked their website yet to learn what they do or if
they have published any books. Will get back to you if you like.

But I do own a couple of Kaplan's books on balanced scorecards.

Also the Access Advisor had an article a couple months back on dashboards.
But the article is really about showing several Access charts on a single
form. For me these are not dashboards, I was using four charts in Excel
windows fifteen years ago to show geologic processes (specifically, magma
generation). It is not a dashboard unless it uses a gauge or a graphical
image, IMHO.

There are some commercial products such as Dundas that you may want to check
out. My company bought their enterprise product recently for a project but
it's not cheap.

2. I know very little about Flash. I have never used any of the
high-powered graphics packages on the market. MSPD has always done what I
needed. I am very sorry that Microsoft abandoned it.

Do you want me to continue describing my app?

What is your application for dashboards?

LDN
 
Hi Prav,

Once again sorry for the double posting but my connections commonly time
out.

I did check out the website for Solid Quality Mentors and I saw no books on
dashboards but some on Business Intelligence and several on SQL and SQL
Server. However, McDowell's article in SQLMAG does mention Microsoft
PerformancePoint Server 2007 (PPS), which has dashboard functionality.

Microsoft has a website with online training videos for PPS at:
http://www.microsoft.com/business/performancepoint/resources/training.aspx
This site has a couple sessions on dashboards that may give you some ideas.
It even has workbooks that you can download. In total there are 14 modules
on the site. So if your organization uses SQL Server and SharePoint, PPS is
probably the best solution for you.

But if you want to use Access then let's continue with my app. If you
understand the Access object model, SQL, and business analytics then you can
create very sophisticated dashboards/scorecards yourself in probably the best
program that MS manufactuers -- Access.

PPS' dashboards are a mix of charts and graphical icons. You can create the
same charts with Access functionality (see Alexander's book) and display the
graphical icons using the methods that I have described.

The icons in PPS usually start with single icons, e.g., red symbols for very
bad news, yellow for bad news, and green ones for good news. What's very bad
news? It's when your company loses hundreds of thousands of dollars on a
project worth just a few million dollars. Bad news is when you lose a few
tens of thousands of dollars and good news is when you are hundreds of
thousands of dollars under budget or profit is that amount.

Similarly, bad news is when your project is weeks behind schedule and good
schedule news is when you are weeks ahead of schedule.

In my project management database, another app that I created that uses the
concept of dashboards, very bad, bad, and good news is displayed using
rectangles (text boxes) that take their colors from simple calculations in an
Access database. For example, if a project is two weeks or more over
schedule, a text box next to the schedule performance field is colored red.

The performance dashboard actually shows schedule, budget, quality, and
resource utilization data. If a project manager sees two or three red text
boxes then he knows immediately that he has a big problem on the project.
If all of the text boxes are red then he knows that it is time to clear out
his desk and remove his nameplate from his office door.

I did see your comments about my skills but really I am still a novice
Access developer in many respects. Access kicks my butt everytime I create a
new app. But I think that it is keeping me from getting alzheimers , as I
approach the big 60.

LDN
 
yeah please continue explaining your apps and i will us the dashboard for the
resourse forcasting and later inplement that in the project management
database.
 
OK, once again I am not if I lost the connection or not so here is my response.

Ok Prav,

The last topic to discuss is how I cycle through the images in my app.

As I said above, I use two events in the form: OnCurrent and After_Update.
Both have the exact same code and begin with Dimension statements after
naming of the modules. Next I make all eleven gauges invisible with a
Visible = False statement. I am not sure if this is necessary but it ensures
that the displayed image is exactly what I want.

Then the code has the Select Case section, which uses a simple equation to
do the calculation and that selects which image to display. This is
accomplished with a Visible = True statement.

When the answers in the app are changed, the After_Update event fires. The
displayed gauges change from gauge0.gif to gauge10.gif and so on. If the
percentage of correct answers decreases, the images change from say
gauge90.gif to gauge80.gif. This is probably what the temperature gauge does
on the weather websie. In other words, your dashboard has to accommodate
increases and decreases to the calculated values, not just increases.

The OnCurrent event is included because a user may exit the app without
answering all of the questions. So when he/she returns and logs into his/her
test, the OnCurrent event fires and displays the correct gauge after making
all of the gauges invisible.

Some Access developers would also add the same code to the OnDirty event.
I don't quite understand why this is necessary and have not done it yet.

So in summary, the important aspects of a dashboard application are: Select
Case; Visible = True and False; calculations; and, use of Access' Insert
Image functionality. There does not seem to be much more to it than that
except having a set of high-quality images.

My test app is more than that because of the purpose of the app. Actually
any app will be more than that because of the need to store, manage, and
output your data using well-designed tables, queries, forms and reports.

I hope that these replies have helped you envision your own dashboard
application. Again this is not advanced Access development in my opinion.
It is just understanding a few of the really powerful features in Access and
going for it.

The simplest scorecards only need to use conditional formatting based on
some calculation. Unfortunately the only icons that you can use with
conditional formatting are rectangles using text boxes in Access.

I have mentioned only three of the books that will be helpful to you. I
could not have understood Select Case without Korol's book. And Alexander's
book really helped me with calculations in Access.

I developed the test app in Access 2003 and have tested it on a machine
running Access 2007. I am still not comfortable with Access 2007 and so I
have not started using the advanced functionality in it yet.

Good luck. If you post questions about dashboards in the future, please
include the word dashboard in the subject line. I don't check the Discussion
Group sometimes for weeks at a time like the MVPs and so I could easily miss
one of your postings.

LDN
 
Prav,

Here is all the code in my events.

============
Private Sub Form_Current()

Dim PercentCorrect As Integer
Me.Recalc
Me!Image127.Visible = False
Me!Image130.Visible = False
Me!Image132.Visible = False
Me!Image133.Visible = False
Me!Image136.Visible = False
Me!Image137.Visible = False
Me!Image138.Visible = False
Me!Image139.Visible = False
Me!Image140.Visible = False
Me!Image141.Visible = False
Me!Image142.Visible = False

MyScore = ([Total Correct] / [MyCount]) * 100

Select Case MyScore
Case 0
Me!Image132.Visible = True
Case Is <= 20
Me!Image130.Visible = True
Case Is <= 30
Me!Image130.Visible = True
Case Is <= 40
Me!Image137.Visible = True
Case Is <= 50
Me!Image133.Visible = True
Case Is <= 60
Me!Image136.Visible = True
Case Is <= 70
Me!Image139.Visible = True
Case Is <= 80
Me!Image140.Visible = True
Case Is <= 90
Me!Image141.Visible = True
Case Is < 100
Me!Image142.Visible = True
Case Is = 100
Me!Image127.Visible = True

End Select
End Sub

=========
The images are nonsequential because I screwed up the numbering. When I
redo the app, I will fix this.

LDN
 
Back
Top