Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reporting on Data Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 8:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 31,168, Visits: 15,612
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
Post #1471420
Posted Tuesday, July 9, 2013 7:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:46 AM
Points: 1,703, Visits: 4,844
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
Post #1471607
Posted Tuesday, July 9, 2013 1:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:31 PM
Points: 161, Visits: 461
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).
Post #1471823
Posted Tuesday, July 9, 2013 2:22 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:14 AM
Points: 272, Visits: 1,354
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
Post #1471846
Posted Tuesday, July 9, 2013 3:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:46 AM
Points: 1,703, Visits: 4,844
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.
Post #1471878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse