Pivot Table linked to Table suddenly acts like no data exists in table

[codeliftsleep]

Excel 2016. I have a pivot table that is connected to a table that refreshes from an SQL Server Connection. For some reason all of a sudden Everything is blank. I check the Data Source and it is still connected to the Table by name,but now even if I choose the table and create a new pivot table from it, it acts like there is no data in the table. Everything shows up as blank.

What is going on here??

 

[jim may]

Is your PT on a seperate sheet from your data sheet or location of your data source?

 

[codeliftsleep]

Is your PT on a seperate sheet from your data sheet or location of your data source?

Yes it's on its own sheet. I just created a new sheet and connection, loading a table to that and then I just changed the data source to this table and deleted the other sheet...very weird issue.

 

[jim may]

Check out this way of diagnosis against your PT problem. I found it very helpful.
Perhaps it will assist you in some way...
Jim

A NEW Worksheet is ADDED by the Macro so it doesn't touch/alter any of your data.
It produces Diagnostics ONLY.

Sub ListAllPivotCaches()
' Developed by www.contextures.com
' list all pivot caches in active workbook
Dim pc As PivotCache
Dim wb As Workbook
Dim ws As Worksheet
Dim lRow As Long
Dim wsAll As Worksheet
Dim lPC As Long
Dim lPCs As Long
Dim lFields As Long
Dim lColDate As Long
Dim ptAll As PivotTable
Dim strSource As String
Dim strST As String
Dim rngS As Range
Dim strSourceR1C1 As String
On Error Resume Next
Application.EnableEvents = False
 
lRow = 1
lFields = 7
lColDate = 6
Set wb = ActiveWorkbook
lPCs = wb.PivotCaches.Count
 
If lPCs = 0 Then
  MsgBox "No pivot caches in the workbook"
  Exit Sub
End If
 
Set ws = Worksheets.Add
With ws
  .Range(.Cells(1, 1), .Cells(1, lFields)) _
      .Value = Array("Cache Index", _
        "PTs", _
        "Records", _
        "Source Type", _
        "Data Source", _
        "Refresh DateTime", _
        "Refresh Open")
End With
 
lRow = lRow + 1
 
For Each pc In wb.PivotCaches
  'count the pivot tables
  lPC = 0
  Select Case pc.SourceType
    Case 1
      strSourceR1C1 = pc.SourceData
      strSource = Application.ConvertFormula("=" & _
          strSourceR1C1, xlR1C1, xlA1)
      strSource = Replace(strSource, "[" & wb.Name & "]", "")
      strSource = Right(strSource, Len(strSource) - 1)
      strST = "xlDatabase"
    Case Else
      strSource = "N/A"
      strST = "Other Source"
  End Select
 
  For Each wsAll In wb.Worksheets
     For Each ptAll In wsAll.PivotTables
        If ptAll.CacheIndex = pc.Index Then
           lPC = lPC + 1
        End If
     Next ptAll
  Next wsAll
 
  With ws
    On Error Resume Next
     ws.Range(ws.Cells(lRow, 1), _
      ws.Cells(lRow, lFields)).Value = _
       Array(pc.Index, _
        lPC, _
        pc.RecordCount, _
        strST, _
        strSource, _
        pc.RefreshDate, _
        pc.RefreshOnFileOpen)
  End With
  lRow = lRow + 1
Next pc
 
With ws
  With .Range(.Cells(1, 1), .Cells(1, lFields))
    .EntireRow.Font.Bold = True
    .EntireColumn.AutoFit
  End With
  .Columns(lColDate).NumberFormat _
      = "[$-409]dd-mmm-yyyy h:mm AM/PM;@"
End With
Application.EnableEvents = True
 
End Sub