290 likes | 440 Views
DEV09:. Interactive Spreadsheets. Formats and Working Designs. Date/Time: Wednesday, December 6 from 10:00 to 10:30 am Session Leader: Bill Palmer, R W Palmer Consulting. 14th International XBRL Conference. Interactive Spreadsheets. Formats and Working Designs.
E N D
DEV09: Interactive Spreadsheets Formats and Working Designs Date/Time: Wednesday, December 6 from10:00 to 10:30 am Session Leader: Bill Palmer, R W Palmer Consulting 14th InternationalXBRL Conference
Interactive Spreadsheets Formats and Working Designs 14th InternationalXBRL Conference
Barriers and Solutions 14th InternationalXBRL Conference
XBRL files difficulties using common Excel XML import • schema and linkbases files • Default loading of XML works using File-Open • Read only - saved changes not original XML • XML Data features of Excel 2003 fail for schemas • XSD substitutionGroups not allowed ! • instance files • Default loading of XML using File-Open fails ! • XBRL tags mapped as columns - max 255 ! • Applies XSLT 1.0 from <?xsl ?> embedded processing instructions on load. 14th InternationalXBRL Conference
Loading xml esp. XBRL instances 14th InternationalXBRL Conference
...XSLT can column-ize and clean up Transform elements with a @contextRef as generic element with an attribute holding element names. Element names will then all be in the new attribute column. 14th InternationalXBRL Conference
Taxonomy processing ? • DTS “discovery” • extension logic and use=“prohibit” 14th InternationalXBRL Conference
…do DTS before Excel load ...do extension logic by “lookup” A simple <files><file></file> ...</files> is sufficient for all XSLT 1.0 extension processing. A cell formula may look or a matching arc that is flagged as “prohibited” 14th InternationalXBRL Conference
Spreadsheet showing tree view of linkbase is too complex 14th InternationalXBRL Conference
…do not use tree ...use list with links 14th InternationalXBRL Conference
… xlink:from=“GrossProfit” ... … xlink:from=“GrossProfit” ... … xlink:to=“Revenue” weight=“+1” ... … xlink:to=“CostOfSales” weight=“-1” ... Same as linkbase … work with a set of arcs for a particular FROM summation in a particular ROLE 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Arc? summation item weight role 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation item weight Arc? summation item weight role 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation item weight role summation weight item 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation weight item A larger example in Excel - including arcs for the px_CostOfSales summation. 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? • This example is greatly simplified by ignoring other attributes • such as use, order, priority, xlink:arcrole, xlink:href etc. • We will mark use=“prohibited” arcs with a “P” so that extension processing formulas can be used. The amount • columns will need to be blanked out for prohibited arcs and the arcs that they match. 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Calculated Control More rows added by text formulas. 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Note use of calculation number 10003 on prohibited extension arc. This allows unique VLOOKUP without listing long combined FROM and ROLE names. “P” as first character means “prohibited”. Previous “W” arc also via “lookup”. 14th InternationalXBRL Conference
Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Another format example spreadsheet. Note numeric column headings … “r1c1” mode allows identical standard formulas on any row. 14th InternationalXBRL Conference
Summation section is arcs above a calculated total, a control total, the difference. MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference
Sheet is “summation” section above “leaf” values for lookup MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference
MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference
SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference
SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference
MANY “leaf” concepts 14th InternationalXBRL Conference
Full featured example in PDF with annotations The spreadsheet is created by a non-xslt processor that collects all arcs for the entire taxonomy and is able to discern which are LEAF nodes and to document where they are used. The program writes all formulas and need not understand “row” addresses by using the “r1c1” mode. A single program step in Excel activates the formulas. 14th InternationalXBRL Conference
14th InternationalXBRL Conference See file xmsft-20050228_calSPREADSHEET.pdf