T
Terry Holland
Im not sure if this is the correct group for this post so please advise of
any other managed newsgroup that would be more appropriate.
I am looking for a method of producing reports of data from a SQL Server
database. The database holds stock information. I am considering HTML,
Crystal and Excel as my reporting tool. Im not very familiar with Excel's
2003 XML capabilities so I'd like someone to advise me whether I can do what
I want to do. Im also not very experienced with XML.
Many of our stock items are assemblies of other stock items and some are
standalone stock items. I have included a sample of XML that could be
produced.
What Id like to know is whether it's possible to set up a template in Excel
that I could use to open my XML export and have it display the top-level
stock items differently to the assmebly sub-items. ie Have the top-level
stock items dispayed with a light blue background and have the text bold,
and have any sub-items of that top level stock item displayed underneath it
with yellow background and italicised text.
Is this something that could easily be set up using Excel 2003
Regards
Terry Holland
=================================
Example of Excel Display
=================================
Stock Code Description Quantity Cost Price Sale Price
000100 Item A £100.00 £200.00
000101 Item A: Subitem 1 4 £10.00 £20.00
000102 Item A: Subitem 2 2 £11.00 £22.00
000103 Item A: Subitem 3 3 £8.00 £16.00
000200 Item B £523.00 £1,046.00
000201 Item B: Subitem 1 2 £52.00 £104.00
000202 Item B: Subitem 2 4 £58.00 £116.00
000203 Item B: Subitem 3 4 £18.00 £36.00
000300 Item C £265.00 £530.00
000400 Item D £15.00 £30.00
=================================
End of Example of Excel Display
=================================
=================================
XML
=================================
<?xml version="1.0" encoding="utf-8" ?>
<PriceList>
<StockItem>
<StockCode>000100</StockCode>
<Description>Item A</Description>
<CostPrice>100.00</CostPrice>
<SalePrice>200.00</SalePrice>
<StockItem>
<StockCode>000101</StockCode>
<Description>Item A: Subitem 1</Description>
<CostPrice>10.00</CostPrice>
<SalePrice>20.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000102</StockCode>
<Description>Item A: Subitem 2</Description>
<CostPrice>11.00</CostPrice>
<SalePrice>22.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000103</StockCode>
<Description>Item A: Subitem 3</Description>
<CostPrice>8.00</CostPrice>
<SalePrice>16.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000200</StockCode>
<Description>Item B</Description>
<CostPrice>523.00</CostPrice>
<SalePrice>1046.00</SalePrice>
<StockItem>
<StockCode>000201</StockCode>
<Description>Item B: Subitem 1</Description>
<CostPrice>52.00</CostPrice>
<SalePrice>104.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000202</StockCode>
<Description>Item B: Subitem 2</Description>
<CostPrice>58.00</CostPrice>
<SalePrice>116.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000203</StockCode>
<Description>Item B: Subitem 3</Description>
<CostPrice>18.00</CostPrice>
<SalePrice>36.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000300</StockCode>
<Description>Item C</Description>
<CostPrice>265.00</CostPrice>
<SalePrice>530.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000400</StockCode>
<Description>Item D</Description>
<CostPrice>15.00</CostPrice>
<SalePrice>30.00</SalePrice>
</StockItem>
</PriceList>
=================================
End of XML
=================================
any other managed newsgroup that would be more appropriate.
I am looking for a method of producing reports of data from a SQL Server
database. The database holds stock information. I am considering HTML,
Crystal and Excel as my reporting tool. Im not very familiar with Excel's
2003 XML capabilities so I'd like someone to advise me whether I can do what
I want to do. Im also not very experienced with XML.
Many of our stock items are assemblies of other stock items and some are
standalone stock items. I have included a sample of XML that could be
produced.
What Id like to know is whether it's possible to set up a template in Excel
that I could use to open my XML export and have it display the top-level
stock items differently to the assmebly sub-items. ie Have the top-level
stock items dispayed with a light blue background and have the text bold,
and have any sub-items of that top level stock item displayed underneath it
with yellow background and italicised text.
Is this something that could easily be set up using Excel 2003
Regards
Terry Holland
=================================
Example of Excel Display
=================================
Stock Code Description Quantity Cost Price Sale Price
000100 Item A £100.00 £200.00
000101 Item A: Subitem 1 4 £10.00 £20.00
000102 Item A: Subitem 2 2 £11.00 £22.00
000103 Item A: Subitem 3 3 £8.00 £16.00
000200 Item B £523.00 £1,046.00
000201 Item B: Subitem 1 2 £52.00 £104.00
000202 Item B: Subitem 2 4 £58.00 £116.00
000203 Item B: Subitem 3 4 £18.00 £36.00
000300 Item C £265.00 £530.00
000400 Item D £15.00 £30.00
=================================
End of Example of Excel Display
=================================
=================================
XML
=================================
<?xml version="1.0" encoding="utf-8" ?>
<PriceList>
<StockItem>
<StockCode>000100</StockCode>
<Description>Item A</Description>
<CostPrice>100.00</CostPrice>
<SalePrice>200.00</SalePrice>
<StockItem>
<StockCode>000101</StockCode>
<Description>Item A: Subitem 1</Description>
<CostPrice>10.00</CostPrice>
<SalePrice>20.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000102</StockCode>
<Description>Item A: Subitem 2</Description>
<CostPrice>11.00</CostPrice>
<SalePrice>22.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000103</StockCode>
<Description>Item A: Subitem 3</Description>
<CostPrice>8.00</CostPrice>
<SalePrice>16.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000200</StockCode>
<Description>Item B</Description>
<CostPrice>523.00</CostPrice>
<SalePrice>1046.00</SalePrice>
<StockItem>
<StockCode>000201</StockCode>
<Description>Item B: Subitem 1</Description>
<CostPrice>52.00</CostPrice>
<SalePrice>104.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000202</StockCode>
<Description>Item B: Subitem 2</Description>
<CostPrice>58.00</CostPrice>
<SalePrice>116.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000203</StockCode>
<Description>Item B: Subitem 3</Description>
<CostPrice>18.00</CostPrice>
<SalePrice>36.00</SalePrice>
</StockItem>
</StockItem>
<StockItem>
<StockCode>000300</StockCode>
<Description>Item C</Description>
<CostPrice>265.00</CostPrice>
<SalePrice>530.00</SalePrice>
</StockItem>
<StockItem>
<StockCode>000400</StockCode>
<Description>Item D</Description>
<CostPrice>15.00</CostPrice>
<SalePrice>30.00</SalePrice>
</StockItem>
</PriceList>
=================================
End of XML
=================================