Problem
You have a graph with many series, example countries. Every time you add a country you could edit the graph to add another series, OR you could do it dynamically.Solution
Create a chart that runs off a PLS/SQL function rather than a SQL query.So create your chart in the normal way, either using a wizard or manually by creating a region of type "chart".
Now go to your series properties and change the source to a function.
The function looks like this:
CREATE or REPLACE function XX_BA_CONSOLIDATED_VIEW_fn
return varchar2
is
l_qry VARCHAR2(32767);
BEGIN
l_qry := 'SELECT null, actual_benefit_year, ';
--Loop through the series and add a sum(decode...) column with column alias
FOR r1 IN (SELECT DISTINCT cou_country_name FROM XX_BA_CONSOLIDATED_VIEW )
LOOP
l_qry := l_qry || 'sum(decode(cou_country_name ,''' || r1.cou_country_name ||
''',calculated,0)) ' || replace(r1.cou_country_name,' ','_')|| ',';
END LOOP;
--Trim off trailing comma
l_qry := rtrim(l_qry, ',');
--Append the rest of the query
l_qry := l_qry || ' FROM XX_BA_CONSOLIDATED_VIEW GROUP BY actual_benefit_year order by actual_benefit_year';
RETURN l_qry;
END XX_BA_CONSOLIDATED_VIEW_fn;
So at run time it will produce something like this:
SELECT NULL,
actual_benefit_year,
SUM (DECODE (cou_country_name, 'Denmark', calculated, 0)) Denmark,
SUM (DECODE (cou_country_name, 'Brazil', calculated, 0)) Brazil,
SUM (DECODE (cou_country_name, 'Ireland', calculated, 0)) Ireland,
SUM (DECODE (cou_country_name, 'Chad', calculated, 0)) Chad,
SUM (DECODE (cou_country_name, 'United Kingdom', calculated, 0))
United_Kingdom,
SUM (DECODE (cou_country_name, 'Estonia', calculated, 0)) Estonia,
SUM (DECODE (cou_country_name, 'France', calculated, 0)) France,
SUM (DECODE (cou_country_name, 'Germany', calculated, 0)) Germany,
SUM (DECODE (cou_country_name, 'Australia', calculated, 0)) Australia,
SUM (DECODE (cou_country_name, 'Belgium', calculated, 0)) Belgium,
SUM (DECODE (cou_country_name, 'Canada', calculated, 0)) Canada,
SUM (DECODE (cou_country_name, 'Japan', calculated, 0)) Japan
FROM XX_BA_CONSOLIDATED_VIEW
GROUP BY actual_benefit_year
order by actual_benefit_year
(You can see the reason for me replacing spaces with underscores in the column name!)
Now make all the changes to your graph to get it to look how you want. Example legends, titles, colours etc.
It is important to do this first, if you want to do the next step.
You can get the series names to show up as chart labels or tool tips. You may not want to do both, it depends how dense your information is. If you do, this is how you do it:
Go to the attributes of your chart, and right near the bottom is a property called Custom XML, set this to "Yes", it now produces a big text box of XML based on all of the properties that you have for your graph. (This is why you needed to have your graph perfect before selecting this option).
We want to change this XML.
Tip: Copy/paste this XML to a nice editor like http://www.freeformatter.com/xml-formatter.html#ad-output , so that you can see what's going on.
Find the XML tag <data_plot_settings> -> <bar_series> -> <label_settings> -> <format>. There you edit the [CDATA] tag by replacing the %Name keyword with %SeriesName
You can then do the same for the [CDATA] tag in <tooltip_settings> -> <format>
Now copy/paste the XML back into the property, save and see whether you like it.
(As I said, you may not want to do both or either, play with them and change them back if it doesn't suit your graph).
It will look something like this, if you've modified the tooltip, the country name will highlight when you hover your mouse over the relevent bar, and if you've modified the labels setting you will get a value at the top of each bar.
Comments
Post a Comment