How to discover number of rows returned by SQL?

Is there some Javascript code I can add to a BIRT report to discover how many rows were returned from a query *before* filtering is applied?

onFetch is called after filtering so the raw number of rows returned is hidden.
(I typed ?this.? in various Data Source/Data Set events hoping I might stumble on a useful method; but no luck.)

Would like to know the raw row count returned from a query for both:
i) a query hard coded in a report *AND*
ii) when I?m using an IOB. (I suspect IOB might be tricky as it's living on the iServer and thus its data may not be easily accessible)

Using A11sp1.

Thanks for any suggestions.
Milt.

Find more posts tagged with

Comments

thuston

Whenever possible the report design will push the Filter to the database to make the performance much more efficient.
If it is added to your SQL you can never know the unfiltered quantity.
If it is not added to the SQL, add a COUNT() column to your SQL and the database will tell you how many rows it returned.

millvall

Hi,

Thanks for reply.
The IOB query below *almost* works. (To count rows returned.)
QUESTION: How do I insert a parameter into the subquery: That is: WHERE state = :pState in the sub query.
It 's says it Param pState not recognized.

Milt.

WITH(pState VARCHAR)

SELECT
CLASSICMODELS_Customers.customerName AS customerName,
CLASSICMODELS_Customers.state AS state,
x.rc

FROM "../Data Sources/c2/CLASSICMODELS.Customers.sma"
AS CLASSICMODELS_Customers,

(
SELECT
count(*) AS rc
FROM "../Data Sources/c2/CLASSICMODELS.Customers.sma"
WHERE state = :pState -- <<< This line does not work

)
AS x

WHERE state = :pState

thuston

That is how it was done in Actuate eRD Pro.<br />
In BIRT and IOD DataSet, you simply use a ? for parameter.<br />
Then OK and on the Parameters tab you define the parameter name and settings to be used for the '?'s in order.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT
count(*) AS rc
FROM "../Data Sources/c2/CLASSICMODELS.Customers.sma"
WHERE state = ?</pre>