Aggregation Count different from SQL Count

Hi all, I have a table named wintelCosts, and the columns of interest for me in this table are activityStatus and serverPurpose. 
 
Fields in the column serverPurpose range from 'Application' to 'Infrastructure' to 'Web', among others, and activityStatus are either 'Active', 'Not Active', or 'Decommissioned'. 
 
I want to find out, say, how many active Wintel servers are there having serverPurpose = 'Application', so I try it out first using the Data Set SQL Query: 
 
select count(*) as ApplicationCount 
from wintelCosts 
where activityStatus = 'Active' and serverPurpose = 'Application' 
 
Which has an output of 191, which is what I want. 
 
However, I have to come up with a count output for all of these combinations ('Application' and active, 'Infrastructure' and active, etc.), so I start looking for a computed column solution. I create a computed column called ApplicationCount, and edit my SQL Query to be this: 
 
 
select * from wintelCosts 
 
The computed column has an integer DataType, a COUNT aggregation, and both the expression and filter (I still don't know the difference between the two) are set to 
 
row["serverPurpose"] == 'Application' && row["activityStatus"] == 'Active' 
 
And I get a count of 189, 2 fewer than the correct count. I have no idea why this is happening, could anyone help me out here? Thank you!

Find more posts tagged with

Comments

Auroras

I am able to reproduce this with this trimmed down sample Excel spreadsheet. I link the table in Access (without importing it) and I link it to BIRT using <a class='bbc_url' href='http://digiassn.blogspot.com/2008/02/birt-birt-reporting-for-microsoft.html'>this guide.</a>

Tubal

Without seeing your actual data, it's hard to say why the counts would be different.

1. Is this in Preview only? Maybe you have your preview row limit set to lower than the actual returned results.

2. Could be that there are some spaces somewhere that BIRT sees that SQL doesn't.

3. Maybe you have an additional filter on the BIRT table that you forgot about.

I would probably remove the filter in the expression and use it only in the filter section of the aggregate, or the table.

So your expression would read something like row["billDate"] (it doesn't really matter which row you count, as long as the value is never null) and then you would put the filters in your filter. Theoretically it shouldn't matter. But maybe it's throwing it off.

Auroras

I'm continuing to play with the query, and now I've found out that without changing the properties for the computed column, I can get it to output the correct results by changing the SQL Query from 
 
select * from wintelCosts 
 
to 
 
select * 
from wintelCosts 
where activityStatus = 'Active' and serverPurpose = 'Application' 
 
So basically I can count correctly if the SQL Query was already restricted to output only those that I want to count. If the SQL Query outputs a lot of other things and I try to count it in the expression builder, something screws up. 
 
From the preview results I see that there are 2 rows where the billDate is different from the rest of the rows: the billDates for almost all the others are in May, whereas the billDates for the last two are for different dates. I don't know if this somehow plays into the discrepancy or just a coincidence. 
 
Otherwise my progress has been slow.

Auroras

Turns out your first response was what was wrong: 
 
<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="105167" data-time="1340643497" data-date="25 June 2012 - 09:58 AM">
Without seeing your actual data, it's hard to say why the counts would be different. 
 
1. Is this in Preview only? Maybe you have your preview row limit set to lower than the actual returned results. 
 </blockquote>
 
When I looked at the data set editor, only the first 500 results were shown, and that for some reason the count column stopped at row 500 and didn't go any further. I tweaked the Data Set Editor display row settings (Window - Preferences - Report Design - Data Set Editor) to show the maximum number of allowed rows, and now everything's behaving as it should be! 
 
Thank you.