sql server - Pivot Tables in visual basic -

i hope find because i'm lost on here. have code generates pivot table dataset, need pivot table have 4 calculated fields wich has; problem when fields displayed in excel document displayed in rows , need display in columns. once excel file generated , if drag fields in correct way displays in columns how need displayed catches attention area of fields in excel file have field named "values" in row areas putting columns , columns fixed

here can take of have explained above

by way don't have experience pivottables following example in same project else made, difference file example generates need 1 calculated field, i'll leave code generate pivot table bellow

if _dsdatos.tables.count > 0      dim xlapp object = createobject("excel.application")      dim xlwbook object = xlapp.workbooks.add()      each dt datatable in _dsdatos.tables         dim col, row integer         dim rawdata(dt.rows.count, dt.columns.count - 1) object          'i use loop rename columns         col = 0 dt.columns.count - 1             if dt.columns(col).columnname.toupper = "dsdelega"                 rawdata(0, col) = "planta"                 continue             elseif dt.columns(col).columnname.toupper = "cdartgen"                 rawdata(0, col) = "artcodigo"                 continue             elseif dt.columns(col).columnname.toupper = "dsartabr"                 rawdata(0, col) = "artdescripcion"                 continue             elseif dt.columns(col).columnname.toupper = "cdunm"                 rawdata(0, col) = "um"                 continue                 'elseif dt.columns(col).columnname.toupper = "uteoricas"                 'rawdata(0, col) = "teoricas"                 'continue                 'elseif dt.columns(col).columnname.toupper = "ureal"                 'rawdata(0, col) = "reales"                 'continue             else                 rawdata(0, col) = dt.columns(col).columnname.toupper             end if         next          'as far understand loop wich fills pivottable                                         col = 0 dt.columns.count - 1             row = 0 dt.rows.count - 1                 rawdata(row + 1, col) = dt.rows(row).itemarray(col)             next         next         'creating excel object         dim xlwsheet object         dim urange string = string.format("a1:{0}{1}", excelcolname(dt.columns.count), dt.rows.count + 1)          xlwsheet = xlwbook.worksheets.add()         xlwsheet.name = dt.tablename         xlwsheet.range(urange, type.missing).value2 = rawdata          dim datarange object = xlwsheet.range(urange)          xlwbook.names.add(name:=dt.tablename & "_range", refersto:=datarange)           xlwsheet = xlwbook.worksheets.add()         xlwsheet.name = "pivot_" & dt.tablename          dim ptcache object = xlwbook.pivotcaches.add(sourcetype:=1, sourcedata:=dt.tablename & "_range")         dim pttable object = ptcache.createpivottable(tabledestination:=xlwsheet.range("a3"), tablename:="pt" & dt.tablename)         'here understand fields added rows or columns         pttable             .manualupdate = true             .pivotfields("planta").orientation = 3             .pivotfields("planta").position = 1             .pivotfields("familia").orientation = 1             .pivotfields("familia").position = 1             .pivotfields("artcodigo").orientation = 1             .pivotfields("artcodigo").position = 2             .pivotfields("artdescripcion").orientation = 1             .pivotfields("artdescripcion").position = 3             .pivotfields("um").orientation = 1             .pivotfields("um").position = 4             .manualupdate = false         end         'not sure         dim pf object         each pttable in xlwsheet.pivottables             each pf in pttable.pivotfields                 pf.subtotals(1) = false             next pf         next pttable         try             'here put calculated fields             pttable                 .manualupdate = true                 .calculatedfields().add("teoricas", "=sum(uteoricas)", true)                 .pivotfields("teoricas").orientation = 4                 .pivotfields("suma de teoricas").numberformat = "0.00"                 .calculatedfields().add("reales", "=sum(ureal)", true)                 .pivotfields("reales").orientation = 4                 .pivotfields("suma de reales").numberformat = "0.00"                 .calculatedfields().add("real", "=sum(creal)", true)                 .pivotfields("real").orientation = 4                 .pivotfields("suma de real").numberformat = "$0.00"                 .calculatedfields().add("productividad", "=((ureal)/(uteoricas))", true)                 .pivotfields("productividad").orientation = 4                 .pivotfields("suma de productividad").numberformat = "0.00%"                 .manualupdate = false             end         catch ex exception             msgbox(ex.message)         end try                                          xlwsheet.columns.autofit()          ptcache = nothing         pttable = nothing         xlwsheet = nothing         datarange = nothing      next      'switch excel.     xlapp         .visible = true         .usercontrol = true     end      'tell garbage collector these objects ready destroyed.     xlwbook = nothing     xlapp = nothing      gc.collect()  end if 

i know i'm doing wrong, not know , alredy expended several hours looking in internet, if can me in advance.

daniel

edit:

i managed somehow solve adding line @ end

'display datafields in columns instead of rows  pttable.datapivotfield.orientation = microsoft.office.interop.excel.xlpivotfieldorientation.xlcolumnfield 

if else have same problem appearently has default orientation datafield have change columnfield , work