Tag Archives: pivottables

Business Intelligence, Excel

5 Underutilized Excel Features To Take Advantage Of

Let’s get straight to the point, because frankly who wants to waste any more time finagling your data and reports? The following are the top 5 Excel features I use on a constant basis to get the most out of my data.

For reference, you can download the example workbook I used in this post: Top5ExcelFeatures.xlsx

#5 – What-If Analysis

WhatIfAnalysis

I actually only started using this one recently, but it’s quickly become a favorite. I’m particularly fond of the Scenario Manager function. What-If Analysis is comprised of three pieces: Scenarios, Goal Seek, and Data Tables. 

Scenarios has the ability to define a scenario that is associated with a particular set of cell values. You can define new scenarios that are tied to different cell values. When a new scenario is loaded, all of the values update. This is great for flipping between Best Case and Worst Case views of a worksheet.

WhatIfAnalysis2

The other two pieces are Goal Seek and DataTables. In Goal Seek, Excel automatically computes and finds a calculated value based on the value of another cell, such as finding an interest rate based on a monthly payment. Data Tables allows you to hook up entire tables of values based on up to two variables.

#4 – Sparklines

Sparklines

These mini-charts were released in Excel 2010 and they provide a quick way to Continue reading 5 Underutilized Excel Features To Take Advantage Of →

Events, Featured, OfficeWriter, Programming, Webinars

New! [Webinar] PivotTables in OfficeWriter 8.4

Because we had such a great response from the first webinar, we’re opening more seats and hosting this PivotTable webinar again.

Take a first look at the new PivotTable API within OfficeWriter 8.4 in this interactive webinar.

Our Senior Sales Engineer (and adept demo master), Chad Evans, will walk you through several ways PivotTables can help you wrangle and report on your data. Bring your questions or email them beforehand and we’ll be happy to include them.

When:

Friday, March 1, 2013 at 1 p.m. EST

What we’ll cover:

  • How to use PivotTables to better sort and filter your data
  • How to programmatically create Excel PivotTables in OfficeWriter
  • How to programmatically update existing Excel PivotTables in OfficeWriter
  • Your questions

Spots are limited. Save your seat and register today.


Can’t attend, but still want to learn more? Register anyway! We’ll send the slides and a recording of the webinar after the event.

ASP.NET, Events, Excel, OfficeWriter, Programming, Webinars

WEBINAR Feb 22nd: PivotTables in OfficeWriter 8.4

Have you heard? OfficeWriter 8.4 with PivotTable support is here! You’re invited to take a first look at the new PivotTable API within OfficeWriter 8.4 in this interactive webinar.

Our Senior Sales Engineer (and adept demo master), Chad Evans, will walk you through several ways PivotTables can help you wrangle and report on your data. Bring your questions or email them beforehand and we’ll be happy to include them.

When:

Friday, February 22, 2013 at 1 p.m. EST

What we’ll cover:

  • How to use PivotTables to better sort and filter your data
  • How to programmatically create Excel PivotTables in OfficeWriter
  • How to programmatically update existing Excel PivotTables in OfficeWriter
  • Your questions

Spots are limited. Save your seat and register today.



Can’t attend, but still want to learn more? Register anyway! We’ll send the slides and a recording of the webinar after the event.

ASP.NET, Excel, Featured, News Releases, OfficeWriter

PivotTables Now Available within OfficeWriter 8.4

OfficeWriter 8.4

WATERTOWN, MA (February 12, 2013) – SoftArtisans announced the addition of PivotTables to their OfficeWriter product today. OfficeWriter 8.4 is now available for download. Included in this new version, is the ability to create, modify, and remove PivotTables within users’ Excel workbooks. With the new PivotTable functionality customers have the ability to add Report Filters to better filter and sort their data, as well as change the data source of a PivotTable (including PivotTables already copied with CopySheet). This provides users with more fine-grained control over their data and reports.

Also included in this product release are new features to their WordTemplate model. Within WordTemplate DOCX files, users now have the option to programmatically set the document properties of their DOCX files and to remove bookmarked content when delivering reports.

OfficeWriter provides customers Continue reading PivotTables Now Available within OfficeWriter 8.4 →

ASP.NET, Excel, Featured, News Releases, OfficeWriter, Programming, SQL Server, SSRS

What’s new in OfficeWriter 8.4

PIVOT TABLES ARE HERE!

OfficeWriter 8.4 packs a powerful punch with exciting new features, most notable of which is ExcelApplication support for PivotTables in OOXML (XLSX, XLSM) files. ExcelWriter already supports the use of PivotTables in ExcelTemplate and SSRS reports,  but now you can programmatically create, manipulate, and remove PivotTables with ExcelApplication.

BlogPostPivot

The new PivotTable API gives you the freedom to:

  • Create PivotTables from scratch (see our tutorial on Creating a Basic PivotTable)
  • Add and manipulate PivotTable fields – data value fields, column labels, row labels, and report filter page fields
  • Access common settings like empty/error values, refresh data when opening a file, and the number of cached items to retain

With the API you also have the ability to change the data source of a PivotTable. Continue reading What’s new in OfficeWriter 8.4 →

Excel, OfficeWriter

NEW OfficeWriter ASKs Forum

You have opinions and we want to hear them. That’s why we’ve created OfficeWriter Asks, a new forum where you can post all of your feedback and wishlist items. Our aim: to bring you the features you desire. Each week we’ll pose a new question related to our OfficeWriter product and how we can improve upon the features you use and need.

This week on OfficeWriter Asks:
  • Pivot Tables
    Love them? Hate them? How do you use them? 







ExcelWriter, Knowledge Base

How to use PivotTables with ExcelTemplate

Solution

If you are interested in using pivot tables in your ExcelTemplate reports, please refer to our documentation on Templates and Pivot Tables.

NOTE: Only use the above guide if you are using ExcelWriter 7.6 or later. There were several issues with using data markers with pivot tables that were resolved in ExcelWriter 7.6.

If you are using ExcelWriter 7.5 or later, please refer to Templates and Pivot Tables in Older Versions instead.

ExcelWriter, Knowledge Base

Setting PivotTable styles with ExcelApplication PivotTables

Problem

In Excel, there are numerous PivotTable settings available. These can be found by right clicking the PivotTable and selecting PivotTable options. Not all of these are accessible through the ExcelApplication PivotTable API yet.

Solution

For any properties that are not yet available in ExcelApplication, we recommend that you create a template file that contains a PivotTable and set the PivotTable options on that table, rather than creating a PivotTable from scratch.

Then use ExcelApplication to modify the existing PivotTable by changing the data source and adding PivotFields.

The settings will be preserved, even if the data source of the PivotTable is changed and new PivotFields are added.

To get a handle on a PivotTable:

 worksheet.PivotTables[0]; //By index in the worksheet worksheet.PivotTables["PivotTable1"]; //By name

To change the data source of a PivotTable, use PivotTable.ChangeDataSource.

For more about building PivotTables, see Creating a Basic PivotTable.

ExcelWriter, Knowledge Base, OfficeWriter

PivotTables are not displaying correctly

Problem

After creating or modifying a PivotTable with ExcelWriter 8.4 or later, the PivotTable has not changed or is empty.

Solution

ExcelWriter does not have the ability to render PivotTables. Changes made to a PivotTable using ExcelApplication will not take effect until the PivotTable is refreshed. There are several ways to refresh the PivotTable:

  1. If you are editing an existing PivotTable, in the original file, go to PivotTable Options > Data > Check off “Refresh data when opening the file.” This will ensure that Excel refreshes the PivotTable as soon as the output is opened.
  2. If you are creating a new PivotTable from scratch, make sure to set the PivotTable.PivotTableSettings.RefreshOnOpen to true. This will force the PivotTable to refresh automatically when the output is opened.
  3. If you don’t want to have Excel refresh the PivotTable when the workbook opens, you can refresh the PivotTable manually or with a macro. Right-click the PivotTable and select Refresh.

We encourage users to keep “Refresh on Open” enabled when working with PivotTables in ExcelWriter to make sure that modified PivotTables load correctly in Excel.

ExcelWriter, Knowledge Base, OfficeWriter

Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’

Problem

When opening an Excel file containing a pivot table that was streamed to an IE client, Excel will throw the following error message:

Cannot open PivotTable source file ‘[filename[1].xls]SourceData’

This is caused by IE caching the streamed file with a temporary name containing brackets (e.g. filename[1].xlsx). Since Excel does not allow pivot table data sources to contain brackets, when the cached file is opened, the pivot tables try to refresh using the temporary file name.

Solution

There are several ways to work around this:

  • Use a non-IE browser, such as Firefox or Chrome
  • Use IE 9 or later – this appears to have been resolved in the newer versions of IE
  • Save the file to disk before opening it to avoid opening the file with a temporary name
  • Stream the file inline instead of an attachment. This will open the file in IE’s Excel plugin instead of downloading it. If you are using IE 7 or later, you will need to configure the registry on the client to use this option.
  • Add Excel macros to the report to dynamically rename the PivotTable to exclude the bracket and then refresh the PivotTable. An example of these macros is available in this blog post.

Note: This is not an issue with the OfficeWriter product. This behavior occurs anytime someone attempts to download and open an Excel workbook with a PivotTable directly from certain versions of IE.

We have made these solutions available online because our customers have found the information helpful. However, these solutions are not covered under OfficeWriter product support. If you experience issues implementing any of these solutions, we encourage you to reach out to the appropriate vendor (i.e. Mozilla for Firefox, Google for Chrome, and Microsoft for IE 9 (or later) or to configure the in-line plugin).

In particular, if you encounter difficulties with the macro solution, feel free to leave a reply on the blog post so that the author of the post can address your concerns.