SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting on Data


Reporting on Data

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: Administrators
Points: 225190 Visits: 19641
Comments posted to this topic are about the item Reporting on Data

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43621 Visits: 12057
In addition to formulas not being replicated properly, another problem is when a cell that appears to contain a numeric value is treated as text by Excel, because it contains an embedded space or something. That's easy to overlook when working with a long series of numers. So when applying a @SUM formula across the column, or just highlighting a range of cells, the malformed "number" is excluded from the total.

10.5
12 .1
4.5
-------
15.0

Here is an actual example where the mistake ended up in a published economic report that was cited during the 2012 presidential race.
http://www.businessweek.com/articles/2013-04-18/economists-spreadsheet-error-upends-the-debt-debate


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
kevin_nikolai
kevin_nikolai
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 523
Hi Steve, you mentioned -
We need to learn more about statistics and analysis to ensure that as we work with business people to query data, we understand what information we are extracting out of data sets.

I agree, the problem is:
1. Users themselves don't always know the system/application well enough to ask for meaningful reports.
2. BPA's and Developers don't know how to analyze requirements documentation, or the developer relies on the BPA to acquire the info - unfortunately the BPA might not know the data or application well enough to do it right, or just accepts what the user asks for - without asking why they want it or what they plan on using it for.

Result: forever updating the same reports or creating variations of the same reports.

Most reports are process based (to assist users or managers to check if all is fine),
yet very few are analysis based (which assist directors to check if company is performing ok or facing closure if they maintain status quo).
phonetictalk
phonetictalk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 Visits: 1775
I'm surprised (or I'd be surprised) that there isn't a way to automatically audit Excel sheets for these sorts of mistakes. I know Excel will highlight if a formula isn't consistent with the other formulas in the column or if a cell is formatted as text and others aren't. There has to be (or should be) a tool to centralize that check.

Another problem I could see in Excel (and reports in general): using a hard-coded value. E.g. 31 because that's how many sales areas there are. Works great. When 31 becomes 30, the report is close enough no-one notices. How long, and how wrong, does the report get before anyone notices?

Leonard
Madison, WI
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43621 Visits: 12057
phonetictalk (7/9/2013)
I'm surprised (or I'd be surprised) that there isn't a way to automatically audit Excel sheets for these sorts of mistakes. I know Excel will highlight if a formula isn't consistent with the other formulas in the column or if a cell is formatted as text and others aren't. There has to be (or should be) a tool to centralize that check.

Another problem I could see in Excel (and reports in general): using a hard-coded value. E.g. 31 because that's how many sales areas there are. Works great. When 31 becomes 30, the report is close enough no-one notices. How long, and how wrong, does the report get before anyone notices?

I'm using Excel 2010, and when I format a column as Numeric, then enter a series of floating point numbers with one having space between last whole digit and decimal, there is no visual cue. That malformed number will simply be excluded from the total.
Another problem comes when importing Excel sheets using SSIS. Any rows marked as hidden are excluded. This type of thing can happen when accounting or marketing department submits data in the form of Excel sheets, and they take the liberty of blinging it up with formatting, adding or renaming columns, etc.
PowerPivot, when the underlying data is stored in SQL Server or Analysis Services, and Excel used just as a presentation tool, mitigates these issues to some degree.
But when it comes to storage or exchange of data, Excel is terrible. Always insist that people providing file feeds do so as delimited or fixed width text.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search