Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Choosing Multiple Columns in a Column Selector – Varying Multiple Columns in a Report

Posted by Venkatakrishnan J on March 20, 2008

I received a very interesting question yesterday with regard to Column Selectors. As you would already be knowing, Column Selector allows the end users to vary the columns in a report. But there is no out of the box way to choose more than 1 column simultaneously. To be more precise, consider the report below.

      

This is a very simple report containing only a tabular view of the Sales in all the Regions. Now, if we have a column selector for the Region column we can have different reports by changing the columns in the Column selector. But the major drawback with this is that it gives us a report only with 2 columns. What if we need more than 1. Like say, i would like to have Region, District as an Option and then Region,District, Brand as another option in the column selector. Lets look at an approach today to achieve this. But remember this would work only for table views. Also, you cannot use the column headings directly. In order to have Column Headings you need to create a narrative view. Lets look at the steps one by one.

Lets start with creating the above report first. For the Region column, ensure that you have set Repeating row property. Also convert this column data format to HTML. And then remove the column headers.

      

      

      

Once this is done, create a simple column selector on the Region column. Add 2 more columns to this column selector. Then add this column selector to the compound layout. So your report should look like the one below.

      

Now, go the advanced tab and copy the XML to a Text Editor. Your XML would look something like the one shown below.

      

      <saw:report xmlns:saw=”com.siebel.analytics.web/report/v1″ xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:sawx=”com.siebel.analytics.web/expression/v1″ xmlVersion=”200705140″>
   <saw:criteria subjectArea=”Paint”>
      <saw:columns>
         <saw:column formula=”Markets.Region” columnID=”c0″>
            <saw:displayFormat suppress=”repeat” interaction=”default”>
               <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
            <saw:columnHeading>
               <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:column>
         <saw:column formula=”&quot;Sales Measures&quot;.Dollars” columnID=”c2″/></saw:columns></saw:criteria>
   <saw:views currentView=”0″>
      <saw:view xsi:type=”saw:compoundView” name=”compoundView!1″ rptViewVers=”200510010″>
         <saw:cvTable>
            <saw:cvRow>
               <saw:cvCell viewName=”titleView!1″>
                  <saw:displayFormat/></saw:cvCell></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName=”columnSelectorView!1″/></saw:cvRow>
            <saw:cvRow>
               <saw:cvCell viewName=”tableView!1″>
                  <saw:displayFormat/></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
      <saw:view xsi:type=”saw:titleView” name=”titleView!1″ rptViewVers=”200510010″/>
      <saw:view xsi:type=”saw:tableView” name=”tableView!1″ rptViewVers=”200510010″ showHeading=”false” deck=”bottom” headingDisplay=”none” rowsPerPage=”100″>
         <saw:displayFormat/></saw:view>
      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.District”/>
            <saw:choice type=”column” formula=”Products.Brand”/>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

In the above XML, the part that is of our concern is the column selector view part. Now, our aim is to display multiple columns simultaneously by clicking on one column selector value. In order to do that, we shall be using a column formula as shown below

      Markets.Region||'<td>’||Markets.District||'</td>’

What this would do is that this will display both the columns in the same row if we treat the above formula as HTML. So, basically in our column selector part of the XML use the below XML

      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.Region||’&lt;td&gt;’||Markets.District||’&lt;/td&gt;'”>
            <saw:displayFormat suppress=”repeat” interaction=”default”>
                       <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading><saw:caption>       
                     <saw:text>Regions and Districts</saw:text></saw:caption></saw:columnHeading></saw:choice>
            <saw:choice type=”column” formula=”Markets.Region||’&lt;td&gt;’||Markets.District||’&lt;/td&gt;’||’&lt;td&gt;’||Products.Brand||’&lt;/td&gt;'”>
                 <saw:displayFormat suppress=”repeat” interaction=”default”>
                            <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
                    <saw:columnHeading><saw:caption>       
                     <saw:text>Regions , Districts and Brand</saw:text></saw:caption></saw:columnHeading></saw:choice>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

instead of the one shown below

      <saw:view xsi:type=”saw:columnSelectorView” name=”columnSelectorView!1″ rptViewVers=”200510010″ labelPosition=”left” goButton=”false”>
         <saw:selector columnID=”c0″ bPrompt=”true”>
            <saw:choice type=”column” formula=”Markets.District”/>
            <saw:choice type=”column” formula=”Products.Brand”/>
            <saw:choice type=”column” formula=”Markets.Region”>
               <saw:displayFormat suppress=”repeat” interaction=”default”>
                  <saw:dataFormat xsi:type=”saw:text” textFormat=”html”/></saw:displayFormat>
               <saw:columnHeading>
                  <saw:displayFormat interaction=”default”/></saw:columnHeading></saw:choice></saw:selector></saw:view></saw:views></saw:report>

Now, if you go back and look at your report you should now be able to select more than one column in your column selector.

      

      

Advertisements

13 Responses to “Oracle BI EE 10.1.3.3/2 – Choosing Multiple Columns in a Column Selector – Varying Multiple Columns in a Report”

  1. Gaurav Nankar said

    Hi Venkat,

    Very nice blog.

    I did not understand why the reporting row property should be used. Wouldn’t it have worked without it?

    Thanks,
    Gaurav

  2. Gaurav Nankar said

    repeating row property

  3. John said

    Great site and useful content! Could you leave some opinion about my sites?
    My pages
    [url=http://ownsite.com/b/]My pages[/url]
    http://ownsite.com/p/ My pages

  4. Sanjeet said

    Hi all,

    I followed the above steps, but was unable to set xml.
    Its giving an error “Error parsing XML”.

    Thanks,
    Sanjeet

  5. Alvin said

    I’ve tried this method and it worked great for text fields. Now what if it is a measure?

    i.e. I have (MTD & LYMTD), (QTD & LYQTD), and (YTD & LYYTD), what if I want to toggle between the three listed using column selector? Thanks in advance.

    Regards,
    Alvin

  6. Paul Pelletier said

    I would like to better understand all the possibilities that the “Advance Tab” offers. Is there a place where I could find more info about it and/or perhaps an xsd I could use ?

    Thanks
    Paul

  7. santhosh said

    I’ve Tried this and it works out good for table view. but when i tried implementing the same thing for a pivot table, the result displayed with no lines after each column. i.e it doesnt displays in tabular form. any suggestions please??

    Thanks.
    – Santhosh

  8. Randy said

    Sure seems like a lot of trouble….why not just create multiple versions of a pivot table and have a view selector instead?

  9. Pronab Das said

    Hi Venkat,

    Its reallly a nice blog.
    But you have said that ‘In order to have Column Headings you need to create a narrative view’.

    Can you please explain how can we achieve this?
    We have a requirement like this.But we need to show the column headings also.

    It will be great if you can help on that note.

    Thanks & Regards,
    Pronab

  10. CHANDRA said

    HiVenkat,

    I followed the above steps ,I am not able to set HTML ,

  11. CHANDRA said

    Hi Venkat,

    I followed the above steps,Fine it is working .But i Have one query,I tried with the above one for Multiple Dimensions and multiple Columns.

    Thanks

    chnadra

  12. Milton said

    Hi, the solution is not working if we have say two columns with summations, since the formula treats both columns in the same row. I wonder if there is a way to apply this solusion to columns with summations.

    Thanks

  13. Lex said

    Does anyone know if there is a way to make the column selector dropdown a multi select? Goal is to have dynamic number of columns presented on a pivot table including measures. Appreciate any help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: