Retrieving RowCount from a dataset

Hi,
I need to calculate a percentage of population based on the number of rows
returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
there is no way for SRS to receive multiple result sets in a single data set.
I could execute the query twice, once for the count and second with the
count included as a column. I hate to run the query twice. What I could use
is a rowcount from the dataset in SRS, but I don't this property exists.
Does anyone have any ideas?
Thank you,
BobBob,
As you are using a SProc to return the recordset, just add the @.@.rowcount to
the end of the SELECT statement and it will be returned with each row. This
will allow a single call only, and the amount of 'extra' data returned (2
bytes per row maybe?) will be mnore efficient than recalling the SProc a
second time.
Tony
"Bob" wrote:
> Hi,
> I need to calculate a percentage of population based on the number of rows
> returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> there is no way for SRS to receive multiple result sets in a single data set.
> I could execute the query twice, once for the count and second with the
> count included as a column. I hate to run the query twice. What I could use
> is a rowcount from the dataset in SRS, but I don't this property exists.
> Does anyone have any ideas?
> Thank you,
> Bob|||Hi Tony,
I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
only valid after the select has completed. I was hoping that a rowcount
property would be available within SRS.
Thank you for the suggestion.
Bob
"Logicalman" wrote:
> Bob,
> As you are using a SProc to return the recordset, just add the @.@.rowcount to
> the end of the SELECT statement and it will be returned with each row. This
> will allow a single call only, and the amount of 'extra' data returned (2
> bytes per row maybe?) will be mnore efficient than recalling the SProc a
> second time.
> Tony
> "Bob" wrote:
> > Hi,
> >
> > I need to calculate a percentage of population based on the number of rows
> > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > there is no way for SRS to receive multiple result sets in a single data set.
> > I could execute the query twice, once for the count and second with the
> > count included as a column. I hate to run the query twice. What I could use
> > is a rowcount from the dataset in SRS, but I don't this property exists.
> > Does anyone have any ideas?
> >
> > Thank you,
> > Bob|||Bob,
Have you tried the CountRows function? (Syntax should be:
=CountRows("yourDataset")) Just a suggestion. I have never used this
technique.
Ernie
Bob wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||I figured out how to do this.
In the table HEADER section, which is the entire scope, I placed a
CountDistinct(fld!name.value) for the unique item in the dataset. Then I
made the column invisible. Lastly, I referenced this value within the table
via ReportItems!textboxname.value. This gave me the count I was looking for.
"Bob" wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||Bob,
Excellent result. We are all still on the learning curve, I'll have to
remember that one.
Regarding using the @.@.RowCount feature, you could also have used a simple
variable in the sproc and set it to Select Count(*) FROm tblename and then
passed that as an extra column in the final select statement.
Tony
"Ernie Gutierrez" wrote:
> Bob,
> Have you tried the CountRows function? (Syntax should be:
> =CountRows("yourDataset")) Just a suggestion. I have never used this
> technique.
> Ernie
> Bob wrote:
> > Hi Tony,
> >
> > I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> > only valid after the select has completed. I was hoping that a rowcount
> > property would be available within SRS.
> >
> > Thank you for the suggestion.
> >
> > Bob
> >
> > "Logicalman" wrote:
> >
> > > Bob,
> > >
> > > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > > the end of the SELECT statement and it will be returned with each row. This
> > > will allow a single call only, and the amount of 'extra' data returned (2
> > > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > > second time.
> > >
> > > Tony
> > >
> > > "Bob" wrote:
> > >
> > > > Hi,
> > > >
> > > > I need to calculate a percentage of population based on the number of rows
> > > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > > there is no way for SRS to receive multiple result sets in a single data set.
> > > > I could execute the query twice, once for the count and second with the
> > > > count included as a column. I hate to run the query twice. What I could use
> > > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > > Does anyone have any ideas?
> > > >
> > > > Thank you,
> > > > Bob
>