IBM Planning Analytics Workspace has several chart visualizations that you can use in place of Explorations. However, this functionality is not available in Planning Analytics for Excel (PAx). Excel has its own chart functionality, but it gets a bit tricky if we want to use it in conjunction with Dynamic Reports.
When a Dynamic Report is rebuilt, it deletes all rows below the first row in the report section and then copies and inserts the first row x times, with x corresponding to the number of rows in the TM1RPTVIEW. In an Excel chart, data series reference specific ranges in the sheet. When these ranges are deleted, the data series will return a #REF error.
To save you the trouble of having to reassign the data series each time the dynamic report rebuilds, we will need the data series to dynamically determine where the referenced data is. Unfortunately, the data series will not accept any functions. It only accepts references. To get around this, we would first need to create named ranges that use the Excel OFFSET function.
In the above example, we created a named range called ChartData3. It uses the OFFSET function to move the reference from cell N69 down 6 rows and span the range across 78 columns. Because row 69 is the first row of our dynamic report, it will never be deleted and cause a #REF error.
With the named range in place, we can now go back to the data series to reference it in the series values field.
With dynamic reports, it’s possible for new rows to be added or rows removed depending on data that’s in the cube. To plan for new rows in the dynamic report, we can establish a set number of data series in our chart. That way, when we do need the additional series, we can go into the “Select Data Source” chart section to enable the series that we need instead of recreating them. Conversely, we can also disable series that we don’t need when rows are removed.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more IBM Planning Analytics Tips & Tricks posts:
IBM Planning Analytics Tips & Tricks: Excel OFFSET Function
IBM Planning Analytics Tips & Tricks: Fill Function
IBM Planning Analytics Tips & Tricks: Sparklines
