Export a set of reports that iterates through the cost centre dimension

[yyround]

Hi,

I am new to TM1 but have software programming background, currently working in the TM1 support team in a government agency. We are using TM1 10.2.2, but only the support team has access to perspective, the end users can only access TM1 reports through Web. We have been asked by the end user to create a function where one can export a set of reports that iterates through the cost centre dimension to excel for their month-end performance reporting.

Currently, the month end performance pack contains 9 reports, each in a separate excel spreadsheet. The nine reports have same title dimensions (Year, version, cost centre, company). User now has to go to each of the 9 reports, select the same title dimension and snapshot it out to excel (there is a subset in the cost centre dimension that shows only the branch (consolidated) and the 3 sections (N element) the user works on, so when user exports it to snapshot, each workbook contains 4 sheets representing the 4 cost centres). Then user need to rearrange the 9 workbooks into 4 performance packs each has the 9 reports for each cost centre. - I hope it makes sense

I understand that print report wizard in the TM1 ribbon can do that, but first, the user doesnt have perspective(the excel ribbon) installed, but If it is the only constraint, we can change our policy on that. Also, using the print report, one still need to select the same title dimension 9 times and a subset needs to be name for Year and version for it to work, that is a pain.

And I found this post in the forum, looks like it is trying to do similar thing, http://www.tm1forum.com/viewtopic.php?t=5758. But I got stuck in reading this line of code "If Run("dimix", server & ":}Dimensions", myDim) = 0 Then". DIMIX is not a TM1 macro functions according to the TM1 reference guide, can we run DIMIX?

As I said before, I am really new to TM1,would be grateful if I can get some help on how can I achieve this or has anyone ever done that before?

Thanks in advance.

[tomok]

And I found this post in the forum, looks like it is trying to do similar thing, http://www.tm1forum.com/viewtopic.php?t=5758. But I got stuck in reading this line of code "If Run("dimix", server & ":}Dimensions", myDim) = 0 Then". DIMIX is not a TM1 macro functions according to the TM1 reference guide, can we run DIMIX?

DIMIX is not a macro function, it's an Excel function that is available when you have the Perspectives add-in loaded. You can get the results of a TM1 function in VBA by using the "Run" format like you see in that post. Basically, what that person is doing is getting the index number of an element in the dimension and then doing something if it is 0, which would be the result of that function if the element did not exist in the dimension. That bottom line is none of this is going to work without Perspectives. Since you are going to have to give the clients Perspectives in order for them to run reports in batch you might as well use the Print Report functionality.

still need to select the same title dimension 9 times

???? Just make the report have 9 tabs and do them all at once. The Print Report functionality will recalculate the entire workbook, not just one sheet.

[yyround]

Thanks tomok for your response.

I have tried combining all the reports into one workbook, but in the print report wizard, it still asks me to select the same dimensions 9 times (see attached snapshot. For instance, Year dimension appears 9 times). Other than that, the selection of subset doesnt accept temporary subset, i have to create a subset for the user to pick, but I dont know which subset other than the cost centre will the user pick, so i basically have to create a subset for each element in the dimensions. Is my understanding right?

Thanks again.

[tomok]

If dimensions are appearing more than once it's because you have multiple SUBNM formulas in the workbook. Just get rid of the extra SUBNM formulas by pointing the cell in Excel to the first. For example, if you are going to print the report for the same version in all tabs, leave the SUBNM formula in the first tab and go the cell in all the other tabs that have the same SUBNM formula in it and point it to the cell in the first tab. Best practice for organization would be to add an extra tab in the workbook and have all your user selections in that tab. Then consolidate all the identical SUBNM's into one.

[yyround]

Thanks so much tomok