MAX COUNT

I have a data set with a list of text values for a survey.
Survey Response Text
54548 More than once a week
84318 More than once a week
81367 Once a week
87186 More than once a week
44987 Twice a week
84938 Twice a week
I've put them into a matrix to display the reponse count.
Response Text Count
More than once a week 3
Twice a week 2
Once a week 1
To do this I used: =COUNT(Fields!ResponseText.Value)
Now what I need to do is make a one-line table/matrix with only the maximum count value of the Response Text that looks something like this:
Response Text Count
More than once a week 3
Unfortunately, I can't do MAX(COUNT(Fields!ResponseText.Value)). MAX(Fields!ResponseText.Value) just gives me the largest string value. Is there a way I can achieve this without creating an additional dataset just to pull the maximum count value in?
Any help would be appreciated.
Thanks,
TedI'm afraid not. Aggregates of aggregates is on our wishlist for a future
version, but for now you'd need to either have a separate data set like you
describe or you could include the counts in your base data set (and do the
max in the report).
However, since you seem to be showing only the aggregates in your matrix
(and none of the details), you could just do this:
select ResponseText, Count(SurveyID) as RespCount from Surveys Group By
ResponseText
And then your grand total would be: =Max(Fields!RespCount.Value)
In fact, you could just put that into the data cell of the matrix (since the
max of one item is that item) and then add a subtotal to the matrix.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Ted" <Ted@.discussions.microsoft.com> wrote in message
news:EF2AC082-1DBE-4DF5-854F-F671DB674D5E@.microsoft.com...
> I have a data set with a list of text values for a survey.
> Survey Response Text
> 54548 More than once a week
> 84318 More than once a week
> 81367 Once a week
> 87186 More than once a week
> 44987 Twice a week
> 84938 Twice a week
> I've put them into a matrix to display the reponse count.
> Response Text Count
> More than once a week 3
> Twice a week 2
> Once a week 1
> To do this I used: =COUNT(Fields!ResponseText.Value)
> Now what I need to do is make a one-line table/matrix with only the
maximum count value of the Response Text that looks something like this:
> Response Text Count
> More than once a week 3
> Unfortunately, I can't do MAX(COUNT(Fields!ResponseText.Value)).
MAX(Fields!ResponseText.Value) just gives me the largest string value. Is
there a way I can achieve this without creating an additional dataset just
to pull the maximum count value in?
> Any help would be appreciated.
> Thanks,
> Ted