Emailing TM1 Reports

If you want to automatically email month end reports to managers then SPF Batch reporting is still the best way to automate the process. You need to add in some vba code to automate the emailing and I have found that when using Outlook 2003 you can get annoying security messages that pop up each time.

The best way of coding the email messages I have found is via cdo as outlined here:

http://www.rondebruin.nl/cdo.htm

By using cdo, it bypasses any outlook security questions, in fact you don't even need outlook installed to send emails thus you can run things off the TM1 server if you wanted.

Excel Iterations problem

I recently had an issue where a TM1 report would not work.
After looking closer I found it would not run the vba code Activecell.Calculate
After a bit of playing around I found that if I turned off
Tools > Options > Calculation > Iterations
everything would work as normal.
Personally I have never found a use for iterations in TM1 reporting and I think its worth turning offon any TM1 excel report.

Rediscover QUDEFINE

I haven't used the TM1 macro QUDEFINE in years.
This is mainly because I normally use MDX to execute the type of logic that Qudefine gives me.
Recently though at a site I was unable to use MDX due to the oledb provider not being installed, so instead I used QUDEFINE and QUSUBSET together.
QUDEFINE allows me to create a view based off a range of parameters provided in the Excel workbook and which can be formulas (thus allowing me to populate them via drop downs).
QUSUBSET allows me to create a subset on a dimension based off the results of a QUDEFINE.

The report I needed to create was a list of all the product sales against a selected customer for the last 12 months. There are something like 30,000 customers to choose from and 15,000 products that could have data.
Normally an MDX statement would do this in flash but thats not an option. A vba routine to loop round all these combinations would take ages.
Instead I put in a drop down of available customers, the result of which populates the customer option in the QUDEFINE Excel range. A refresh button then runs the following code:

Sub GetTM1Data()
Dim vTemp As Variant
vTemp = Application.Run("QUDEFINE", "Server:Revenue", "PricingTemplateQuery", Sheets("TM1 View").Range("rngPricingTemplateQuery"), , , True, False)
vTemp = Application.Run("QUSUBSET", "Server:Revenue", "PricingTemplateQuery", "Products", "PricingTemplateQuery")
Application.Run "TM1RECALC"
End Sub

QUDEFINE creates the view for me and QUSUBSET gives me a list of all the products that have values for the last 12 months. I can then use a report based off SUBNMs to that subset created from the QUSUBSET.

The finished report actually runs at a good speed and there is hardly any VBA used.

I'll do another post soon on how the 12 months rolling total was derived, when the year and month were seperate dimensions.

Calculate Options

Here's some tips for VBA calculating options that I posted on the forum

In the TM1 documentation you will find W_CALC which calculates a TM1 worksheet.

If you record a macro and do a F9 it will record it as "TM1RECALC" (bear in ming F9 calculates all worksheets in the workbook)

If you record a macro and do a SHIFT F9 it will record it as "TM1RECALC1" (SHIFT F9 calculates the active worksheet).

If your worksheet has TM1 formulas you are best using one of the above methods.

If you just want to calculate a lookup worksheet then you can use:

Thisworkbook.Sheets("SheetName").Calculate

If you just want to calculate a named range:

Range("MyRange").Calculate

Just the first 3 rows in the worksheet would be:

Activesheet.Range("1:3").Calculate

vba progress bars

If you have a vba report that takes a bit of time to work out the results then you should display progress to the user so that they know the report is working and hasn't crashed.

Andy Pope's website has some fantatic progress bars that you can download and use in your applications:

http://www.andypope.info/vba/pmeter.htm

Workbook Events VBA

There are 2 events at least people need to capture in TM1 frontends:
1) Stop the delete key so people don't delete the DBRW formula
2) Stop an F9 and instead do a shift plus f9

The code below in the workbook events module will help with this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnKey "{DELETE}"
Application.OnKey "{F9}"

End Sub

Private Sub Workbook_Open()

Application.OnKey "{DELETE}", "delKey"
Application.OnKey "{F9}", "+{F9}"

End Sub

Sub delKey()

MsgBox "The delete key has been disabled. Please use the space bar instead", vbExclamation

End Sub

Alternative to SUBNM

If you want a user to select an element from a TM1 dimension from your Excel front end then you can use a SUBNM and get a user to double click the cell or....

put a transparent text box over the subnm cell and assign a macro like this

Sub Dept()

Sheets("Menu").Range("Dept").Select
Application.Run Range("[tm1.xla]tm1!ENTER")

End Sub

This allows a user to only single click to get to the subset editor.