nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

creating chart from table colum names

rundolph

Hello List, 
 
I'm quite new to birt and I need to design a pie chart using availability values from nagios. 
The corresponding tdata set supplies the data as followes: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>| Percent_OK| Percent_Down| Percent_Warning |

| 99 | 1 | 0 |</pre>
 
So I need to create a pie chart with "catogory definition" as colum names and and "slice size definition" as its values. 
 
As I figured out, i can only create a chart when the names and values are in seperate colums like 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>status | value

Percent_OK | 99
Percent_Down | 1
Percent_Warning | 0</pre>
 
But in my case that is not possible. 
Can anyone give me some hints or solutions for this? I think it is a common scenario? 
Thank you and best regards 
 
Rundolph

Find more posts tagged with

Comments

Hans_vd

Hi Rundolph, 
 
Are you selecting the from a database? 
Can you write your query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT 'Percent_OK' AS status,
percent_ok AS status_value
FROM [your_table]
UNION ALL
SELECT 'Percent_Down' AS status,
percent_down AS status_value
FROM [your_table]
UNION ALL
SELECT 'Percent_warning' AS status,
percent_warning AS status_value
FROM [your_table]</pre>
 
Hope this helps 
Hans

rundolph

Hi Hans, 
 
thank you for your quick response. Yes, I know this works easy but it does not work with report parameters. 
The only solution would be to assign variables to the data set. But there seems to be only possible asigning report parameters. 
 
My Data set looks as followes: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT round(avg(PERCENT_TIME_OK_SCHEDULED +
PERCENT_TIME_OK_UNSCHEDULED),2) Percent_OK,
round(avg(PERCENT_TIME_WARNING_UNSCHEDULED),2) Percent_Warning,
round(avg(PERCENT_TIME_CRITICAL_UNSCHEDULED),2) Percent_Down,
round(avg(PERCENT_TIME_CRITICAL_SCHEDULED +
PERCENT_TIME_UNKNOWN_SCHEDULED +
PERCENT_TIME_WARNING_SCHEDULED),2) Downtime,
round(avg(PERCENT_TIME_UNKNOWN_UNSCHEDULED),2) Unknown,
round(avg(PERCENT_TIME_UNDETERMINED_NOT_RUNNING +
PERCENT_TIME_UNDETERMINED_NO_DATA),2) 'no Data'
FROM dashboard.service_availability
where HOST_NAME = ?
and SERVICE_NAME = ?
and DATESTAMP between ? and ?
group by SERVICE_NAME;</pre> 
 
With union selects any select needs the "where" clause so I need to assign always a ? or a fixed variable. 
Have you assigned parameters or variables to this union selects? 
Regards 
 
Ralf

Hans_vd

Hi Ralf, 
 
There's an interesting article on reusing parameters in a birt dataset <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/'>here</a>. But you will have to add a where clause to every select statement of course. You're query will look like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH params (
SELECT ? AS p_host_name,
? AS p_service_name,
? AS p_date_from,
? AS p_date_to
FROM dual
)
SELECT 'Percent_OK' AS status,
round(avg(PERCENT_TIME_OK_SCHEDULED + PERCENT_TIME_OK_UNSCHEDULED),2) AS status_value
FROM dashboard.service_availability,
params
WHERE HOST_NAME = p_host_name
AND SERVICE_NAME = p_service_name
AND DATESTAMP BETWEEN p_date_from AND p_date_to
GROUP BY SERVICE_NAME;
UNION ALL
SELECT 'Percent_Warning'
round(avg(PERCENT_TIME_WARNING_UNSCHEDULED),2) AS status_value
FROM dashboard.service_availability,
params
WHERE HOST_NAME = p_host_name
AND SERVICE_NAME = p_service_name
AND DATESTAMP BETWEEN p_date_from AND p_date_to
GROUP BY SERVICE_NAME;
UNION ALL
SELECT 'Percent_Down' AS status,
round(avg(PERCENT_TIME_CRITICAL_UNSCHEDULED),2) AS status_value
FROM dashboard.service_availability,
params
WHERE HOST_NAME = p_host_name
AND SERVICE_NAME = p_service_name
AND DATESTAMP BETWEEN p_date_from AND p_date_to
GROUP BY SERVICE_NAME;
UNION ALL
...
</pre>
 
 
About the use of dual, you can read about that in the comments of the blogpost I linked to 
 
 
Regards 
Hans

rundolph

Hello Hans,

thank you for that very good solution. However, this data set is little ressource intensive, but it works. :-)
Best regards
Rundolph

Hans_vd

Hi Ralf, 
 
If you're looking for a solution that is less resource intensive, you might want to try this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH columns_to_rows AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY ROWNUM <= 6
)
SELECT CASE ctr.rn
WHEN 1 THEN 'Percent_OK'
WHEN 2 THEN 'Percent_Warning'
WHEN 3 THEN 'Percent_Down'
WHEN 4 THEN 'Downtime'
WHEN 5 THEN 'Unknown'
WHEN 6 THEN 'no Data'
END status,
CASE ctr.rn
WHEN 1 THEN round(avg(PERCENT_TIME_OK_SCHEDULED + PERCENT_TIME_OK_UNSCHEDULED),2)
WHEN 2 THEN round(avg(PERCENT_TIME_WARNING_UNSCHEDULED),2)
WHEN 3 THEN round(avg(PERCENT_TIME_CRITICAL_UNSCHEDULED),2)
WHEN 4 THEN round(avg(PERCENT_TIME_CRITICAL_SCHEDULED + PERCENT_TIME_UNKNOWN_SCHEDULED + PERCENT_TIME_WARNING_SCHEDULED),2)
WHEN 5 THEN round(avg(PERCENT_TIME_UNKNOWN_UNSCHEDULED),2)
WHEN 6 THEN round(avg(PERCENT_TIME_UNDETERMINED_NOT_RUNNING + PERCENT_TIME_UNDETERMINED_NO_DATA),2)
END status_value
FROM dashboard.service_availability,
columns_to_rows ctr
WHERE HOST_NAME = ?
AND SERVICE_NAME = ?
AND DATESTAMP BETWEEN ? AND ?
GROUP BY SERVICE_NAME, ctr.rn
</pre>
 
This solution also makes use of the WITH clause, but in this case it is used to generate a number for each row you'll have in the dataset. Next, in the query the rownumbers are joined to your original query and in the select, depending on the rownumber another function or sum or whatever you want is performed on the data. Don't forget to add the rownumber to the GROUP BY, to make things work. 
 
Some of the syntax may be Oracle specific, but I'm sure you'll find an equivalent for whatever database it is that you are executing the query on. 
 
Regards 
Hans

rundolph

Thank you, Hans. The Database is a Mysql Database and does not support the with clause. But I will try to use your hints, as soon as they become necessary. For me, assigning each report parameter to each union select works too. :-)