Tag Archives: reporting

Big Data

How I Learned to Love My Data: Gobbles and Gobbles of Data

Love your dataLet me preface this by saying I am a communications major, a lover of language and all things related to the humanities, following the auspices of the left brain. Science, statistics, numbers, data – that was for my logically-minded friends. Attending a research university, I was constantly surrounded by studies, which as you guessed it, are based off of piles and piles of data. It’s not that I didn’t understand the importance of data, it’s that I just never loved it. As a communications major I tended to shy away from numbers. (Okay, more like run flailing in the opposite direction as though my life depended on it.) Turns out numbers are a very real part of marketing, if not the crux of every marketing campaign. It allows you to measure what is working for your goals and what needs adjustment.

Generally speaking, I love the insights it gives, the conclusions it reaches. I just don’t enjoy the process of data collection in order to reach those conclusions. But who does? With data tied to many different sources, and housed in varying formats, it’s not easy to make it come together in one simple report. I’d like my data handed to me, preferably on a silver platter. Yes, well, that’s not how it works. And that’s not how it should work. In order to really understand the insights and not be misled by false assumptions, you should be able to understand where this data is coming from, how things are being measured, and what the goals are behind it.

Working at a software company, whose product deals with a ton of data and is designed for companies processing it to perform their reporting, I’ve had to become more comfortable with it. In any job this is a valuable skill to possess. Being able to deliver reports and present your work and results to the company/client/manager is a very necessary part of any business, and one that CEOs and execs place a lot of stake in. Not only that, it puts a tangible number to your work you can point to, to assess improvements and successes.

While there is this necessary business side to data collection, that doesn’t have quite the same motivation to learning to fully appreciate it. As I dove deeper into the weeds – spreadsheets, SSRS, Big Data, dark data, and servers – I discovered the ways in which people were using these numbers, the artful approach to using and displaying the information that is being collected. My coworkers showed me spreadsheets can be the springboard for masterpieces (see: Baking Cookies in Excel and Making Art with Excel). Speaker and data visualization blogger, Cole Nausbaumer, showed me you can infuse creativity into numbers. In her Storytelling with Data blog, she shows the meshing of the creativity behind presenting your data in a way people can relate to and process it: the age old art of storytelling. Now that is something to which I can relate. (If you haven’t yet, you should read her blog, and pick up tricks on data visualization.)

Along the same lines of displaying your data, Continue reading How I Learned to Love My Data: Gobbles and Gobbles of Data →

Events, Excel, OfficeWriter, Webinars

New Webinar! Make Reports that Measure Up

Take a look at how OfficeWriter can turn your drab Excel reports into chart-topping spreadsheets. This month, it’s all about music as we cover your favorite bands, artists, and labels.

In this webinar we’ll cover:

  • Grouping and nesting in Excel
  • Using SQL Server Reporting Services (SSRS)
  • Charting in Excel and relationships between genre, artists, labels, and album price

When: Friday, August 23, 2013 at 1 P.M. EST

*Register early as space is limited.

Can’t attend? Register anyway, and we’ll send a copy of the slides and recording following the webinar.



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 →

Business Intelligence, SQL Server, SSRS

Automating Reports with SSRS Subscriptions

SQL Server Reporting Services subscriptions are an easy way to automatically run and deliver SSRS reports. You can schedule reports to run at a regular interval and deliver to file share, e-mail, or a SharePoint document library (if you’re using SharePoint integration mode).

How it’s done:

Here is my SSRS 2008 R2 instance, with a couple of prepared reports:

I’m going to create a subscription for SSRS_example_multivalue, which has a parameter with multiple values to select from. Continue reading Automating Reports with SSRS Subscriptions →

ExcelWriter, Knowledge Base, OfficeWriter

Exception: ‘[character]’, hexidecimal value 0x[value], is an invalid character

Problem

When running an Excel report rendered by ExcelWriter’s ExcelTemplate object, the report will throw the following eception if the data set contains certain special characters:

Unhandled Exception: SoftArtisans.OfficeWriter.ExcelWriter.SAException: ‘[character], hexadecimal value 0x[value], is an invalid character. at SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate.Process()

This exception occurs if:

  • Data is imported into an Excel file using ExcelWriter
  • Files are .XLSX (Office 2007) or .XLSM (Office 2007 with macros enabled)
  • Data set contains a character that is not valid in XML

Note: Support for OOXML files was introduced in OfficeWriter 4.0, so this issue only occurs in OfficeWriter 4.0-4.6.0 (ExcleWriter 7.0 – 7.6.0).

Solution

Option 1: Upgrade to OfficeWriter 4.6.1 or later (recommended)

This issue was addressed in OfficeWriter 4.6.1 (ExcelWriter 7.6.1). As of version 7.6.1, ExelWriter correctly preserves characters that are not valid in XML, so they will appear in output.

Product updates for OfficeWriter can be downloaded at www.officewriter.com/product-updates.

Note: Make sure to uninstall the old version of OfficeWriter before installing a new version.

If you are experiencing this issue with OfficeWriter for SQL Server Reporting Services and need to upgrade the server-side installation of OfficeWriter, we generally recommend upgrading the client-side installation of the OfficeWriter Designer as well.

Option 2: Scrub data of invalid XML characters

There are certain ranges of characters that are not valid XML. The XML specification has defined which ranges of characters are supported in XML files. These files cannot be written into XML files, which includes the OOXML file formats. Excel handles the special characters by encoding them before they are written to file.

Since ExcelWriter 7.0-7.6.0 does not handle these characters properly, the other solution is to remove any characters that are not valid XML, if the characters don’t need to be preserved.