Creating Pivot Table using Database

[chandrashekar]

Hi,

How to create pivot table using access database. Pls help me out.

Thanks in advance.

 

[Andrew Poulsom]

This may help you:

http://office.microsoft.com/en-us/a...excel-quick-financial-charts-HA001103912.aspx

 

[chandrashekar]

Hi,

Thanks for your reply.

I need a vba code to do this task.

Regards,

Chandra Shekar B

 

[Andrew Poulsom]

Have you tried recording a macro?

 

[chandrashekar]

Hi,

In tried below code but here I am getting a problem i.e I am trying to add all subject total and getting percentage out of it.

1) pvt_tbl.PivotFields("Marks Total") and
2) pvt_tbl.PivotFields("Percentages Total")

In report I am getting in the same row that is H2 and H3
H2= "Sum of Marks Total" and
H3 = "Sum of Percentages Total"

But I need this field in the column wise i.e.
"Sum of Marks Total" in H Column
"Sum of Percentages Total" in I Column

*If u need I will attach excel file.

Please help me out

Sub pivot_Acc2Xl()
Dim dbs_nm, ssql As String
Dim dbs_conn As ADODB.Connection
Dim dbs_rs As ADODB.Recordset
Dim pvt_tbl As PivotTable
Dim pvt_che As PivotCache

dbs_nm = "C:\Documents and Settings\Chandru\Desktop\test.mdb"
Set dbs_conn = New ADODB.Connection
Set dbs_rs = New ADODB.Recordset
With dbs_conn
.Provider = "Microsoft.jet.oledb.4.0"
.Open dbs_nm
End With
Set dbs_rs.ActiveConnection = dbs_conn
ssql = "Select * from table1"
dbs_rs.Open ssql

Set pvt_che = ThisWorkbook.PivotCaches.Add(xlExternal)
Set pvt_che.Recordset = dbs_rs
Set pvt_tbl = pvt_che.CreatePivotTable(Worksheets(3).Range("A1"))

With pvt_tbl
.SmallGrid = False
.AddFields Array("Stud Nm", "Class", "Kannada", "English", "Maths", "Science", "Social")
End With

With pvt_tbl
.CalculatedFields.Add "Marks Total", "=sum(Kannada+English+Maths+Science+Social)"
.CalculatedFields.Add "Percentages Total", "=Marks Total /600*100"
End With

With pvt_tbl
.ColumnGrand = False
.RowGrand = False
.InGridDropZones = False
.TableStyle2 = "PivotStyleLight17"
.DisplayFieldCaptions = True
End With

With pvt_tbl.PivotFields("Marks Total")
.Orientation = xlDataField
.NumberFormat = "#,##0.00"
.LayoutForm = xlTabular
End With


With pvt_tbl.PivotFields("Percentages Total")
.Orientation = xlDataField
.NumberFormat = "#,##0.00"
.LayoutForm = xlTabular
End With


On Error Resume Next
For i = 2 To pvt_tbl.PivotFields.Count
a = pvt_tbl.PivotFields(i).Name
With pvt_tbl.PivotFields(a)
.LayoutForm = xlTabular
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
Next
On Error GoTo 0

With pvt_tbl
.PivotFields("Stud Nm").LayoutBlankLine = True
.NullString = "0"
.ShowDrillIndicators = False
.ColumnGrand = False
.RowGrand = False
.InGridDropZones = False
.PivotFields("Sum of Marks Total").Caption = " Marks Total"
.PivotFields("Sum of Percentages").Caption = " Percentage"
.TableStyle2 = "PivotStyleLight17"
.DisplayFieldCaptions = True
End With


With pvt_tbl
pitm_cnt = pvt_tbl.PivotFields("Stud Nm").PivotItems.Count
For j = 1 To pitm_cnt
pitem = pvt_tbl.PivotFields("Stud Nm").PivotItems(j)
For i = 1 To pitm_cnt
pvtitem = pvt_tbl.PivotFields("Stud Nm").PivotItems(i)
If pvtitem = pitem Then
pvt_tbl.PivotFields("Stud Nm").PivotItems(pvtitem).Visible = True
Else
pvt_tbl.PivotFields("Stud Nm").PivotItems(pvtitem).Visible = False
End If
Next
.TableRange1.Offset(1, 0).Copy
add_sht (pitem)
For Each pvtfld In pvt_tbl.PivotFields("Stud Nm").PivotItems
pvtfld.Visible = True
Next
i = i + 1

Next
End With


Set pvt_che = Nothing
Set pvt_tbl = Nothing
Set dbs_conn = Nothing
Set dbs_rs = Nothing
End Sub
Sub add_sht(ByRef pitem As String)
ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ThisWorkbook.Worksheets(Worksheets.Count).Name = pitem
With ThisWorkbook.Worksheets(pitem)

.Range("A3").PasteSpecial xlPasteValuesAndNumberFormats
.Range("A2:I2") = Array("Stud Nm", "Class", "Kannada", "English", "Maths", "Science", "Social", "Marks", "Percentange")
.Range("A1") = "Progess Report of " & pitem
.Range("A1:I1").Merge
.Range("A1:I1").HorizontalAlignment = xlCenter
.Range("A1:I1").Font.ColorIndex = 45
.Range("A1").Font.Size = 15
.Range("A1").Font.Bold = True
.Range("A2:I2").Font.Bold = True
.Range("A2:I2").Font.ColorIndex = 55
.Range("A1").Select
.Columns.AutoFit
End With

With Worksheets(3)
Application.CutCopyMode = False
End With
End Sub


Regards,

Chandra Shekar B

 

[Andrew Poulsom]

Is this what you want?

<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=505 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 width=64> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=56> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 width=51>Data</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=49> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=58> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=54> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=50> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=44> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl26 width=79> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=34>Stud Nm</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>Class</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl31 width=51>Sum of English</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=49>Sum of Maths</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=58>Sum of Kannada</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=54>Sum of Science</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=50>Sum of Social</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=44>Marks Total</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl33 width=79>Percentages Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>a</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>60</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>89</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>46</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>230</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.15323117921385743">15.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>b</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>53</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>25</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>61</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>37</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>33</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>209</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.13924050632911392">13.92%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>c</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>75</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>91</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>83</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>305</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.20319786808794138">20.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>d</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>58</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>94</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>245</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.16322451698867421">16.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>e</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>35</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>90</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>89</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>250</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.16655562958027981">16.66%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>f</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>94</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>37</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>14</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>38</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>79</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>262</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.17455029980013326">17.46%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl24 height=17 colSpan=2>Grand Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 align=right x:num>177</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>178</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>358</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>438</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>350</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>1501</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl30 align=right x:num="1">100.00%</TD></TR></TBODY></TABLE>

The Percentages Total field has the same formula as the Marks Total field, but the data is shown as % of column.

 

[chandrashekar]

Hi,

Thanks for your reply.

Exactly here I don't need Sum of but I need only subject name for ex. english etc.

Regards,

Chandra Shekar

 

[Andrew Poulsom]

I don't think it's possible to name a data field the same as the name in the source data.

 

[chandrashekar]

Hi,

Thank u for your suggestion I also tried but I could not able to do it.

1) How can I simplify this code?
2) I need to insert chart for each worksheet, how to do it?

Please help me out.

 

[Andrew Poulsom]

I'm not sure about simplifying the code, but did you try recording a macro while adding a pivot chart?