Tuesday Feb 11, 2014

Filtered Charts

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to show a few. For example:

 This ...
 rather than this

 There are a couple of ways to tackle this:

1. Filter the chart data in the chart definition. Using an XPATH expression you can filter out all of the criteria you do not want to see. Open the chart definition and update the definition. You will need to update the RowCount, RowLabels and DataValues attributes in the chart definition. Adding in the following XPATH expression:

    [DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']

so the DataValues value becomes:

    <DataValues><xsl:for-each-group select=".//G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                        DEPARTMENT_NAME='Executive']" ...

2. Create a variable in the template to hold just the values you want to chart.

    <?variable: filterDepts; /DATA_DS/LIST_G_1/G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                       DEPARTMENT_NAME='Executive']?>

Then update the chart definition with the variable for the same three attributes above, the RowCount, RowLabels and DataValues. For example:

    <DataValues><xsl:for-each-group select="$filterDepts" ...

These both work admirably, but they both require some manual updating of the chart definition which can get fiddly and a pain to maintain. I'm also just filtering for three departments, when you get up to 5 or 6 then the XPATH starts to become a pain to maintain. Option 2 alleviates this somewhat because you only need to define the filter once to create the filtered variable.
A better option may be ...

3. Force the effort down into the data layer. Create another query in the report that just pulls the data for the chart.

LIST_G2/G_2 holds the data for the chart. Then all you need do is create a vanilla chart on that particular section of the data.

Yes, there is some overhead to re-fetch the data but this is going to be about the same if not less than the extra processing required in the template with options 1 and 2. This has another advantage, you can parametrize the criteria for the user. You can create a parameter to allow the user to select, in my case, the department(s) they want to chart.


Its simple enough to create the multi-select parameter and modify the query to filter based on the values chosen by the user.

Sample report (including data model and layout template here) just un-archive into your catalog.
RTF Template plus sample data available here.




Wednesday Nov 14, 2012

Chart Filtering

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data?

In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done.

RTF

Build your chart as you would normally to include all the data to start with.

Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries.

<Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="true"/>
 <Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/>
 <LocalGridData colCount="{count(.//G_2)}" rowCount="1">
  <RowLabels>
   <Label>SALARY</Label>
  </RowLabels>
  <ColLabels>
   <xsl:for-each select=".//G_2">
    <Label><xsl:value-of select="EMP_NAME"/></Label>
   </xsl:for-each>
  </ColLabels>
  <DataValues>
   <RowData>
    <xsl:for-each select=".//G_2">
     <Cell><xsl:value-of select="SALARY"/></Cell>
    </xsl:for-each>
   </RowData>
  </DataValues>
 </LocalGridData>
</Graph>

Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My  data is structured thus:

 

<DATA_DS>
    <G_1>
        <DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
        <G_2>
            <MANAGER>Higgins</MANAGER>
            <EMPLOYEE_ID>206</EMPLOYEE_ID>
            <HIRE_DATE>2002-06-07T00:00:00.000-04:00</HIRE_DATE>
            <SALARY>8300</SALARY>
            <JOB_TITLE>Public Accountant</JOB_TITLE>
            <PARAS>11000</PARAS>
            <EMP_NAME>William Gietz</EMP_NAME>
        </G_2>

So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.

Online Template

Implementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.


  You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.

Pretty straightforward stuff!

Monday Feb 20, 2012

Extra, extra, read all about it "Charting FAQ Found"

I was digging back through some previous chart entries looking for something for a customer. One of the holy grails was the Oracle Reports Charting FAQ page that I have linked to (BIP 10g shares the same engine) ... the page is missing :0(

http://www.oracle.com/technology/products/reports/htdocs/faq/graph_faq_with_style.html

Enter the Wayback Machine and a bit of digging and I found a copy :0) I have saved it locally for all to share, some images are missing but the code for the chart features you want are there. Get it here or you can jump over to the time machine page here.

Enjoy time traveling!

Tuesday Jul 05, 2011

Chart with BI Publisher 11g Layout Editor

image

Chart is one of the best and most effective ways to provide your audience a great insight about the data. Creating charts with BI Publisher 11g Layout Editor has made the chart creation so much easy and simple. Just for that reason, I personally use BI Publisher Layout Editor to visualize my data in spreadsheets. All I need is a standard web browser and a mouse (or touch pad with my macbook pro), after a few drags & drops I have beautiful charts that are automatically linked together and I can start interacting with the charts to gain more insights from the data.

There are many common menus available in the Layout Editor’s ribbon tool bar and much more options available under Property section at the left hand side. Here is a quick video that I put together to show you how to create charts quickly and make them valuable. It covers the following items.

  • Bar/Pie Charts
  • Multiple Measures/Series
  • Legend Position, Legend Label Text and Formatting
  • Chart Title

Please let me know if you have any question or something that you want us to cover on the video or blog. Also, please share what are the most common property options that you use for the chart. We’re currently evaluating what property options to move to the ribbon bar to make your chart design even more simple for the next release.

Friday Sep 24, 2010

Stacked Charts with Totals

[Read More]

Wednesday Jan 13, 2010

Stacked Chart with only two data fields?

[Read More]

Tuesday Jan 12, 2010

Where's the chart text in Word 2007?

[Read More]

Wednesday Jun 03, 2009

Charts with Top 5 + Rest of World

[Read More]
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today