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=”"Sales Measures".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||’<td>’||Markets.District||’</td>’”>
<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||’<td>’||Markets.District||’</td>’||’<td>’||Products.Brand||’</td>’”>
<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.
Posts
