creating graph from active forms

[grrreen]

hi is there a way i can create a graph from out of active form into a different sheet. I dont want use reporter or web but need this in excel only. any help will be appreciated. i have attached the out file i need.

i need do this many time and kill the tm1 formula once the output is saved.

[asutcliffe]

Is your issue with the chart specifically or do you just want produce lots of static reports? If it's the latter, have you looked at the "print report" wizard?

[Wim Gielis]

Also, see:
http://www.tm1forum.com/viewtopic.php?f=3&t=2671

[lotsaram]

Also, see:
http://www.tm1forum.com/viewtopic.php?f=3&t=2671

Many users struggle with the whole concept of using named formulas and the range for chart series. This is the most elegant and efficient way but I have also come up with another way by using 2 secondary ranges on another sheet; one that holds static text values of the address of the cells in the active form data range that is being created and destroyed and another that uses indirect formula references to pull in the values. You make the number of rows with indirect as large as the maximum expected and return NA if no value to suppress unwanted zeros in the data series. This is less efficient but easier for most users to "get" since the data range for the chart is then a static range that they can see and understand.

Hope that makes sense. If not I can post an example.

[grrreen]

hi lotsaram, can you please post an example, that will be help me alot...

hi asutcliffe... my issue is with the chart only...

Thanks Wim but how do i make this work for me as i have got 2 set active forms in one sheet..

[lotsaram]

Example attached that uses a constant or "normal" range as input for the chart and uses INDIRECT formula to get around the fact that the ultimate source data range is destroyed on Active Form refresh. Note that the sheet name reference is derived from a CELL formula with address reference which means the file must first be saved before the CELL formula will return a value for "address" and therefore all the formulas using INDIRECT also won't work until the file is saved.

All TM1 formulas removed and all data and brand codes randomized to protect the innocent.

AF_Chart_with_INDIRECT.xlsx

(63.65 KiB) Downloaded 524 times

I haven't tested this on the web but I believe TM1 Web now supports INDIRECT but not CELL so it should also work provided the cell formula was removed and the sheet name simply hard coded.

Regarding you question about how to do charts with AFs if you have multiple AFs on a sheet. Answer: still possible but becomes much more difficult. I would really look at whether you need multiple active forms on one sheet as this adds a fair degree of difficulty to charting.