Excelcraft

then you know it can be challenging getting accounts and sub-totals where you want them. And forget inserting rows or columns for spacing or to add ratios or commentary; Excel frowns on that.

So how do you layout your financial or management reports exactly the way you want it?

The answer is Excel Cube Formula Reports. To see an example download this free workbook.
(requires Excel 2010 with PowerPivot add-in installed and a reasonable amount of free RAM).

One of Excel's best kept secrets, Cube Formula Reports use CUBE functions that can be positioned anywhere in a worksheet, just like any other Excel functions. This is a huge benefit for accountants, financial managers, FP&A staff and anyone responsible for building financial and management reports and dashboards that require very specific placement.

And just like Excel functions you leverage your existing Excel skills - no new products to learn. Data and presentation (your Excel report) remain separated, drastically reducing the potential for spreadsheet hell. You'll need Excel 2007 or 2010.

Excel Cube Functions allow you to pull data into a worksheet using functions that, like other Excel functions, can be placed anywhere. So feel free to insert/delete/cut/ copy/paste at will; customize your reports just how you want them. They retain the live link to the underlying database and have parameters (row labels, column headers or any other cells) that determine what data value each cube function should pull. Unlike a PivotTable, a Cube Formula Report can retrieve data from multiple sources. When you refresh your worksheet you get the latest updates from the database. With a shared database other users do too - and it's all the same version of the truth.

Despite the name, you are not just limited to cube data. With Excel 2010 and the free PowerPivot add-in you can use Cube Functions to report on PowerPivot data models. These can source data from many different Microsoft and non-Microsoft databases, including tables and ranges in the same workbook the models are created in. So with Excel 2010 and PowerPivot you can generate Excel Cube Functions from an Excel range. This is so much easier and more robust than using the GetPivotData function which requires a PivotTable to reference. Plus, you can share this data in SharePoint. In addition, using Excel PivotTables and Excel Cube Functions, you can access Oracle® OLAP (using MDX Provider for Oracle OLAP from Simba Technologies) and Teradata® OLAP Connector

Maintaining your reports (adding additional months or a new region) can be as simple as copying a row or column and changing the label or heading. To repeat, because cube functions are regular Excel functions you can leverage your existing skills and all of Excel's other functionality - no new products to learn or buy.

So what is the best way to start? That's easy if you use PivotTables.

1. Build a PivotTable from your PowerPivot data or OLAP cube and click on it.
2. Click on the PivotTable Tools tab, OLAP Tools, Convert to Formulas, Convert

But you'll want to build robust and reliable Cube Formula Reports that are easy to use, re-use, maintain and enhance. And you'll want to do this without regular involvement of your IT staff. There is a learning curve here but Excelcraft has the express chair lift.

Building Dynamic Reports and Dashboards with Excel Cube Functions is a self-paced multi-media training course that will quickly and methodically get you up to speed. The course comprises:

• Learning Guide (PDF over 90 pages) with links to
• 35 short, digestible, explanatory videos
• 8 workbook examples that you can re-use
• supported by a sample database

It starts by getting you off and running, developing reports quickly - 80% of the result with 20% of the effort. You'll layout your financial and management reports and dashboards exactly how you want them without the restrictions imposed by PivotTables. In subsequent sections you will learn how to:

• make your reports more robust, easier to maintain, and re-usable
• add drop down lists to make report parameter selection easy for users
• add functionality to check key report numbers and display alerts if numbers don't tie
• use Excel's built-in graphic capabilities to create dynamic dashboards that convey at a glance the values, trend and status of your KPIs, pulled from your centrally-stored data
• automate report printing using VBA macros
• debug formulas, work with nested dimensions, member properties, multiple data sources, add additional data rows and columns to existing reports
• convert legacy "copy/paste/re-key" reports to cube formula reports
• use PowerPivot for Excel (2010) as a data source. PowerPivot can be an aggregator of hundreds of millions of rows of data from multiple Microsoft and non-Microsoft sources (including Excel ranges) - invaluable for rapid prototyping of new BI applications or for self-service BI
• set up your reports so they remain dynamic when published to Excel Services in SharePoint
• generally leverage Excel's functionality and your skills to enhance your reports and dashboards and the report reader's experience

To learn about Excelcraft's money-back guarantee, volume discounts, or order your copy of Building Dynamic Reports and Dashboards with Excel Cube Functions Click Here.

To download and preview the Table of Contents and a sample chapter, free of charge, click Download Free Trial.

A single license allows the purchaser to install the files on up to two computers, for his or her own use. Installation unzips an Adobe PDF file, Excel .xlsx files and Windows Media Video files (.wmv) files into a new folder on your hard drive.