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; } } }

scripting a sum value

Go forward

HI 
I have this query: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select 'threatened' as type_menace, count(*) from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_menaces)
and gez_id_geom_zonage = 217609
union
select 'less threatened' as type_menace, count(*) from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_less_menaces)
and gez_id_geom_zonage = 217609
union
select 'non threatened' as type_menace, count(*) from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_non_menaces)
and gez_id_geom_zonage = 217609
union
select 'non evaluated' as type_menace, count(*) from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_non_evalues)
and gez_id_geom_zonage = 217609
</pre>
 
I have done a pie chart from the above query, a similar result is shown in the attached picture. 
Now the number 240 mentionned on the report means the sum of all the count i have in my query. 
How can I do this sum usig a script? 
Thank's for your help

Find more posts tagged with

Comments

mwilliams

You want to find the sum of all the counts in your query and use the value in your chart title? Is that what you're asking?

Go forward

Sorry for the delay Williams,

Not necessary in chart title, but if it cannot be done out of the chart title then I have to display in my chart title.

Any Idea of how to do it?
Thank's in advance.

mwilliams

You could display it in the chart title or any label within the chart. Or you could display it outside the chart in a text box. If you want to know the number of rows in your dataSet, which is used for the chart, you can create a computed column to do a count aggregation, then store the value in a global variable in the report context, then recall this variable in the chart or in your text box. If you let me know your BIRT version, I can make you an example.

Go forward

It's a 3.7 Birt version.

mwilliams

Take a look at this example. Places to look are: 
 
<ul class='bbc'><li>Computed column in dataSet</li><li>Text box bound to dataSet at top of report</li><li>onCreate script of text box, to set global variable</li><li>chart script, to set chart title</li></ul>

Go forward

Thank's very much Williams I will take look at it now, I was trying to figure out other things in my report.

I tell you later what is going be done?

Thnak's

Go forward

I got this problem when I preview your report whitin Eclipse IDE :

java.lang.IllegalStateException: The viewing session is not available or has expired

even my reports are not displayed.

mwilliams

Is this with the viewer? Or just in preview? Or all output formats? Can you go to Window->Preferences->ReportDesign->Preview and select to always use external browsers, then run it in the web viewer?

Go forward

Sorry for the delay Williams, I had some problems and I could not connect to the Internet.
So my problem is about the preview mode within Eclipse not with the viewer cause I did what you
ask me to do and it works very well with the viewer.
But when previewing my reports after passing my parameters the screen still loading the report...

Go forward

I had take a look at the places you told me to take look at but when I preview the report in the web viewer it displays this error:

Wrapped java.lang.IllegalArgumentException: Cannot format given Object as a Number at ligne 7 in the script :'' (Element ID:9)

mwilliams

Sorry for that. I used a GlobalVariable, where I should have used a persistentGlobalVariable. This worked in the preview, but not in the separate run and render tasks of the web viewer. The above example has been replaced with a corrected one.

Go forward

So what should I do while this code dosn't work in the viewer, and not even the preview mode is working for me?
I have changed GlobalVariable into persistentGlobalVariable but nothign is displayed.

You can see the attached picture, the "Menace" pie chart is where I put my script in the script Tab.

Go forward

If you don't mind, I have another question about creating a cross tab, If you have more time.

Thank's for your answers

mwilliams

Did you get the chart thing working? For the crosstab question, can you start a new thread, for that?

Go forward

No I got nothing working in the chart.

mwilliams

Is the example I posted working? Did you redownload the example above? I replaced it with a new version.

Go forward

Thank's Williams
Yes it's working,
But what if I want to display it outside of my chart title in a text box, cause
I need to put a kind of text that contains the count value ?

Go forward

It's working for your chart example but not in mine.
My chart title still empty!

mwilliams

Can you attach your report? Or send it to me in email, if you cannot attach it in here?

Go forward

Hi Williams,
Here is one of my reports, the DataSource is created from an external DataBase.

Go forward

HI Williams, 
In order to count the count(*) rows, I have create another dataset and fill it with the following query : 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select sum(result.nbre) from(
select 'Esp?ces menac?es ' as type_menace, count(*) as nbre from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_menaces)
and gez_id_geom_zonage = ?
union
select 'Esp?ces quasi menac?es ' as type_menace, count(*)as nbre from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_quasi_menaces)
and gez_id_geom_zonage = ?
union
select 'Esp?ces non menac?es ' as type_menace, count(*)as nbre from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_non_menaces)
and gez_id_geom_zonage = ?
union
select 'Esp?ces non evalu?es' as type_menace, count(*)as nbre from tv_taxon_observe_commune
where tao_id_taxon in (select tao_id_taxon from tv_taxons_qualif_non_evalues)
and gez_id_geom_zonage = ?
)result
</pre>
I stock the result in "result". 
Now I need to acces to this "result" whithin this dataset and call it form the other dataset wich contains the Pie Chart( where I need to display the sum value in a textbox for example)? 
 
Thank's

mwilliams

Try this. You didn't have the text box, where you set the variable, bound to the dataSet.

Go forward

The Text box is displaying order by year (4) nice but it's not the result I want it to
be displayed, I want the sum of all the count in my query.
Sorry If I make you understand the problem in a different way, not the number 4 which shoud
be displayed but:
For example in my Pie chart,
If i have 85 : the count number of ' Esp?ces menac?es '
and 90 : the count number of ' Esp?ces quasi menac?es '
and 50 : the count number of ' Esp?ces non menac?es '
and 200: the count number of ' Esp?ces non evalu?es '
The sum of these counts (425) should be siplayed in the text box.

I want to know How did you bound the textbox to the dataset ?

mwilliams

You bind the text box to the dataSet by going to the binding tab of the property editor and selecting a dataSet.

mwilliams

I changed your computed column to be a summation of your count field. Hopefully this does what you're wanting.

Go forward

Thank's very much Williams that's exactly what I want.
It works.
I had create a dynamic text and bind it with my dataset in order to display into it
a text with the sum value you have shown me cause in the title of the pie chart, the text
seems to be very big and it makes the chart looks very small, looks very bad like that, but it's working
anyway.

As I told you, I have to do a cross tab, and I have posted the topic if you could take look at it, cause really I need help on it it has been several days that I'm trying to do it as it should be done.

But I'm still not progressing in this task.
Thank's a lot for your precious help.

Goforward.

mwilliams

I'll go take a look for the other post.