I have data in a pivot table that changes when I change the report filter. The format that I want to preserve is this:
<table border="0" cellpadding="0" cellspacing="0" width="316"><colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;width:45pt; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid #D8D8D8; border-right:none;border-bottom:.5pt solid #D8D8D8;border-left:none; background:yellow;mso-pattern:black none" height="17" width="60">(All)</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">QUOTES</td> <td>QUOTES</td> <td>ORDERS</td> <td>ORDERS</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right" height="17">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right">2012</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid gray;border-left: none;background:gray;mso-pattern:gray none">Grand Total</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" height="17">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2012</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid gray" align="right" height="18">23</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: .5pt solid gray;border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:.5pt solid gray" align="right">8</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: .5pt solid gray;border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:.5pt solid gray" align="right">31</td> </tr> <tr style="mso-height-source:userset;height:71.25pt" height="95"> <td style="height:71.25pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid gray; border-bottom:.5pt solid #D8D8D8;border-left:.5pt solid gray" align="right" height="95">6</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:.5pt solid gray;border-bottom: .5pt solid #D8D8D8;border-left:.5pt solid gray" align="right">20</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none" align="right">2</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:.5pt solid gray;border-bottom: .5pt solid #D8D8D8;border-left:.5pt solid gray" align="right">28</td> </tr> </tbody></table>
The four columns I want to always be there, even if there is no data in those columns. How do I make sure the pivot table preserve's these columns so when I change the report filter it doesn't look like this:
<table border="0" cellpadding="0" cellspacing="0" width="188"><colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" colspan="3" style="height:12.75pt; mso-ignore:colspan;width:141pt;font-size:10.0pt;color:windowtext;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #D8D8D8;border-right:none;border-bottom: .5pt solid #D8D8D8;border-left:none;background:black;mso-pattern:black none" height="17" width="188">BRAZORIA VALVE AND FITTING CO</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">QUOTES</td> <td>ORDERS</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right" height="17">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid gray;border-left: none;background:gray;mso-pattern:gray none">Grand Total</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" height="17">
</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:.5pt solid gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right" height="18">1</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: none;border-left:none" align="right">1</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right">2</td> </tr> <tr style="mso-height-source:userset;height:71.25pt" height="95"> <td style="height:71.25pt;font-size:10.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:2.0pt double gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right" height="95">1</td> <td style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:2.0pt double gray;border-right:none;border-bottom: none;border-left:none" align="right">1</td> <td style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:2.0pt double gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right">2</td> </tr> </tbody></table>
Thanks,
J
<table border="0" cellpadding="0" cellspacing="0" width="316"><colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;width:45pt; font-size:10.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid #D8D8D8; border-right:none;border-bottom:.5pt solid #D8D8D8;border-left:none; background:yellow;mso-pattern:black none" height="17" width="60">(All)</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">QUOTES</td> <td>QUOTES</td> <td>ORDERS</td> <td>ORDERS</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right" height="17">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right">2012</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid gray;border-left: none;background:gray;mso-pattern:gray none">Grand Total</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" height="17">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2012</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border:.5pt solid gray" align="right" height="18">23</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: .5pt solid gray;border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:.5pt solid gray" align="right">8</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: .5pt solid gray;border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:.5pt solid gray" align="right">31</td> </tr> <tr style="mso-height-source:userset;height:71.25pt" height="95"> <td style="height:71.25pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:.5pt solid gray; border-bottom:.5pt solid #D8D8D8;border-left:.5pt solid gray" align="right" height="95">6</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none" align="right">0</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:.5pt solid gray;border-bottom: .5pt solid #D8D8D8;border-left:.5pt solid gray" align="right">20</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none" align="right">2</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:none;border-right:.5pt solid gray;border-bottom: .5pt solid #D8D8D8;border-left:.5pt solid gray" align="right">28</td> </tr> </tbody></table>
The four columns I want to always be there, even if there is no data in those columns. How do I make sure the pivot table preserve's these columns so when I change the report filter it doesn't look like this:
<table border="0" cellpadding="0" cellspacing="0" width="188"><colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" colspan="3" style="height:12.75pt; mso-ignore:colspan;width:141pt;font-size:10.0pt;color:windowtext;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid #D8D8D8;border-right:none;border-bottom: .5pt solid #D8D8D8;border-left:none;background:black;mso-pattern:black none" height="17" width="188">BRAZORIA VALVE AND FITTING CO</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">QUOTES</td> <td>ORDERS</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none" align="right" height="17">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid #D8D8D8; border-left:none;background:gray;mso-pattern:gray none">
</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border-top:none;border-right:none;border-bottom:.5pt solid gray;border-left: none;background:gray;mso-pattern:gray none">Grand Total</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt;font-size:10.0pt;color:white;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" height="17">
</td> <td style="font-size:10.0pt;color:white;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border:none;background:gray;mso-pattern:gray none" align="right">2011</td> <td style="font-size:10.0pt;color:white;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:none;background:gray;mso-pattern:gray none">
</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:.5pt solid gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right" height="18">1</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:none;border-bottom: none;border-left:none" align="right">1</td> <td style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:.5pt solid gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right">2</td> </tr> <tr style="mso-height-source:userset;height:71.25pt" height="95"> <td style="height:71.25pt;font-size:10.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:2.0pt double gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right" height="95">1</td> <td style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:2.0pt double gray;border-right:none;border-bottom: none;border-left:none" align="right">1</td> <td style="font-size:10.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Arial;border-top:2.0pt double gray;border-right:.5pt solid gray; border-bottom:none;border-left:.5pt solid gray" align="right">2</td> </tr> </tbody></table>
Thanks,
J
