How to tell the book what data to use

This tutorial will talk you through the following:

  • Adding crosswalks to the book
  • Adding XML financial data to the book
  • Data consolidation process
  • Consolidated data format

Let's get started

We will use the same Exhibit Generation Crosswalk tutorial as in previous part.

Adding crosswalks to the book

To specify what data should be used in the budget book, just add <DataFiles> tag to the book file and specify the data files. All exhibits in this book will use specified data. This gives you greater flexibility, using the same exhibit transformation scripts with different data across multiple books.

In our example, we use one crosswalk file, but besides Excel crosswalks you can specify any XML file. That allows you to use the data extracts from the financial data source of your choosing.

<Book Name="Exhibit Generation Quick Steps Tutorial"
      Value="" Version="1.0" StyleTemplate="">
<DataFiles>
    <File Path="Data\FundsCrosswalk.xlsx"/>
    <File Path="sql_server_database_extract.xml"/>
    <File Path="oracle_database_extract.xml"/>
    <File Path="hyperion_performance_data.xml"/>
    <File Path="oracle_financials_xml_extract.xml"/>
    <File Path="sap_financial_report.xml"/>
    <File Path="e-gov_data.xml"/>
    <File Path="MUNIS-financial_extract.xml"/>
</DataFiles>
...
</Book>

 

Data consolidation process Excel compatible crosswalks

The data consolidation process for excel compatible crosswalks, will extract named range values row by row for every worksheet, and convert each worksheet in to its own data table. All the formulas, in the excel file, will be calculated and values saved in the consolidated data file, so you can use the values. Budget In Brief supports very sophisticated formulas to accommodate your financial calculation needs.



Figure 1


When all crosswalks are processed they are converted in to the following format:

<Data>
    <Table Name="Worksheet Name"
           DataFile="Spreadsheet Name"
           Location="Spreadsheet location">
       <Row>
            <NamRangeA>Value</NamRangeA>
            <NamRangeB>Value</NamRangeB>
            <NamRangeC>Value</NamRangeC>
       </Row>
       <Row>
            <NamRangeA>Value</NamRangeA>
            <NamRangeB>Value</NamRangeB>
            <NamRangeZ>Value</NamRangeZ>
       </Row>
    </Table>
    <Table Name="Another Worksheet Name"
           DataFile="Another Spreadsheet Name"
           Location="Spreadsheet location">
       <Row>
           <NamRangeX>Value</NamRangeX>
           <NamRangeY>Value</NamRangeY>
       </Row>
    </Table>
</Data>

Each worksheet becomes its separate table in the XML document, with following table attributes:

  • Name - is the worksheet name in the spreadsheet;
  • DataFile - is the crosswalk file name;
  • Location - is the folder where crosswalk is located.

When you run the Budget In Brief application, for example, in the book “Exhibit Generation.book” you will find “ConsolidatedBookData.xml” file, located in the Temp folder. File cached data for “FundsCrosswalk.xlsx” supplied crosswalk file and will look similar to the following:

<Data>
    <Table Name="Sheet1" DataFile="FundsCrosswalk.xlsx" Location="Data" >
    . . .
    <Row>
        <Fund>General Fund</Fund>
        <ReserveClass>Statutory Reserves</ReserveClass>
        <Reserve>Cemetery Care Reserve</Reserve>
        <PYBalance>489422</PYBalance>
        <CYAdditions>7500</CYAdditions>
        <CYBalance>496922</CYBalance>
        <BYAdditions>7500</BYAdditions>
        <BYBalance>504422</BYBalance>
        <BBYAdditions>7500</BBYAdditions>
        <BBYBalance>511922</BBYBalance>
        <BBBYAdditions>7500</BBBYAdditions>
        <BBBYBalance>519422</BBBYBalance>
        <BBBBYAdditions>7500</BBBBYAdditions>
        <BBBBYBalance>526922</BBBBYBalance>
    </Row>
    <Row>
        <Fund>General Fund</Fund>
        <ReserveClass>Statutory Reserves</ReserveClass>
        <Reserve>Community Works Reserve</Reserve>
        <PYBalance>6756910</PYBalance>
        <CYAdditions>2344246</CYAdditions>
        <CYBalance>9101156</CYBalance>
        <BYAdditions>2503800</BYAdditions>
        <BYBalance>11604956</BYBalance>
        <BBYAdditions>2530753</BBYAdditions>
        <BBYWithdrawals>3210750</BBYWithdrawals>
        <BBYBalance>10924959</BBYBalance>
        <BBBYAdditions>2359603</BBBYAdditions>
        <BBBYWithdrawals>13260750</BBBYWithdrawals>
        <BBBYBalance>23812</BBBYBalance>
        <BBBBYAdditions>2231480</BBBBYAdditions>
        <BBBBYBalance>2255292</BBBBYBalance>
    </Row>
    . . .
    </Table>
</Data>

 

Data consolidation process for XML files

The data consolidation process for XML data files, works in similar way to the spreadsheet consolidation and will load all data files into their separate data tables with exceptions that:

  • process will load all supplied data as provided;
  • no formula calculation will be done for XML data;



Figure 2

The only requirement we have for the XML data files is the singular root element.

All XML files will be appended into the consolidated data file, which can be found in the TEMP folder.

Each XML file will become a separate Table in the consolidated XML document, with following attributes:

  • Name - is the name of the root element in XML file;
  • DataFile - is the XML file name;
  • Location - is the folder where XML file is located.

Start the Budget In Brief application, open “Exhibit Generation - XLSX and XML Data Files.book” book. This book includes “ObjectClassExtractFromDatabase.xml” data file with the following XML:

<ObjectClassTable>
    <ObjectClass>
        <Name>Object Class A</Name>
        <Id>1</Id>
        <Abreviation>OCA</Abreviation>
    </ObjectClass>
    <ObjectClass>
        <Name>Object Class B</Name>
        <Id>2</Id>
        <Abreviation>OCB</Abreviation>
    </ObjectClass>
    <ObjectClass>
        <Name>Object Class C</Name>
        <Id>3</Id>
        <Abreviation>OCC</Abreviation>
    </ObjectClass>
</ObjectClassTable>


Click the “Create Document” button and check the consolidated data file, “ConsolidatedBookData.xml” located in the Temp folder. Besides crosswalk data, the consolidated file will include XML data in following format:

<Data>
    . . .
    <Table Name="ObjectClassTable"
           DataFile="ObjectClassExtractFromDatabase.xml"
           Location="E:\Crosswalk\Data">
        <ObjectClass>
             <Name>Object Class A</Name>
             <Id>1</Id>
             <Abreviation>OCA</Abreviation>
        </ObjectClass>
        <ObjectClass>
             <Name>Object Class B</Name>
             <Id>2</Id>
             <Abreviation>OCB</Abreviation>
        </ObjectClass>
        <ObjectClass>
             <Name>Object Class C</Name>
             <Id>3</Id>
             <Abreviation>OCC</Abreviation>
        </ObjectClass>
     </Table>
     . . .
</Data>

 

Conclusion

Add name ranges to your crosswalks, include crosswalks in the book file, include the existing XML files in the book, and you are done with data configuration.
 


Site Map | Printable View | © 2008 - 2017 Budget In Brief | Powered by mojoPortal | HTML 5 | CSS | Design by styleshout