2 Replies Latest reply: Nov 5, 2013 7:42 AM by Swapnil K RSS

    How to create a dynamic RTF report which creates dynamic columns based on dynamic column selection from a table?

    Swapnil K

      Hi All,

       

      Suppose I have table, whose structure changes frequently on daily basis.

      For eg. desc my_table gives you following column name on Day 1

      SQL > desc my_table;

      Output

      Name

      Age

      Phone


      On Day 2, two more columns are added, viz, Address and Salary.

      SQL > desc my_table;

      Output

      Name

      Age

      Phone

      Address

      Salary


      Now I want to create an Dynnamic RTF report which would fetch data from ALL columns from my_table on daily basis. For that I have defined a concurrent program with XML as output type and have attached a data template/data definition to it which takes in XML as input and gives final output of conc program in EXCEL layout. I am able to do this for constant number of columns, but dont know how to do it when the number of columns to be displayed changes dynamically.

       

      For Day 1 my XML file should be like this.

      <?xml version="1.0" encoding="UTF-8"?>
      <dataTemplate name="XYZ" description="iExpenses Report" Version="1.0">
      <dataQuery>
      <sqlStatement name="Q2">
      <![CDATA[
      SELECT Name
      ,Age
      ,Phone
      FROM my_table
      ]]>
      </sqlStatement>
      </dataQuery>
      <dataStructure>
      <group name="G_my_table" source="Q2">
        <element name="Name" value="Name" />
        <element name="Age" value="Age" />
        <element name="Phone" value="Phone" />
      </group>
      </dataStructure>
      </dataTemplate>
      
      

       

      And my Day 1, EXCEL output from RTF template should be like this.
      Name     Age     Phone

      Swapnill     23     12345

       

      For Day 2 my XML file should be like this. With 2 new columns selected in SELECT clause.

      <?xml version="1.0" encoding="UTF-8"?>
      <dataTemplate name="XYZ" description="iExpenses Report" Version="1.0">
      <dataQuery>
      <sqlStatement name="Q2">
      <![CDATA[
      SELECT Name
      ,Age
      ,Phone
      ,Address
      ,Salary
      FROM my_table
      ]]>
      </sqlStatement>
      </dataQuery>
      <dataStructure>
      <group name="G_my_table" source="Q2">
        <element name="Name" value="Name" />
        <element name="Age" value="Age" />
        <element name="Phone" value="Phone" />
        <element name="Address" value="Address" />
        <element name="Salary" value="Salary" />
      </group>
      </dataStructure>
      </dataTemplate>
      
      

       

      And my Day 2, EXCEL output from RTF template should be like this.
      Name     Age     Phone     Address     Salary

      Swapnill     23     12345         Madrid     100000

       

      Now, I dont know below things.

      • Make the XML dynamic as in on Day 1 there must be 3 columns in the SELECT statement and on Day 2, 5 columns. I want to create one dynamic XML which should not be required to be changed if new columns are added in my_table. I dont know how to create this query and also create their corresponding elements below.
      • Make the RTF template dyanamic as in Day1 there must 3 columns in EXCEL output and on Day 2, 5 columns. I want to create a Dynamic RTF template which would show all the columns selected in Dynamic XML.I dont know how the RTF will create new XML tags and how it will know where to place it in the report. Means, I can create RTF template on Day 1, by loading XML data for 3 columns and placing 3 XML tags in template. But how will it create and place tags for new columns on Day 2?

       

      Hope, you got my requirement, its a challenging one. Please let me know how I can implement the required solution using RTF dynamically without any manual intervention.

       

      Regards,

      Swapnil K.

       

      Message was edited by: SwapnilK