Get the max of the aggregates

Hello:

I've been researching a likely common problem with reporting services: the inability to get an aggregate of an aggregate. One particular discussion thread comes close to solving my specific problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1

Here's my problem. I have a table that groups data per month based on Count(). I'd like to get the max(count()) -- i.e., which month has the highest count?

For example:

JAN 30

FEB 20

MAR 25

I'd like to identify the month that has the max count. In this case, I'd like to capture the aggregate value "30" as being the max value of the three months displayed.

My goal is to embed a horizontal stacked-bar chart into the table (to the left of the count() values). Various types of medical services are being counted per month: Inpatient Stay, Outpatient Service, PCP visit. The key to displaying the horizontal stacked-bar charts is to make sure the maximum value of the chart is the same for all charts -- i.e., I need to know which month has the highest count and then set that value as the max limit on the chart.

Robert Bruckner's technical article on "Get More out of SQL Server Reporting Services Charts" briefly touches on the topic of embedded charts in a table, but doesn't go into the level of detail I'm considering.

I've also come across related information from SSW Rules to Better Reporting Services. Similar to Robert's article, SSW doesn't address scaling an in-line chart based on data that is aggregated, but rather scaling the in-line chart based on the values found in a specified data field.

Ultimately, I'd like to create an in-line bar chart that appropriate shows the month of JAN as having the longest horizontal bar, and FEB/MAR having appropriately scaled smaller bars.

Thanks in advance!

--Pete

Why not create a second dataset that calcualtes the MAX of the COUNT in SQL or MDX or whatever you're using. This dataset would return only 1 row and 1 column and you can refer to this value by using =First(Fields!your_max_count_field.value, "dataset name") to scale your charts.

I realise that this is doubling up on the query and increases maintenance and that it's a workaround for a missing bit of functionality etc. etc. etc.

The point is it would work.

|||

I've thought about setting up a second dataset to achieve this, as you suggested, but can a chart embedded in a table actually reference a different dataset? i.e. -- if the table uses dataset "A", and an inline chart is displaying a stacked-bar chart using "A", can you actually set the max limit of the chart to dataset "B"? Seems like anytime I've ever attempted to drive a data region with values that are NOT part of a the same data region dataset, I get an error....

|||

There is no restriction in using values from a different dataset. The key part is to qualify the Aggregate call with a dataset name i.e.

= First(Fields!col1.Value, "some_other_dataset")

|||

Ah, I see... I'll shall give it a go. Sounds promising.

Thanks Adam,

--Pete