Sub Armenia_Forecast_Pivot()
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array("ODBC;DSN=MS Access Database;DBQ=***;DefaultDir=***;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT OLAP_Forecast.Date, OLAP_Forecast.Year, OLAP_Forecast.Month, OLAP_Forecast.Lag, OLAP_Forecast.Sequence_Number, OLAP_Forecast.Brand_Name, OLAP_Forecast.SKU_Name, OLAP_Forecast.Forecast, OLAP_For", "ecast.IMS_Volume" & Chr(13) & "" & Chr(10) & "FROM `***`.OLAP_Forecast OLAP_Forecast")
.CreatePivotTable TableDestination:=Range(Pivot_cell.Value), TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
End With
With ActiveSheet.PivotTables("PivotTable2")
.PivotFields("Year").Orientation = xlRowField
.PivotFields("Year").Position = 1
.AddDataField ActiveSheet.PivotTables("PivotTable2").PivotFields("Month"), "Sum of Month", xlSum
.PivotFields("Sum of Month").Orientation = xlHidden
.PivotFields("Month").Orientation = xlRowField
.PivotFields("Month").Position = 2
.PivotFields("Lag").Orientation = xlPageField
.PivotFields("Lag").Position = 1
.PivotFields("Sequence_Number").Orientation = xlPageField
.PivotFields("Sequence_Number").Position = 1
.PivotFields("Brand_Name").Orientation = xlPageField
.PivotFields("Brand_Name").Position = 1
.PivotFields("SKU_Name").Orientation = xlPageField
.PivotFields("SKU_Name").Position = 1
.AddDataField ActiveSheet.PivotTables("PivotTable2").PivotFields("Forecast"), "Forecast ", xlSum
.AddDataField ActiveSheet.PivotTables("PivotTable2").PivotFields("IMS_Volume"), "IMS_Volume ", xlSum
.DataPivotField.Orientation = xlColumnField
.DataPivotField.Position = 1
.PivotFields("Lag").CurrentPage = "3"
.PivotFields("Sequence_Number").CurrentPage = "1"
.PivotFields("Brand_Name").CurrentPage = "AAA"
.CalculatedFields.Add "Forecast Accuracy", "=1 - ABS((Forecast -IMS_Volume )/Forecast )", True
.PivotFields("Forecast Accuracy").Orientation = xlDataField
.PivotSelect "'Sum of Forecast Accuracy' Lag['3'] Sequence_Number['1'] 'AAA'", xlDataAndLabel, True
.PivotFields("Sum of Forecast Accuracy").NumberFormat = "0.00"
.PivotFields("Sum of Forecast Accuracy").Caption = "Forecast Accuracy "
.CalculatedFields.Add "Persentage_Error ", "= (Forecast -IMS_Volume )/Forecast ", True
.PivotFields("Persentage_Error ").NumberFormat = "0.00"
End With
End Sub
Вот процедура, которая создает сводную (я после рекордера ее немножко поправил). Все работает. Куда (и можно ли) прописать, чтобы если пользователь решит строить сводную диаграму из данной сводной таблицы, он сразу получал дуаграмму заданого типа и формата?