Odd behavior with PivotTable showing zero instead of the value

[cisco55]

Greetings. For a couple years I have been an avid visitor, but I just this moment registered, as the issue is still completely unresolved. Thus, my first post.
(The only forum I trust about MS Excel on the Internet is this one. Mr Excel, you are wise and well-respected in my workbook.

My problem is this:

I use Excel 2010 and have Pivot Tables that are using a Data Connection which is being built (and rebuilt) with VBA.
The Pivot Table field is calculating a field that contains an integer value. The Pivot Table field filter properly displays this value.
However, the Pivot Table itself calculated Sum field only displays 0 (aka zero). Other fields that are exactly the same as this one are displaying properly. Mystifying.

I am assuming you will want to see a little code.
My data connection is built as follows:

ThisWorkbook.Connections.Add "ChartQuery", "", "OLEDB;Persist Security Info=0;DSN=Excel Files;DBQ=" & wbFullName & _        
";DefaultDir=" & wbPath & ";DriverId=1046;MaxBufferSize=1024;PageTimeout=5;BackgroundQuery:=False", sSQL, 2

(And yes, my sSQL is sound, there are no typos, no special character issues)

Keep in mind that the filter in the Pivot Table shows my value (for example, 14) however it always only displays a zero

My Pivot Table is formatting the field (like the others just like it) as a Number, two decimals
My Pivot Table data source is ChartQuery
The worksheet this is pulling from is also set to use Number format for the entire column, but changing that doesn't make any difference even on the other fields that are working.

Perhaps this is enough information to get us started. Please ask the questions that you may.
I've done everything but open my computer case looking for the bug or mistake here.

Thanks
Francisco Shillander

 

[cisco55]

Hopefully this helps.

When I look that the actual connection after it's created, it actually shows:

Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=Excel Files;DBQ=C:\Folder\Filename.xlsm;DefaultDir=C:\Folder;DriverId=1046;MaxBufferSize=1024;PageTimeout=5;"

The SQL:
SELECT `Dates$`.`30 Days`,`MyData$`.Date, `MyData$`.Field1, `MyData$`.`Field 2`, `MyData$`.Field3, `MyData$`.Field4, `MyData$`.Field5, `MyData$`.Field6, `MyData$`.`Field 7`,`MyData$`.Field8, `MyData$`.Field9, `MyData$`.Field10, `MyData$`.Field11, `MyData$`.Field12, `MyData$`.Field13, `MyData$`.Field14, `MyData$`.Field15 FROM {oj `Dates$` `Dates$` LEFT OUTER JOIN `MyData$` `MyData$` ON `Dates$`.`30 Days` = `MyData$`.Date}

The problem field is Field10

 

[Jerry Sullivan]

Hi Francisco,

If a datafield of your PivotTable is being aggregated as "Sum" and displaying 0 instead of numbers, then most likely Excel has interpreted that as a String data type. I understand that you've considered that and done checking to ensure everything is typed as numbers, but for whatever reason Excel sees it differently.

Here's a few things to try...

Instead referencing what I presume is the entire sheet "MyData$", try defining a named range that includes just your data and no blank rows. The named range should be a static named range (not a Table or Dynamic Named Range).

Depending on what version of Excel you are using, try using a different provider. For reading worksheet data sources using Excel 2010 and 2013, I've had good results with

      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .Properties("Extended Properties").Value = "Excel 12.0 Xml;HDR=YES;IMEX=1"
      .Open sFilePathOfDataFile

For earlier versions of Excel....

      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Extended Properties").Value = "Excel 8.0;IMEX=1"
      .Open ActiveWorkbook.FullName

If the Provider has a flag to specify whether your data has headers, use it so Excel isn't using the header as the basis for the data type of a field.

This thread discusses how the mixed-data type fields are evaluated. Consider modifying your registry to evaluate all rows of data instead the default of the first 8 rows.

http://www.mrexcel.com/forum/microsoft-access/625668-ado-connection-excel.html

 

[cisco55]

Hi Jerry,

Thank you for the response and suggestion. I believe your last mention for data type fields may be a significant factor considering my dataset. My ChartQuery is creating a JOIN on a set of dates and creation the relation to my data's dates so that no dates are missed in the Pivot Table and charts. That being said, my data (which you mention may may be analyzed up to 8 rows) is significantly blank.

Unfortunately, I have some limitations on my project:
A) It must be compatible with Excel 2007 - 2013
B) It cannot require addition of reference libraries, or registry updates.

Is there a way that I can explicitly define the format, as many of these auto-detected formats will be looking at blank data? With the exception of the two dates fields, all of the datafields should be numeric.

The odd part is, if the system is analyzing the first 8 rows, then it has a problem with the word Vigorous as a header, the words Light and Moderate are just fine during it's analysis of the 8 rows to make their datafields numeric.

I'm also a bit mystified by the characters ` as being required (the left single quote) and it doesn't accept ' (the right single quote) in the SQL string.

Also, why would the OLEDB provider be replaced with MSDASQL.1 when I actually look at the connection that it creates?

Thanks again!

 

[Jerry Sullivan]

I don't know why one of your fields is being interpreted as String data type and seemingly equivalent fields are not.

Blanks within a field often create problems for PivotTables and typically if a field contains a single blank, the PivotTable doesn't not allow the field to have a NumberFormat applied.

Have you tried altering your SQL query to replace blanks with 0?

 

[cisco55]

I am back to report that nothing I do with the source data, data connection string, or pivot table is changing the behavior of this field.
I have revised my connection string several times, deleting and recreating it upon doing so. I have even deleted and recreated the file, pivot tables, and the charts.
I have changed the registry key TypeGuessRows to set it to 0, also tried 16, used HDR=YES;, and even gave the data set fake values in the first read row to 'help' it determine the data type.

In the Data Connections.Add statement, I used IIF(Field10 IS NULL, 0, Field10) as Field10 and all it returned still was zeros for this one field.
Without the IIF, the entries with values display in the pivot table as zeros, and entries without values are blank. With the IFF, it returned zeros for every row.

I am suspecting a bug, of course. I am almost certain it relates to my connection type, using OLEDB and DSN=Excel Files; but yet I am concerned that it's not consistent for other similar fields.
I even tried replacing the 'or' in the header name Vigorous , in case it was picking up something there, with chr(111) & chr(114) in the connection string, no effect.

If there are any other suggestions they would sure be appreciated.

Additionally, if you can suggest another method of feeding a Pivot Table a range of dates (30 days) with the range from my data set so the pivot table displays every day that otherwise would be missing from the data, it would help me avoid the OLEDB connection altogether.

Thank you.

 

[Jerry Sullivan]

Francisco, If you are able to provide an example of the data file and pivot/chart file with any sensitive data removed), it might expedite finding a solution.
You could upload to a hosting site and post a link, or send me a PM and we'll exchange email addresses.

Regarding other methods of feeding the PivotTable, if you use a X-Axis of values or timescale type (instead of category type), you shouldn't need a to create data points for the blank dates.

 

[cisco55]

Jerry,

The workbook is simply too complex and extensive to share online, but I would be happy to PM you and provide a trimmed version that replicates the issue. Thanks greatly for your help!

 

[Jerry Sullivan]

Francisco, Thanks for sending your file. I experimented with it quite a bit and found it difficult to isolate the cause of the field showing 0's instead of values. It tends to happen when there are many blank rows for that field at the top of the record set; however I couldn't reliably cause the 0s or values to appear just by altering the number of blank rows at the top. There wasn't anything special about that header as I was able to get some of the other fields exhibit the same problem.

Even if we found a fix for this rogue field, I'd suggest you consider alternatives to your current approach of doing an SQL Join as the data source for a PivotTable and presenting the data in a PivotChart. I don't like using Pivot Charts because they are more limited than using ordinary charts.

If you use an ordinary chart you could use a date type scale for the X-Axis which will represent all dates within your 30 day period even if there are no data points for some of those dates. This would eliminate the need for the query and data connection-greatly simplifying things. The data source for the charts could be tied directly to your "raw" data sheet or linked through an intermediate range or table.

 

[cisco55]

Jerry,
Thanks for your analysis. Glad you saw the issue and sorry that it wasn't an easy fix. I'm certain there's some sort of limitation I've stumbled across for good OLE32.dll, as it happens even without blank rows.

I didn't realize that PivotChart had limitations, so it explains why I hadn't seen the options of adding my own data to x-axis.
Filling 'last 30 days' into static x-axis data must be possible.

I will likely rework the reporting aspect of the dataset, and avoid the PivotChart.
The size of the spreadsheet seems to grow exponentially when adding additional components such as charts, perhaps I thought PivotChart would offer some savings as it would be reading the existing PivotTable.

Certainly a lot left to learn. I hope to post some additional questions in new threads in upcoming weeks.

Thanks again! Very helpful.