Planning Analytics for Microsoft Excel report types
When you are using data from an IBM® Planning Analytics data source, you can work in multiple ways: Exploration Views, lists, Quick Reports, Dynamic Reports, Universal Reports, or Custom Reports (reports based on formulas). You can use multiple types of report in a workbook. Each report type has its own benefits and limitations.
Lists
A list shows data in rows and columns. Each column shows the members of a dimension or set.
When you create a list, you use a single drop zone, columns, to create a list of members. A drop zone is an area where you can drag items to include in a report. You can add more columns to populate the list with additional information.
Use list explorations to show detailed information from a data source, such as customer lists or product lists.
You can also create a list to look up the value of a member, and then use the value in another worksheet, for example.
Some of the strengths that are associated with lists include drag-and-drop capabilities and visual cues that are provided by the zone in the overview area. With lists, you can view all of the members in a dimension or set easily and quickly.
- To create a list of members in a dimension
Exploration Views
An Exploration View shows data in rows and columns. An Exploration View also has a context area, which you can use to filter the data in the Exploration View.
When you create an Exploration View, you use drop zones to add objects from the source tree to the rows, columns, and context area of the report. A drop zone is an area where you can drag items to include in a report.
Some of the strengths that are associated with Exploration Views include drag-and-drop capabilities and visual cues that are provided by the drop zones in the overview area.
Use Exploration Views to compare and manipulate data so that you can better understand relationships between data and the relative importance of individual data items.
For example, you look at revenue for the years 2012 - 2015 by sales region. You notice a dip in the revenue for 2014. You focus the Exploration View on 2014 only and expand 2014 to show revenue results by quarter. You then replace the sales region dimension with the products dimension to explore revenue in 2014 from a different perspective.
- To find answers to simple questions that can be found in your data source, such as the revenue for Tents in the Americas for 2015
- To build interactive Exploration Views that you or another user can drill up and down in
- When you are not concerned with formatting
- To build a data set to convert and use in a more complex analysis, such as a formula-based report or a Dynamic Report
Quick Reports
A Quick Report shows data in rows and columns. A context area above the Quick Report shows the context members.
When you use Quick Reports, you drag-and-drop objects onto the regions of the report: rows, columns, context, and data. You can create Quick Reports by using existing Exploration Views or views. You can create multiple Quick Reports on a worksheet. You can also use multiple data sources.
For example, you create three Quick Reports on a worksheet to show different views of financial performance by region. You then add a fourth Quick Report that uses a different IBM Planning Analytics data source. You then create a Microsoft Excel calculation that references cells in the four Quick Reports to add more information to the report.
Some of the strengths that are associated with Quick Reports include the ability to present multiple views that use the same or different data sources in a worksheet, the ability to build complex layouts, and the ability to add Excel calculations, charts, and formatting. With Quick Reports, you can move beyond simple Exploration View layouts while still having the advantage of visual cues provided through the Quick Report regions.
- To build complex, highly formatted reports
- To use multiple data sources or servers
- When the row and column definitions will not change
Custom Reports
When you use Custom Reports, you use TM1® worksheet functions to build the report. You can create a Custom Reports report from scratch, you can build an Exploration View or Quick Report and convert it to formulas, or you can create Custom Reports from a cube view.
Some of the strengths that are associated with Custom Reports include the ability to use multiple data sources in the same worksheet; the ability to move cells, rows, and columns; and the ability to add Excel calculations, charts, and formatting. With formula-based reports, you can move to a custom or complex layout.
- To build a complex report that cannot be easily achieved with Quick Reports or Dynamic Reports
- To work with data from multiple servers and data sources in a report
- When formatting is important
Dynamic Reports
A Dynamic Report shows data in rows and columns. A context area above the Dynamic Report shows the context members. Dynamic Reports use TM1 functions to define the components of the report, such as context members, row members, and display properties. Dynamic Reports also use formulas to apply formatting to rows automatically. You can create a Dynamic Report from scratch or by converting an Exploration View to a Dynamic Report.
Some of the strengths that are associated with Dynamic Reports include the ability to have dynamic row members with customized formatting, the ability to expand and collapse rows, and the ability to use Excel calculations and other Excel features in the report. With Dynamic Reports, you can create more complex reports while still using interactive features, such as expanding and collapsing rows.
- To build a complex report that cannot be easily achieved with Quick Reports
- To apply formatting automatically to rows
Universal Reports
A Universal Report is a hierarchy aware and parameterized report type that achieves its parametrization through the use of named ranges and named range schemas. You can use Universal Reports to create hierarchy aware and parameterized reports, which can dynamically execute formulas and queries against the TM1 Server.
Formulas that are used in a Universal Report can be used to recognize not only a single member, but also the member's position in a hierarchy of data. Universal Report formulas can be used to recognize leaf members, a single reference to the member itself, or a reference to the entire tree below a member. You can use formulas in a Universal Report to read from and write to cells. A Universal Report's layout places key data into known specific cells, such as named ranges. You can make really powerful changes to a Universal Report by directly editing the hidden controller-cells.
- To build a complex report that cannot be easily achieved with Quick Reports or Dynamic Reports