Creating a Dynamic Print Area in a Dynamic Report in TM1
I was working on a Dynamic Report today and needed to have some selection criteria visible at the top of the report that I did not want to print. Easy I thought, I will just do a set of Print_Titles for the rows I need at the top of the report and the data will print below them. Wrong. My selection cells printed as well, above the print titles! So I needed to define a Print_Area, but have it dynamically updated based on the length of the Dynamic Report (or Active Form if you are still using Perspectives). Here is the detailed instructions on how to create a dynamic print area for a Dynamic Report in TM1.
I found a great here that detailed the method I used, I am going to adopt it for TM1 though.
The Excel OFFSET Function
We’re going to use the Excel Offset function to define the print area. The syntax of this function is:
=OFFSET (startcell, rows, cols, [height], [width])
- startcell– The starting point, supplied as a cell reference or range.
- rows – The number of rows to offset below the starting reference.
- cols – The number of columns to offset to the right of the starting reference.
- height – [optional] The height in rows of the returned reference.
- width – [optional] The width in columns of the returned reference.
Steps to Create a Dynamic Print Area in a TM1 Dynamic Report
1.You need to create a dynamic named range at first. Click Formulas > Name Manager. See screenshot:
2. In the Name Manager dialog box, click the New button to open the New Name dialog box. And in the New Name dialog box, enter Print_Area_Formula into the Name box, and enter formula =OFFSET(startcell,1,1,COUNTA($A:$A$)-X,width) into the Refers to box, and finally click the OK button. See screenshot:
Note: in the formula, startcell is the cell in column A that is on the same row as the header cells of the Dynamic Report table, X represents the number of rows with data in your Dynamic Report Format Area (thus that will be subtracted from the total count of cells returned from the COUNTA on column A) and width is the number of columns in the Dynamic Report from column B onwards (so if the Dynamic Report goes out to column H, that would be 7 (cos H is 8th column less 1 for column A). You can change them as you need.
When it returns to the Name Manager dialog box, please close it.
In the Active Form, select the range with data you would normally set as print area then click Page Layout > Print Area > Set Print Area.
And here is the clever bit. Click Formulas > Name Manager again to reopen the Name Manager dialog box.
In the Name Manager dialog box, select the Print_Area in the Name box, then replace the original formula with = Print_Area_Formula (the name of the dynamic range you created above) in the Refers to box, and then click the button to save the change. Finally close the Name Manager dialog box.
Now the dynamic print area is created. You can see the print area is adjusting with the data you added or deleted until it reaches the specified row and column. You might need to play with the definition of Print_Area_Formula to get it exactly what you need.
Then regenerate the Dynamic Report and the print range should expand to fit the required cells.