Home

Excel Chart Error from SQL Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-844460%22%20slang%3D%22en-US%22%3EExcel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-844460%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20pulling%20SQL%20values%20into%20excel%20and%20the%20values%20associated%20with%20dates%20and%20times%20will%20not%20plot.%20There%20are%20roughly%2010%2C000%20data%20points%20and%20the%20'values'%20column%20comes%20in%20as%20'general'%20designated%20formatting.%20I%20changed%20it%20to%20number%20but%20still%20no%20data%20is%20registering%20in%20the%20graph.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20suggestions%20to%20help%20get%20past%20this!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130764iE2F6428B413AEFFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2220190906_153108.jpg%22%20title%3D%2220190906_153108.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-844460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-844874%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-844874%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406027%22%20target%3D%22_blank%22%3E%40dpvk8%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20you%20can%20take%20a%20screenshot%20with%20the%20built-in%20Windows%20tools.%20Every%20computer%20has%20the%20%22Snipping%20Tool%22.%20Hit%20the%20Windows%20button%20and%20start%20typing%20%22Snipping%20tool%22%20and%20it%20will%20come%20up.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20pivot%20table%2C%20the%20number%20values%20must%20be%20in%20the%20%22Values%22%20area%2C%20not%20the%20%22Axis%22%20(or%20%22Rows%22)%20area%20of%20the%20pivot%20table.%26nbsp%3B%20Pivot%20charts%20need%20the%20numbers%20in%20the%20Values%20area.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20drag%20the%20%22Value%22%20field%20from%20the%20Axis%20to%20the%20Values%20area.%20If%20it%20shows%20as%20%22Count%20of%22%2C%20right-click%20and%20change%20the%20calculation%20options%20to%20%22Sum%22.%20If%20that%20does%20not%20work%2C%20your%20source%20data%20may%20still%20contain%20text%20values%20as%20well%20as%20numbers%20and%20you%20need%20to%20clean%20the%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-845009%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845009%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20helping%20me.%3C%2FP%3E%3CP%3EWhen%20I%20move%20over%20the%20value%20field%20to%20%22VALUES%22%20and%20change%20it%20to%20sum%20all%20my%20data%20points%20change%20to%200.0000.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130825iA3635474A381D09C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-845916%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406027%22%20target%3D%22_blank%22%3E%40dpvk8%3C%2FA%3E%26nbsp%3B%20Can%20you%20post%20a%20small%20data%20sample%20and%20upload%20as%20an%20Excel%20file%3F%20click%20the%20paper%20clip%20icon%20below%20the%20reply%20box.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847420%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BI%20have%20attached%20one%20tab%20of%20this%20worksheet.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847825%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406027%22%20target%3D%22_blank%22%3E%40dpvk8%3C%2FA%3E%26nbsp%3BIn%20your%20source%20data%2C%20the%20values%20are%20stored%20as%20text.%26nbsp%3B%20You%20need%20to%20convert%20them%20to%20real%20numbers.%20If%20the%20data%20comes%20from%20SQL%2C%20you%20may%20want%20to%20ensure%20the%20SQL%20query%20uses%20the%20correct%20data%20type.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20data%20in%20SQL%20cannot%20be%20converted%20to%20numbers%2C%20you%20can%20use%20Power%20Query%20instead%20of%20a%20direct%20SQL%20query%20to%20get%20the%20same%20data%20and%20clean%20it%20up%20in%20the%20Power%20Query%20editor.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889537%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889537%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20convert%20it%20to%20a%20number%20in%20Excel%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889676%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Chart%20Error%20from%20SQL%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406027%22%20target%3D%22_blank%22%3E%40dpvk8%3C%2FA%3E%26nbsp%3BYes.%20You%20can%20use%20Power%20Query%20to%20pull%20the%20data%20from%20SQL%20and%20change%20the%20data%20type%20in%20Power%20Query.%20It's%20a%20lot%20more%20powerful%20than%20the%20old%20style%20SQL%20query%20connection.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20a%20quick%20Once-Only%20conversion%20from%20text%20to%20numbers%2C%20you%20can%20do%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3Ewrite%20a%200%20into%20an%20empty%20cell%3C%2FLI%3E%0A%3CLI%3Ecopy%20that%20cell%3C%2FLI%3E%0A%3CLI%3Eselect%20all%20the%20cells%20that%20have%20numbers%20stored%20as%20text%3C%2FLI%3E%0A%3CLI%3Eopen%20the%20Paste%20Special%20dialog%20from%20the%20Home%20ribbon%20and%20select%20%22Add%22%2C%20then%20click%20OK%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20494px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135182i0FA2EE2D454ED099%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22CoerceToNumberPasteAdd0.gif%22%20title%3D%22CoerceToNumberPasteAdd0.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
dpvk8
New Contributor

Hello,

I am pulling SQL values into excel and the values associated with dates and times will not plot. There are roughly 10,000 data points and the 'values' column comes in as 'general' designated formatting. I changed it to number but still no data is registering in the graph. 

 

Thank you for any suggestions to help get past this! 

 

20190906_153108.jpg

7 Replies

Hello @dpvk8,

 

First, you can take a screenshot with the built-in Windows tools. Every computer has the "Snipping Tool". Hit the Windows button and start typing "Snipping tool" and it will come up.

 

In the pivot table, the number values must be in the "Values" area, not the "Axis" (or "Rows") area of the pivot table.  Pivot charts need the numbers in the Values area.

 

So, drag the "Value" field from the Axis to the Values area. If it shows as "Count of", right-click and change the calculation options to "Sum". If that does not work, your source data may still contain text values as well as numbers and you need to clean the

Thank you for helping me.

When I move over the value field to "VALUES" and change it to sum all my data points change to 0.0000.

Capture.PNG

@Ingeborg Hawighorst 

@dpvk8  Can you post a small data sample and upload as an Excel file? click the paper clip icon below the reply box.

@Ingeborg Hawighorst I have attached one tab of this worksheet. 

@dpvk8 In your source data, the values are stored as text.  You need to convert them to real numbers. If the data comes from SQL, you may want to ensure the SQL query uses the correct data type.

 

If the data in SQL cannot be converted to numbers, you can use Power Query instead of a direct SQL query to get the same data and clean it up in the Power Query editor.

 

 

Is there a way to convert it to a number in Excel?

@dpvk8 Yes. You can use Power Query to pull the data from SQL and change the data type in Power Query. It's a lot more powerful than the old style SQL query connection.

 

If you need a quick Once-Only conversion from text to numbers, you can do this:

 

  • write a 0 into an empty cell
  • copy that cell
  • select all the cells that have numbers stored as text
  • open the Paste Special dialog from the Home ribbon and select "Add", then click OK

CoerceToNumberPasteAdd0.gif

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies