Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2009-04-03
5
Medium Priority
?
298 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Rhonda Carroll
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24058843
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
0
 

Author Comment

by:Rhonda Carroll
ID: 24059134
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
0
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 1000 total points
ID: 24059184
use
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'
0
 

Author Comment

by:Rhonda Carroll
ID: 24059244
Did that too, look at the pic to see what is happening.
report-error.doc
0
 

Author Comment

by:Rhonda Carroll
ID: 24059269
never mind got it. Thanks!
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question