asked on

How can I get the value of my textbox in reporting services to include a sum expression?

Hi,
I have the following in one of my text boxes on my value:

= "Total Number of Incidents in Service Alliance queue: "&" "&Sum(Fields!TotalIncidents.Value, "IncidentsInSAQueue")

IncidentsInSAQueue is the name of my dataset:

select count(sequence) from _SMDBA_._TELMASTE_
where [date open] > '01-oct-08'
and _SMDBA_._TELMASTE_.subject = 1381
and _group_ in (1011, 1004)
and status = 'O'

When I try to run I get this error:
C:\Reports\Astea\Astea\Service_Alliance_Incident_Report.rdl A value expression used for the report parameter '=count(sequence)' includes an aggregate function. Aggregate functions cannot be used in report parameter expressions.

Can someone please show me how this should be entered into the textbox?

Thanks,
Rhonda

udaya kumar laligondla

no aggregate functions are allowed in the report parameters.
if you have to provide the aggregate value then make it part of the query to generate the aggregate value as a field, and use the field as parameter
create select as
select A,B,C,(select Count(A) as CountA) from tableName
and use CountA as parameter

Beverly Penney

ASKER

Hi udayakumarlm:

Can you modify my query to do this?

This is my orginal:
select count(sequence) from _SMDBA_._TELMASTE_
where [date open] > '01-oct-08'
and _SMDBA_._TELMASTE_.subject = 1381
and _group_ in (1011, 1004)
and status = 'O'

I tried:
select sequence, (select count(sequence) as seq) from _SMDBA_._TELMASTE_
where [date open] > '01-oct-08'
and _SMDBA_._TELMASTE_.subject = 1381
and _group_ in (1011, 1004)
and status = 'O'

and used this in my text box:
= "Total Number of Incidents in Service Alliance queue: "&" "&(Fields!seq.Value, "IncidentsInSAQueue")

but it gives a group by error

ASKER CERTIFIED SOLUTION

udaya kumar laligondla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Beverly Penney

ASKER

Did that too, look at the pic to see what is happening.
report-error.doc

Beverly Penney

ASKER

never mind got it. Thanks!