Does anybody know if this is a feature or a bug….?

“This looks very risky. How are three people going to travel on only one ticket?”, one of the accountants asks.

“Watch and you’ll see! Take notice of our brand new risk management approach”, one of the actuaries answers.

They all board the train. The accountants take their respective first class seats, but all three actuaries cram into a restroom and close the door behind them.

Shortly after the train has departed, the conductor comes around collecting tickets. He knocks on the restroom door and says, “Ticket, please.” The door opens just a crack and a single arm emerges with a ticket in hand. The conductor takes it and moves on.

The accountants were deeply impressed by the actuarial approach and agreed it was - after all - quite a clever idea without any substantial risk.

So, completely confident and with even more Risk Management skills gained at the inspiring Conference, the accountants decide to copy the actuaries new risk approach on the return trip and save some money (accountants have always been clever with money!). When they get to the station they buy a single ticket for the return trip.

To their astonishment, this time the actuaries don’t buy a ticket at all. “This is reckless, how are you going to travel without a single ticket?”, one of the perplexed accountants asked. “Watch and you’ll see! Take full notice of our latest risk management approach” answered an actuary.

When they board the train the three accountants cram into a restroom and the three actuaries cram into another one nearby. The train departs.

Shortly afterward, one of the actuaries leaves his restroom and walks over to the restroom where the accountants are hiding. He knocks on the door and says, “Ticket, please.”

Conclusion:
What conclusions can we draw from this simple story?

Risk Management is a game without end

The effect of Risk Management Conferences is threefold:

Some attendants get smarter
Others get overconfident
Final result: Increasing Risk, instead of decreasing Risk

There’s an old Dutch saying that expresses the danger of increased Risk Management :

“A warned man counts for two”

If we want to reap the fruits of Risk Management, accountants and actuaries have to start working together, instead of struggling and competing each other.

Risk Manager Profile and qualifications
Insight, creativity and integrity are important requirements to become a professional Risk Manager. Unfortunately, this is not enough.

To tackle Risk Management in a company, you need the best potential crook around. One who’s willing to settle his salary and earnings for a little less than he would have earned as a real crook, in return for having a respectable job and not risking to end up in jail. You could call it the Personal Risk management of the Risk manager. Employers that settle for an inferior Risk Manager, know one thing for sure: someday somebody more ‘crooky’ than ‘your risk manager’ will tear your company down!

source

‘Set Reference to Microsoft Scripting Runtime
‘Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Function ColumnNames(ByVal sConnection As String, ByVal sTable As String) As Dictionary

Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim sSQL As String
Dim dColumnNames As Dictionary

sSQL = sSQL & “SELECT sc.name AS column_name, st.name AS datatype ”
sSQL = sSQL & “FROM sysobjects so ”
sSQL = sSQL & “JOIN syscolumns sc ON so.id = sc.id ”
sSQL = sSQL & “JOIN systypes st ON sc.xtype = st.xtype ”
sSQL = sSQL & “WHERE so.xtype = ‘U’ AND st.name <> ’sysname’ AND so.name = ‘” & sTable & “‘”

Set dColumnNames = New Dictionary

Set cConnection = New ADODB.Connection

cConnection.Open sConnection
Set rsRecordset = New ADODB.Recordset
Set rsRecordset.ActiveConnection = cConnection

rsRecordset.Open sSQL

Do While Not rsRecordset.EOF And Not rsRecordset.BOF

dColumnNames.Add rsRecordset.Fields(0).Value, rsRecordset.Fields(1).Value
rsRecordset.MoveNext

Loop

Set rsRecordset = Nothing
cConnection.Close
Set cConnection = Nothing

Set ColumnNames = dColumnNames

End Function

I’ve got 2 tables, lets call them tbMaster ans tbSlave.  Most people use a new vlookup for every column they want to retrieve from tbSlave. What I usually do is use vlookup as an array formula.

For example if I want to get the 4th, 7th and 9th column from tbSlave I select 3 columns in tbMaster and type     =VLOOKUP(tbMaster[[#This Row],[Name]],tbSlave,{4,7,9},0) followed by Ctrl Shift Enter

Besides the normal vlookup disadvantages in my opinion the array solution is better

I divide my officers into four classes; the clever, the lazy, the industrious, and the stupid.

Each officer possesses at least two of these qualities.

Those who are clever and industrious are fitted for the highest staff appointments.

Use can be made of those who are stupid and lazy.

The man who is clever and lazy however is for the very highest command; he has the temperament and nerves to deal with all situations.

But whoever is stupid and industrious is a menace and must be removed immediately!

Any post will have to wait until I get back from my 2 weeks of vacation starting next friday.

When another ape tries to climb the stairs, the other apes will try to prevent it.

Now, turn off the cold water. Next, remove one ape from the cage and replace it with a new one.
The new ape sees the banana and wants to climb the stairs. To his horror, all of the other apes
attack him.

After another attempt and attack, he knows that if he tries to climb the stairs, he will be
assaulted. Next, remove another of the original five apes and replace it with a new one.
The newcomer goes to the stairs and is attacked. The previous newcomer takes
part in the punishment with enthusiasm.

Again, replace a third original ape with a new one. The new one makes it to the stairs and is
attacked as well. Two of the four apes that beat him have no idea why they were not permitted
to climb the stairs, or why they are participating in the beating of the newest ape.

After replacing the fourth and fifth original apes, all the apes which have
been sprayed with cold water have been replaced. Nevertheless, no ape
ever again approaches the stairs.
Why not? “Because that’s the way it’s always been around here.”

That’s how individual initiative is beaten to death and a company culture begins.

( If you pay peanuts,… you get monkeys )

http://blogs.technet.com/office2010/

Enjoy

What I dislike about this aproach is the button on the sheet, the size of the workbook(s)
and the dependancy on odbc links. So I started thinking on a new setup for this workbook.
The first abvious thing was changing to 2 main pivotcaches this would reduce the size to less
than 8 MB whitch means a 90% reduction of disk space.
Secondly I wanted to refresh the data by connecting a new pivotcache to the pivottables.
In the past I wrote some code to switch pivotcaches between pivottables and to produce a
pivotcache from a sql statement and a connectionstring. It looked liked I was almost there
but there were a couple of pitfalls. Apperently you must create a pivottable first from a
pivotcache to be able to switch the pivotcache. Also when the your pivottable uses calculated
fields in the value section switching the cache results in a blank pivottable.
After realizing these things the coding didn’t take along time. Altough everything works I would
like a answer from microsoft or anybody else about the problem around using a pivotcache
without pivottable and the calculated field issue. Below the code without error handling for
the obvious reasons. Comments a very welcome.

Sub UpdateWorkbook()

Dim sSQL1 As String: Dim sSQL2 As String: Dim sDate As String

sDate = InputBox(”Enter the date (yyyymmdd)”)

sSQL1 = “SELECT *, (new-old) AS change FROM table1 WHERE date=’” & sDate & “‘”
sSQL2 = “SELECT * FROM table1 WHERE date = ‘” & sDate & “‘”

SwapPivotCaches sSQLServer, sSQL1, “PivotTable1″
SwapPivotCaches sSQLServer, sSQL2, “PivotTable2″

End Sub

Sub SwapPivotCaches(sConnection As String, sSQL As String, sPivotTable As String)

Dim pcTemp As PivotCache: Dim ptTemp As PivotTable
Dim ws As Worksheet: Dim pt As PivotTable

Application.ScreenUpdating = False
Sheet0.Visible = xlSheetVisible

Set pcTemp = PivotCacheFromSQL(sSQL, sConnection)

Set ptTemp = pcTemp.CreatePivotTable(TableDestination:=Sheet0.Range(”B5″))

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If pt.Name = sPivotTable Then pt.CacheIndex = ptTemp.CacheIndex
Next pt
Next ws

Set ptTemp = Nothing
Set pcTemp = Nothing

Sheet0.Cells.Delete
Sheet0.Visible = xlSheetVeryHidden
Application.ScreenUpdating = False

End Sub

Function PivotCacheFromSQL(sSQL As String, sConnection As String) As PivotCache

Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim pcPivotCache As PivotCache

Dim iErrorCounter As Integer

iErrorCounter = 0

Set cConnection = New ADODB.Connection

cConnection.Open sConnection

Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cConnection

With cmdCommand
.CommandText = sSQL
.CommandType = adCmdText
End With

Set rsRecordset = New ADODB.Recordset
Set rsRecordset.ActiveConnection = cConnection

rsRecordset.Open cmdCommand

Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pcPivotCache.Recordset = rsRecordset

Application.StatusBar = False

Set PivotCacheFromSQL = pcPivotCache

Set rsRecordset = Nothing
Set cmdCommand = Nothing
Set cConnection = Nothing

End Function