Is it possible to access the @@RowCount from a Sproc in a report

Hi ,

I am trying to access the @.@.RowCount that is been returned by my stored procedure in a report. Can you please tell me how can i access it, This is my Stored procedure

USE [ICCStatements]GO/****** Object: StoredProcedure [dbo].[rpt_SelectInvestments] Script Date: 08/03/2007 11:54:47 ******/SET ANSI_NULLSOFFGOSET QUOTED_IDENTIFIEROFFGOALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanIdAS integer)ASBEGIN-- History-- 08/17/2004svanpatter/JSWCOinitial version created-- 08/30/2004svanpatter/JSWCOadd-- Select available fundsDeclare @.CountintSELECT [ClientPlan].PlanId, [ClientPlan].PlanName,-- Fund.[FundName] AS InvestmentName,CASEWHEN PlanFund.PlanFundDisplayNameISNULLTHEN Fund.ShortNameELSE PlanFund.PlanFundDisplayNameEND InvestmentName,'F'AS InvestmentType,--PlanFund.PlanId As InvestmentID PlanFund.IsPortfolioFundOnly, PlanFund.FundDisplayOrderAs InvestmentIDFROM [ClientPlan]--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdINNERJOIN PlanFundON [ClientPlan].PlanId = PlanFund.PlanIdAnd IsPortfolioFundOnly = "0"INNERJOIN FundON PlanFund.FundId = Fund.FundId--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperIDWHERE[ClientPlan].PlanId = @.PlanIdUNION-- Select PortfoliosSELECT [ClientPlan].PlanId, [ClientPlan].PlanName, PlanPortfolio.PortfolioNameAS InvestmentName,'P'AS InvestmentType,NULL,PlanPortfolio.PortfolioIdAs InvestmentIDFROM [ClientPlan]INNERJOIN PlanPortfolioON [ClientPlan].PlanId = PlanPortfolio.PlanId--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdWHERE[ClientPlan].PlanId = @.PlanIdORDER BYInvestmentType, InvestmentIDSet @.Count =@.@.RowCountReturn @.CountEND

Can some one pls tell me how can i access @.Count from the report.

Any help will be appreciated..

Regards,

Karen

If you meant Crystal Reports then I don't understand why you need this as in CR you already have such function that returns number of rows returned by SP.

|||

I meant in SQl server Reporting Services.

|||

=Count(Field!somefieldname.Value)

|||

lavanya,

Is it possible to access the no. of rows returned by the stored procedure....

Regards

Karen

|||

You shouldn't need to return the RowCount from the stored procedure. In SQL Reporting Services there is a CountRows() function that can be used to return the number of rows in the datasource. To use it you would call it like so: =CountRows("DatasourceName"). The "DatasourceName" obviously being the name of the datasource that calls your stored procedure.

Also, the = sign is only needed if it is the only item, or first item, in a text field in the report.