Interactive Reports part 1

The reports in sqldev 1.1 change a lot. Among the changes are different types of reports, master details , context menus and drill downs. Alot of these features can be created/changed by the Create Report dialog but a number of them can not. I'll try and explain how to do things not in the dialog in the next few blogs working up to a interactive report which will show how to add these to your own reports.

In this post I'll talk about about the basic changes to the xml. In 1.0, the report looked like this:

<report type="report" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
    <query >
       <sql><![CDATA[select ......]]></sql>
  </query>
</report>

If you had some reports in 1.0 and have since start 1.1, you can look at the file in $HOME/.sqldeveloper/UserReports.xml and see the file was migrated. This same report in 1.1 will look like:

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>
</display>

While this mostly looks the same, the noticeable addition is the id, type, style attributes. The id attribute is what shows up in the LOV of the Advanced Tab of the dialog when the Add Report is pressed. The type attribute isn't used in this example yet. The style is what kind of report it is and the default styles are

• Table - normal grid of data
• Code - A big text field of the data concatenated together
• Chart - renders a chart from the data
• Script - runs the contents of the sql tag as a sql script
• plsql-dbms_output - This will grab the dbms_output from the plsql and render it as html
• More later on how to create your own

To make a master detail report all that's needed is to add another display tag inside the existing one. The binds for the child will be looked up into the row which is selected in the parent.

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>

  <!-- child -->
  <display id="" type="" style="Table" enable="true">
    <name><![CDATA[Details of stuff ]]></name>
    <description><![CDATA[my details ]]></description>
    <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
    <query>
      <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
    </query>
  </display>
</display>

Now I've shown nothing more than what the Report dialog can do to create a master/detail report. The first thing you can do which is not in the dialog is take these display tags and nest them as deep as you'd like. Such as this which adds a grandchild to this example:

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>

    <!--  child -->
    <display id="" type="" style="Table" enable="true">
       <name><![CDATA[Details of stuff ]]></name>
       <description><![CDATA[my details ]]></description>
       <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
       <query>
         <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
       </query>
      
        <!-- grandchild -->
        <display id="" type="" style="Table" enable="true">
           <name><![CDATA[Details of stuff ]]></name>
           <description><![CDATA[my details ]]></description>
           <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
           <query>
              <sql><![CDATA[select :PARENT_DATA parent_data from dual]]></sql>
           </query>
        </display>
    </display>
</display>