3 simple steps to Active form and chart harmony

Post Reply
miker100
Posts: 9
Joined: Mon Oct 26, 2009 3:42 pm
OLAP Product: TM1
Version: 9.1 SP4 HF2 x64
Excel Version: 2003

3 simple steps to Active form and chart harmony

Post by miker100 »

First of all apologies if this has been posted before.

As I am sure you are all aware charting from an Active form can be a p.i.t.a so below are 3 easy steps to overcome the challenges.

N.B. Excel 2007 and above required.

1. Create the Active form from perspectives with the rows and columns you wish to chart.

2. In Excel select the rows and columns in the active form and then press ctrl and t together (this will create an Excel Table), accept the defaults.

3. With the area to chart still selected insert a chart. Size and position the chart as required.

N.B. If the chart is within the boundaries of the Active form refresh area you will need to select the chart and then right click and format the chart area select the Properties and then select Don't Move or Size on Cell change.

This solution works with TM1 Web as well.

The reason it works is that the Excel table gives you dynamic ranges so on rebuild the chart refreshes the data based on what is in the Excel table range.

Please feel free to comment or ask questions on this.

Mike
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: 3 simple steps to Active form and chart harmony

Post by macsir »

if you can give example in Excel with snapshot, it would be good. Thanks.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: 3 simple steps to Active form and chart harmony

Post by Mark RMBC »

I know, as am sure lots of us do, that you can easily make charts dynamic in Excel by converting the data into a table but what I hadn't thought of was converting data in TM1 active forms into tables (I think I must have subconsciously thought it wouldn't work in the web!).

But actually it does work pretty well!

So a thumbs up from me

Having said that this makes charts less of a pain the ass, as even with this method the charts can look a bit of a mess, at least in version 10.1!
miker100
Posts: 9
Joined: Mon Oct 26, 2009 3:42 pm
OLAP Product: TM1
Version: 9.1 SP4 HF2 x64
Excel Version: 2003

Re: 3 simple steps to Active form and chart harmony

Post by miker100 »

macsir,

Please follow the link which describes the Creation process in Excel (although without the active form).

https://trumpexcel.com/dynamic-chart-range/

As an added bonus it also shows hot to create dynamic ranges for pre Excel 2007 users.

If you need anything more please let me know.

Mike
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: 3 simple steps to Active form and chart harmony

Post by macsir »

Thanks, haven't used excel table a lot in TM1 web but often use OFFSET function to make dynamic name ranges.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply