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

How to count rows containing a specific string?

tsvetanako

Hello everyone!

I'm using BIRT since almost 6 months and I love really love it. I'm facing an issue rite now and I can't find a solution, so that's why I'm here.

In my report the query is returning some rows. I have a row named "approved" and this row contains "on" or "null" value. What I want to do is to count the ammount of "no", but as I said I can't find a solution the last 2 days.

Can you help me with this issue?

Thanks in advance!

Find more posts tagged with

Comments

Hans_vd

Where do you want the count to appear?
As a column in the data set?
In the footer row of a table?

tsvetanako

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="115449" data-time="1364389525" data-date="27 March 2013 - 06:05 AM">
Where do you want the count to appear? 
As a column in the data set? 
In the footer row of a table? </blockquote>
 
As a column in the dataset.

Hans_vd

First create a computed column compColNo with this expression: 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (row["approved"]=="on") { 1; } else { 0; }</pre>
 
Then create a second computed column compColNoCount with aggregation = SUM and expression = row["compColNo"] 
 
That should be it. 
 
Regards 
Hans

tsvetanako

Thanks for helping me!
I have 17 "on" results and the count returned 743...

Hans_vd

Are you sure, you used SUM in the aggregation and not COUNT?
Do you have any filters on the table?

tsvetanako

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="115454" data-time="1364392429" data-date="27 March 2013 - 06:53 AM">
Are you sure, you used SUM in the aggregation and not COUNT? 
Do you have any filters on the table? </blockquote>
 
Yep. I'm 100% sure.

Hans_vd

You are not counting "on", you are counting "no" values.
Is that a value that appears in the data set? Because if not, that would only make it even less understandable.

tsvetanako

sorry, my mistake. I made a quick screenshot, right after experimenting, That's why the value is "no". Anyways with the correct <pre class='_prettyXprint _lang-auto _linenums:0'>on</pre> value I'm getting 743 insted of 17

Hans_vd

What happens if you try it with an other valid value of the approved column?
Do you get 743 as a result too then?
Is 743 the total number of rows in the data set or does it seem just a random value to you?

tsvetanako

743 is not the total number of rows. 
 
when I'm tring: 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (row["approved"]== null ) { 1; } else { 0; }</pre>
 
I'm getting 464 as result. It seems like a random number to me.

Hans_vd

I'm a bit out of ideas now.
Can you post the rptdesign so that I can take a look at it?

tsvetanako

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="115461" data-time="1364394433" data-date="27 March 2013 - 07:27 AM">
I'm a bit out of ideas now. 
Can you post the rptdesign so that I can take a look at it? </blockquote>
 
I can't because it's linked to a productive server.

Thanks again for trying to help me!

Tubal

I'm not sure why Hans solution isn't working, but another thing to try would be to just use a COUNT aggregate in your computed columns and use a filter:

Hans_vd

Hi Tubal,

Thanks for pointing that out!
I've seen this dialog many many times, but for some reason I never realized aggregating and filtering in 1 step was possible.

Regards
Hans