VBA - Bulk Reporting; including subsets

Post Reply
l_stoker
Posts: 8
Joined: Thu Oct 14, 2010 3:22 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

VBA - Bulk Reporting; including subsets

Post by l_stoker » Mon Aug 01, 2011 4:53 pm

Good Afternoon,

I'm looking for some guidance on amending the following VBA code to not only create reports based on department codes which are n level items, but also create reports based on subsets which amalgamate numerous departments - for those managers controlling larger teams.

Any advice would be appreciated. I'm currently struggling to see what i need to update and how.

Many thanks in advance

Sub BulkReport()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=359
'+ admin@bihints mods
'+ some of Martin Ryan code
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
Dim TM1Element As String
Dim i As Integer
Dim myDim As String
Dim server As String
Dim fullDim As String
Dim total As Long
Dim folder As String
Dim destination As String
Dim Department, RowStart, RowEnd As Long
Dim ThisWorkbook, ThatWorkbook, OpexCurrency, Comparison, Company, OpexMeasure, Period As String
Dim OpexDept, Year As Long

destination = Sheets("Front Sheet").Range("F12")
server = "domforecast2"
myDim = "OraDept"
fullDim = server & ":" & myDim
If Run("dimix", server & ":}Dimensions", myDim) = 0 Then
MsgBox "The dimension does not exist on this server"
Exit Sub
End If

'loop over all elements of the branch dimension
For i = 1 To Run("dimsiz", fullDim)

TM1Element = Run("dimnm", fullDim, i)

'Name reference points for use later in macro

Sheets("Front Sheet").Select

ThisWorkbook = ActiveWorkbook.Name
Period = Range("F5")
Year = Range("F6")
Company = Range("F7")
Comparison = Range("F8")
OpexCurrency = Range("F9")
VVersion = Range("F10")
OpexMeasure = Range("F11")

'see if there are any sales for that branch

total = Application.Run("DBRW", "domforecast2:OracleOpexReporting", Range("F5").Value, Range("F6").Value, Range("F9").Value, Range("F10").Value, Range("F7").Value, TM1Element, Range("F11").Value)
'process only level 0 elements and sales <> 0 otherwise skip it
If ((Application.Run("ellev", fullDim, TM1Element) = 0) And (total <> 0)) Then

'update the dimension
Sheets("Opex Report").Range("$E$5").Value = "=SUBNM(""" & fullDim & """, """", """ & TM1Element & """, ""Name"")"
Sheets("Opex Report").Range("$B$8").Value = "=SUBNM(""" & fullDim & """, """", """ & TM1Element & """, ""Description"")"
'refresh worksheet
Application.Run ("TM1RECALC")

Sheets("Opex Month View").Select
Application.Run ("TM1RECALC")

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo ErrCatcher
'Sheets(Array("Sheet1", "CopyMe2")).Copy
Sheets(Array("Opex Report", "Opex Month View")).Select
Sheets("Opex Month View").Activate
Sheets(Array("Opex Report", "Opex Month View")).Copy
Sheets("Opex Report").Activate
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets

ThatWorkbook = ActiveWorkbook.Name

Sheets("Opex Report").Select
Cells.Select
Application.CutCopyMode = False
With Selection
.MergeCells = False
End With

Selection.AutoFilter Field:=1

Cells.Copy
Range("A1").PasteSpecial Paste:=xlValues
Cells.Hyperlinks.Delete
Application.CutCopyMode = False

Selection.AutoFilter Field:=1, Criteria1:="1"

Cells(1, 1).Select

Sheets("Opex Month View").Select
Cells.Select
Application.CutCopyMode = False
With Selection
.MergeCells = False
End With


Cells.Copy
Range("A1").PasteSpecial Paste:=xlValues
Cells.Hyperlinks.Delete
Application.CutCopyMode = False

Cells(1, 1).Select

Sheets("Opex Report").Select
Range("A1").Select

' 'Remove named ranges except print settings
' For Each nm In ActiveWorkbook.Names
' If nm.NameLocal <> "Sheet1!Print_Area" And nm.NameLocal <> "Sheet1!Print_Titles" Then
' nm.Delete
' End If
' Next nm

'name report after the branch name
' NewName = Left(Range("$B$9").Value, 4)

'Save it in the branch folder of the same name
'folder = Dir(destination & NewName & "*", vbDirectory)

ActiveWorkbook.SaveCopyAs destination & Period & "_" & Year & "_Dept" & TM1Element & ".xls"
'skip save file confirmation
ActiveWorkbook.Saved = True
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With

End If
Next i

Exit Sub

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub

User avatar
Martin Ryan
Site Admin
Posts: 1957
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: VBA - Bulk Reporting; including subsets

Post by Martin Ryan » Mon Aug 01, 2011 11:47 pm

Not sure which of that code is mine, but nice to be credited!

There is no way in VBA to get a list of the subsets that a dimension has. You could create another dimension that contains the list of subsets and cycle through that.

However what I'd suggest you do instead is create consolidations in the dimension instead of using subsets. Then create an attribute called "Report" that has a 1 or 0 against every N level or C level element that you want to report. Then you can do something like this...

Code: Select all

For i = 1 To Run("dimsiz", fullDim)
TM1Element = Run("dimnm", fullDim, i)
if run ("attrn", fullDim, tm1Element, "Report")=1 then
' Do your reporting
end if
next i
HTH,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

User avatar
Alan Kirk
Site Admin
Posts: 5996
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: VBA - Bulk Reporting; including subsets

Post by Alan Kirk » Mon Aug 01, 2011 11:57 pm

Martin Ryan wrote:Not sure which of that code is mine, but nice to be credited!

There is no way in VBA to get a list of the subsets that a dimension has.
For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"... but I like your idea way better.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
LoadzaGrunt
Posts: 72
Joined: Tue May 26, 2009 2:23 am
Version: LoadzaVersions
Excel Version: LoadzaVersions

Re: VBA - Bulk Reporting; including subsets

Post by LoadzaGrunt » Tue Aug 02, 2011 1:03 am

For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"...
You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...

YMMV

User avatar
Alan Kirk
Site Admin
Posts: 5996
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: VBA - Bulk Reporting; including subsets

Post by Alan Kirk » Tue Aug 02, 2011 1:13 am

LoadzaGrunt wrote:
For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"...
You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...

YMMV
The mileage will usually vary depending on whether it's an admin or an end user who's doing the looking. I try to shy away from solutions that need to look at the data directory these days since it's impossible to deploy them to end users. (Unless of course you get TI to do the listing and outputting the list somewhere, but that might be over-complicating it.) And that even assumes that the Admin has access to the data directory, which some highly paranoid IT departments won't allow.

But you're correct, it is another source of the information.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

l_stoker
Posts: 8
Joined: Thu Oct 14, 2010 3:22 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Re: VBA - Bulk Reporting; including subsets

Post by l_stoker » Tue Aug 02, 2011 5:51 am

Appreciate your guidance guys

Wim Gielis
MVP
Posts: 2259
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: VBA - Bulk Reporting; including subsets

Post by Wim Gielis » Tue Aug 02, 2011 8:30 pm

A bit off-topic, but the code in your VBA procedure could be clearly improved. Some of the issues:

- Dim ThisWorkbook, ThatWorkbook, OpexCurrency, Comparison, Company, OpexMeasure, Period As String
ONLY Period is now declared as string, the other variables are Variants. You need to repeat "As String" for the other variables

- ThisWorkbook is part of VBE, do not use it in your code. Same for Year, which is a function. You should use other variable names.

- You use:
server = "domforecast2"

but below in the code I still see instances of "domforecast2"

- instead of Range("F5"), Range("F6"), Range("F7"), ..., Range("F11"): why not use Named Ranges in Excel? If you insert a row in the sheet above or in that area, you need to change the VBA code

- Selecting and Activating sheets and ranges is not needed in the VAST majority of statements.

-

Code: Select all

                Sheets("Opex Report").Select
                Cells.Select
                Application.CutCopyMode = False
                With Selection
                    .MergeCells = False
                End With
Obviously, merged cells should NEVER be used, but if you do, code it as:

-

Code: Select all

                Sheets("Opex Report").UsedRange.MergeCells = False
- Using With ... End With is advised, but use it in a better way then you do it here.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Post Reply