|
XML Source
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 <StockCode000100</StockCode <DescriptionItem A</Description <CostPrice100.00</CostPrice <SalePrice200.00</SalePrice <StockItem <StockCode000101</StockCode <DescriptionItem A: Subitem 1</Description <CostPrice10.00</CostPrice <SalePrice20.00</SalePrice </StockItem <StockItem <StockCode000102</StockCode <DescriptionItem A: Subitem 2</Description <CostPrice11.00</CostPrice <SalePrice22.00</SalePrice </StockItem <StockItem <StockCode000103</StockCode <DescriptionItem A: Subitem 3</Description <CostPrice8.00</CostPrice <SalePrice16.00</SalePrice </StockItem </StockItem <StockItem <StockCode000200</StockCode <DescriptionItem B</Description <CostPrice523.00</CostPrice <SalePrice1046.00</SalePrice <StockItem <StockCode000201</StockCode <DescriptionItem B: Subitem 1</Description <CostPrice52.00</CostPrice <SalePrice104.00</SalePrice </StockItem <StockItem <StockCode000202</StockCode <DescriptionItem B: Subitem 2</Description <CostPrice58.00</CostPrice <SalePrice116.00</SalePrice </StockItem <StockItem <StockCode000203</StockCode <DescriptionItem B: Subitem 3</Description <CostPrice18.00</CostPrice <SalePrice36.00</SalePrice </StockItem </StockItem <StockItem <StockCode000300</StockCode <DescriptionItem C</Description <CostPrice265.00</CostPrice <SalePrice530.00</SalePrice </StockItem <StockItem <StockCode000400</StockCode <DescriptionItem D</Description <CostPrice15.00</CostPrice <SalePrice30.00</SalePrice </StockItem </PriceList ================================= End of XML ================================= |
XML Source
why use xml? can't you just populate a recordset through an ADO connection?
you can then use the Range CopyFromRecordset method to load the sheet. "Terry Holland" wrote: 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 <StockCode000100</StockCode <DescriptionItem A</Description <CostPrice100.00</CostPrice <SalePrice200.00</SalePrice <StockItem <StockCode000101</StockCode <DescriptionItem A: Subitem 1</Description <CostPrice10.00</CostPrice <SalePrice20.00</SalePrice </StockItem <StockItem <StockCode000102</StockCode <DescriptionItem A: Subitem 2</Description <CostPrice11.00</CostPrice <SalePrice22.00</SalePrice </StockItem <StockItem <StockCode000103</StockCode <DescriptionItem A: Subitem 3</Description <CostPrice8.00</CostPrice <SalePrice16.00</SalePrice </StockItem </StockItem <StockItem <StockCode000200</StockCode <DescriptionItem B</Description <CostPrice523.00</CostPrice <SalePrice1046.00</SalePrice <StockItem <StockCode000201</StockCode <DescriptionItem B: Subitem 1</Description <CostPrice52.00</CostPrice <SalePrice104.00</SalePrice </StockItem <StockItem <StockCode000202</StockCode <DescriptionItem B: Subitem 2</Description <CostPrice58.00</CostPrice <SalePrice116.00</SalePrice </StockItem <StockItem <StockCode000203</StockCode <DescriptionItem B: Subitem 3</Description <CostPrice18.00</CostPrice <SalePrice36.00</SalePrice </StockItem </StockItem <StockItem <StockCode000300</StockCode <DescriptionItem C</Description <CostPrice265.00</CostPrice <SalePrice530.00</SalePrice </StockItem <StockItem <StockCode000400</StockCode <DescriptionItem D</Description <CostPrice15.00</CostPrice <SalePrice30.00</SalePrice </StockItem </PriceList ================================= End of XML ================================= |
XML Source
1) I want sub-items clearly identified by using a different formatting.
2) I use XML throughout the application as a means of transferring data. 3) I want to see what Excel 2003 can do with XML "Patrick Molloy" wrote in message ... why use xml? can't you just populate a recordset through an ADO connection? you can then use the Range CopyFromRecordset method to load the sheet. "Terry Holland" wrote: 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 <StockCode000100</StockCode <DescriptionItem A</Description <CostPrice100.00</CostPrice <SalePrice200.00</SalePrice <StockItem <StockCode000101</StockCode <DescriptionItem A: Subitem 1</Description <CostPrice10.00</CostPrice <SalePrice20.00</SalePrice </StockItem <StockItem <StockCode000102</StockCode <DescriptionItem A: Subitem 2</Description <CostPrice11.00</CostPrice <SalePrice22.00</SalePrice </StockItem <StockItem <StockCode000103</StockCode <DescriptionItem A: Subitem 3</Description <CostPrice8.00</CostPrice <SalePrice16.00</SalePrice </StockItem </StockItem <StockItem <StockCode000200</StockCode <DescriptionItem B</Description <CostPrice523.00</CostPrice <SalePrice1046.00</SalePrice <StockItem <StockCode000201</StockCode <DescriptionItem B: Subitem 1</Description <CostPrice52.00</CostPrice <SalePrice104.00</SalePrice </StockItem <StockItem <StockCode000202</StockCode <DescriptionItem B: Subitem 2</Description <CostPrice58.00</CostPrice <SalePrice116.00</SalePrice </StockItem <StockItem <StockCode000203</StockCode <DescriptionItem B: Subitem 3</Description <CostPrice18.00</CostPrice <SalePrice36.00</SalePrice </StockItem </StockItem <StockItem <StockCode000300</StockCode <DescriptionItem C</Description <CostPrice265.00</CostPrice <SalePrice530.00</SalePrice </StockItem <StockItem <StockCode000400</StockCode <DescriptionItem D</Description <CostPrice15.00</CostPrice <SalePrice30.00</SalePrice </StockItem </PriceList ================================= End of XML ================================= |
XML Source
Hi
So far I am researching the issue, and I will update you with new information ASAP. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
XML Source
Terry,
What about using a linked table in Access and using its report. You can export to HTML. NickHK "Terry Holland" wrote in message ... 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 <StockCode000100</StockCode <DescriptionItem A</Description <CostPrice100.00</CostPrice <SalePrice200.00</SalePrice <StockItem <StockCode000101</StockCode <DescriptionItem A: Subitem 1</Description <CostPrice10.00</CostPrice <SalePrice20.00</SalePrice </StockItem <StockItem <StockCode000102</StockCode <DescriptionItem A: Subitem 2</Description <CostPrice11.00</CostPrice <SalePrice22.00</SalePrice </StockItem <StockItem <StockCode000103</StockCode <DescriptionItem A: Subitem 3</Description <CostPrice8.00</CostPrice <SalePrice16.00</SalePrice </StockItem </StockItem <StockItem <StockCode000200</StockCode <DescriptionItem B</Description <CostPrice523.00</CostPrice <SalePrice1046.00</SalePrice <StockItem <StockCode000201</StockCode <DescriptionItem B: Subitem 1</Description <CostPrice52.00</CostPrice <SalePrice104.00</SalePrice </StockItem <StockItem <StockCode000202</StockCode <DescriptionItem B: Subitem 2</Description <CostPrice58.00</CostPrice <SalePrice116.00</SalePrice </StockItem <StockItem <StockCode000203</StockCode <DescriptionItem B: Subitem 3</Description <CostPrice18.00</CostPrice <SalePrice36.00</SalePrice </StockItem </StockItem <StockItem <StockCode000300</StockCode <DescriptionItem C</Description <CostPrice265.00</CostPrice <SalePrice530.00</SalePrice </StockItem <StockItem <StockCode000400</StockCode <DescriptionItem D</Description <CostPrice15.00</CostPrice <SalePrice30.00</SalePrice </StockItem </PriceList ================================= End of XML ================================= |
XML Source
So it looks like I will not easily be able to use Excel for my reporting
needs. I'm a little surprised as Im sure this is quite a common reporting scenario. Would it make any difference if by sub-item had a different element name ie <SubItem instead of <StockItem? ""Peter Huang" [MSFT]" wrote in message ... Hi Based on my research, the Excel sheet is an two dimension table. But your test xml file is of three dimension. e.g. To Excel, the data below have no difference. 000100 Item A ?00.00 ?00.00 000101 Item A: Subitem 1 4 ?0.00 ?0.00 Here is what we can done so far. 1. [Test.xsd, Schema File] <?xml version="1.0" encoding="utf-8" <xs:schema id="PriceList" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" <xs:element name="PriceList" <xs:complexType <xs:sequence <xs:element name="StockItem" <xs:complexType <xs:sequence <xs:element name="StockCode" type="xs:string" minOccurs="0" / <xs:element name="Description" type="xs:string" minOccurs="0" / <xs:element name="CostPrice" type="xs:string" minOccurs="0" / <xs:element name="SalePrice" type="xs:string" minOccurs="0" / </xs:sequence </xs:complexType </xs:element </xs:sequence </xs:complexType </xs:element </xs:schema 2. Attached xlt file which has been formatted, and it will format the data based on the column. 3. Double click the Test.xlt, and it will open a new workbook. import the data file(the content you have post in your first post) via the steps below. 3.1 select menu Data/XML/Import Hope this helps. BTW: even if we attach a schema conform to your orginal data file, Excel can only attach one of the root node in the schema as below. Because the scheme below is of three dimensions. <?xml version="1.0" encoding="utf-8"? <xs:schema id="PriceList" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" <xs:element name="StockItem" <xs:complexType <xs:sequence <xs:element name="StockCode" type="xs:string" minOccurs="0" / <xs:element name="Description" type="xs:string" minOccurs="0" / <xs:element name="CostPrice" type="xs:string" minOccurs="0" / <xs:element name="SalePrice" type="xs:string" minOccurs="0" / <xs:element ref="StockItem" minOccurs="0" maxOccurs="unbounded" / </xs:sequence </xs:complexType </xs:element <xs:element name="PriceList" msdata:IsDataSet="true" msdata:Locale="zh-CN" <xs:complexType <xs:choice maxOccurs="unbounded" <xs:element ref="StockItem" / </xs:choice </xs:complexType </xs:element </xs:schema Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
XML Source
Hi
As the schema, we can consider PriceList as a table, StockItem as a tablerow and StockCode... as a column. If you wants to add more nodes, e.g. <SubItem, I think you may try to add them a new column, so it will apprear as a new column in the excel table. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
XML Source
|
| All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com