ahulme

Text Book Report Using BIRT in Learn

Blog Post created by ahulme on Apr 14, 2016

This idea for this report came from a question on this site: https://community.blackboard.com/message/2948#2948

 

 

The fastest way to create this report was via a BIRT B2.   This can be used as an example for building other reports.

 

Overview of Report:

 

System report containing all courses with text books added. Course ID, Course Name, Book Title, Author and ISBN are displayed This is an

example report and should be used be tested on a staging system before using on a live Environment. Tested Windows and Linux October 2015

release.

 

Reports as PDF

 

 

Full BIRT XML

 

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.21" id="1">
    <property name="createdBy">Eclipse BIRT Designer Version 4.4.2.v201410272105 Build &lt;4.4.2.v20150217-1805></property>
    <property name="units">in</property>
    <property name="iconFile">/templates/blank_report.gif</property>
    <property name="layoutPreference">auto layout</property>
    <property name="bidiLayoutOrientation">ltr</property>
    <property name="imageDPI">72</property>
    <list-property name="libraries">
        <structure>
            <property name="fileName">blackboard.common/resources/common.rptlibrary</property>
            <property name="namespace">common</property>
        </structure>
    </list-property>
    <data-sources>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="bb_common_data_source" id="995"
 extends="common.bb_common_data_source"/>
        <script-data-source name="scriptedDataSource" id="1582"/>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="BBLEARN_data_source" id="1587"
 extends="common.BBLEARN_data_source"/>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="ASR" id="1644">
            <list-property name="privateDriverProperties">
                <ex-property>
                    <name>metadataBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledMetadataBidiFormatStr</name>
                </ex-property>
                <ex-property>
                    <name>contentBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledContentBidiFormatStr</name>
                </ex-property>
            </list-property>
            <property name="odaDriverClass">oracle.jdbc.OracleDriver</property>
            <property name="odaURL">jdbc:oracle:thin:@ASRservername.blackboard.com:1521:ORACLE</property>
            <property name="odaUser">asr_user</property>
            <encrypted-property name="odaPassword" encryptionID="base64">YXNycGFzc3dvcmQ=</encrypted-property>
        </oda-data-source>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="ORACLE" id="1643">
            <list-property name="privateDriverProperties">
                <ex-property>
                    <name>metadataBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledMetadataBidiFormatStr</name>
                </ex-property>
                <ex-property>
                    <name>contentBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledContentBidiFormatStr</name>
                </ex-property>
            </list-property>
            <property name="odaDriverClass">oracle.jdbc.OracleDriver</property>
            <property name="odaURL">jdbc:oracle:thin:@Pd.pd.local:1521:ENG11R2</property>
            <property name="odaUser">BBLEARN</property>
            <encrypted-property name="odaPassword" encryptionID="base64">aEFsbDB3MzNuMQ==</encrypted-property>
        </oda-data-source>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="SQLServer" id="10">
            <list-property name="privateDriverProperties">
                <ex-property>
                    <name>metadataBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledMetadataBidiFormatStr</name>
                </ex-property>
                <ex-property>
                    <name>contentBidiFormatStr</name>
                    <value>ILYNN</value>
                </ex-property>
                <ex-property>
                    <name>disabledContentBidiFormatStr</name>
                </ex-property>
            </list-property>
            <property name="odaDriverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
            <property name="odaURL">jdbc:sqlserver://msserver:1433;databaseName=BBLEARN</property>
            <property name="odaUser">BBLEARN</property>
            <encrypted-property name="odaPassword" encryptionID="base64">YmxAY2tiMGFyZA==</encrypted-property>
        </oda-data-source>
    </data-sources>
    <data-sets>
        <oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="TextBooks" id="1698">
            <list-property name="columnHints">
                <structure>
                    <property name="columnName">COURSE_ID</property>
                    <property name="analysis">dimension</property>
                    <text-property name="displayName">COURSE_ID</text-property>
                    <text-property name="heading">COURSE_ID</text-property>
                </structure>
                <structure>
                    <property name="columnName">COURSE_NAME</property>
                    <property name="analysis">dimension</property>
                    <text-property name="displayName">COURSE_NAME</text-property>
                    <text-property name="heading">COURSE_NAME</text-property>
                </structure>
                <structure>
                    <property name="columnName">TITLE</property>
                    <property name="analysis">dimension</property>
                    <text-property name="displayName">TITLE</text-property>
                    <text-property name="heading">TITLE</text-property>
                </structure>
                <structure>
                    <property name="columnName">MAIN_DATA</property>
                    <property name="analysis">dimension</property>
                    <text-property name="displayName">MAIN_DATA</text-property>
                    <text-property name="heading">MAIN_DATA</text-property>
                </structure>
            </list-property>
            <list-property name="parameters"/>
            <structure name="cachedMetaData"/>
            <property name="dataSource">BBLEARN_data_source</property>
            <xml-property name="queryText"><![CDATA[SELECT CRS.COURSE_ID, CRS.COURSE_NAME, CC.TITLE, CC.MAIN_DATA

 FROM COURSE_MAIN CRS, COURSE_CONTENTS CC

 WHERE  (CNTHNDLR_HANDLE = 'resource/x-bb-textbook' OR 
 CNTHNDLR_HANDLE = 'resource/x-bb-textbook-manual')

 AND CC.CRSMAIN_PK1=CRS.PK1 
]]></xml-property>
            <xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<model:DesignValues xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
  <Version>2.0</Version>
  <design:ResultSets derivedMetaData="true">
    <design:resultSetDefinitions>
      <design:resultSetColumns>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>COURSE_ID</design:name>
              <design:position>1</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
            <design:precision>100</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>NotNullable</design:nullability>
            <design:uiHints>
              <design:displayName>COURSE_ID</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>COURSE_ID</design:label>
            <design:formattingHints>
              <design:displaySize>100</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>COURSE_NAME</design:name>
              <design:position>2</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
            <design:precision>333</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>NotNullable</design:nullability>
            <design:uiHints>
              <design:displayName>COURSE_NAME</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>COURSE_NAME</design:label>
            <design:formattingHints>
              <design:displaySize>333</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
        <design:resultColumnDefinitions>
          <design:attributes>
            <design:identifier>
              <design:name>TITLE</design:name>
              <design:position>3</design:position>
            </design:identifier>
            <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
            <design:precision>333</design:precision>
            <design:scale>0</design:scale>
            <design:nullability>Nullable</design:nullability>
            <design:uiHints>
              <design:displayName>TITLE</design:displayName>
            </design:uiHints>
          </design:attributes>
          <design:usageHints>
            <design:label>TITLE</design:label>
            <design:formattingHints>
              <design:displaySize>333</design:displaySize>
            </design:formattingHints>
          </design:usageHints>
        </design:resultColumnDefinitions>
      </design:resultSetColumns>
      <design:criteria/>
    </design:resultSetDefinitions>
  </design:ResultSets>
</model:DesignValues>]]></xml-property>
        </oda-data-set>
    </data-sets>
    <styles>
        <style name="report" id="4">
            <property name="fontFamily">sans-serif</property>
            <property name="fontSize">10pt</property>
        </style>
        <style name="crosstab-cell" id="5">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
        <style name="crosstab" id="6">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
    </styles>
    <page-setup>
        <simple-master-page name="Simple MasterPage" id="2">
            <property name="topMargin">1in</property>
            <property name="leftMargin">1.25in</property>
            <property name="bottomMargin">1in</property>
            <property name="rightMargin">1.25in</property>
        </simple-master-page>
    </page-setup>
    <body>
        <table id="1699">
            <property name="textAlign">left</property>
            <property name="dataSet">TextBooks</property>
            <list-property name="boundDataColumns">
                <structure>
                    <property name="name">COURSE_ID</property>
                    <text-property name="displayName">COURSE_ID</text-property>
                    <expression name="expression" type="javascript">dataSetRow["COURSE_ID"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">COURSE_NAME</property>
                    <text-property name="displayName">COURSE_NAME</text-property>
                    <expression name="expression" type="javascript">dataSetRow["COURSE_NAME"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">TITLE</property>
                    <text-property name="displayName">TITLE</text-property>
                    <expression name="expression" type="javascript">dataSetRow["TITLE"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">MAIN_DATA</property>
                    <text-property name="displayName">MAIN_DATA</text-property>
                    <expression name="expression" type="javascript">dataSetRow["MAIN_DATA"]</expression>
                    <property name="dataType">string</property>
                </structure>
                <structure>
                    <property name="name">ISBN</property>
                    <text-property name="displayName">ISBN</text-property>
                    <expression name="expression" type="javascript">var str = dataSetRow["MAIN_DATA"];&#13;
var n = str.indexOf("ISBN");&#13;
var SubISBN = str.substr(n,100);&#13;
var nsubstart = SubISBN.indexOf("td");&#13;
&#13;
//find end point. Minus the missing start chars and 4 for the letters ISBN&#13;
var nsubend = SubISBN.indexOf("/td")-(nsubstart+4);&#13;
&#13;
&#13;
&#13;
var result = SubISBN.substr(nsubstart+3,nsubend);&#13;
&#13;
result</expression>
                    <property name="dataType">string</property>
                    <property name="allowExport">true</property>
                </structure>
                <structure>
                    <property name="name">Column Binding</property>
                    <text-property name="displayName">Author</text-property>
                    <expression name="expression" type="javascript">var str = dataSetRow["MAIN_DATA"];&#13;
var n = str.indexOf("Author");&#13;
var SubISBN = str.substr(n,100);&#13;
var nsubstart = SubISBN.indexOf("td");&#13;
&#13;
&#13;
var nsubend = SubISBN.indexOf("/td")-(nsubstart+4);&#13;
&#13;
&#13;
&#13;
var result = SubISBN.substr(nsubstart+3,nsubend);&#13;
&#13;
result</expression>
                    <property name="dataType">string</property>
                    <property name="allowExport">true</property>
                </structure>
            </list-property>
            <column id="1723"/>
            <column id="1724"/>
            <column id="1725"/>
            <column id="1726"/>
            <column id="1730"/>
            <header>
                <row id="1700">
                    <cell id="1701">
                        <label id="1702">
                            <text-property name="text">Course ID    </text-property>
                        </label>
                    </cell>
                    <cell id="1703">
                        <label id="1704">
                            <text-property name="text">Course Name</text-property>
                        </label>
                    </cell>
                    <cell id="1705">
                        <label id="1706">
                            <text-property name="text">Title</text-property>
                        </label>
                    </cell>
                    <cell id="1707">
                        <label id="1738">
                            <text-property name="text">Author</text-property>
                        </label>
                    </cell>
                    <cell id="1727">
                        <label id="1736">
                            <text-property name="text">ISBN</text-property>
                        </label>
                    </cell>
                </row>
            </header>
            <detail>
                <row id="1709">
                    <cell id="1710">
                        <data id="1711">
                            <property name="resultSetColumn">COURSE_ID</property>
                        </data>
                    </cell>
                    <cell id="1712">
                        <data id="1713">
                            <property name="resultSetColumn">COURSE_NAME</property>
                        </data>
                    </cell>
                    <cell id="1714">
                        <data id="1715">
                            <property name="resultSetColumn">TITLE</property>
                        </data>
                    </cell>
                    <cell id="1716">
                        <data id="1737">
                            <property name="resultSetColumn">Column Binding</property>
                        </data>
                    </cell>
                    <cell id="1728">
                        <data id="1735">
                            <property name="resultSetColumn">ISBN</property>
                        </data>
                    </cell>
                </row>
            </detail>
            <footer>
                <row id="1718">
                    <cell id="1719"/>
                    <cell id="1720"/>
                    <cell id="1721"/>
                    <cell id="1722"/>
                    <cell id="1729"/>
                </row>
            </footer>
        </table>
    </body>
</report>

Attachments

Outcomes