Referencing a specific column header in a matrix

Question

Referencing a specific column header in a matrix

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

I've done things like this in two ways. The first is to just do the cross tab and everything else in the query and use a table to output it. You get a bit more control over the groupings that way...

Not 100% sure on what you're asking, but if you just want to divide a single number (a total of all clients) you could assign it to a hidden textbox that you could reference.

Additionally you could look at using some of the aggregate functions based on different scopes of your data. Instead of just using the sum(field, current scope) default you could specify sum(field, next higher scope) or something along those lines. It all depends on how you have your group and scopes setup.

Hopefully one of the above gets you moving in the right direction.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

terrynelson711 SSC Rookie Points: 41 More actions · Answer 2

Luke,

Thanks for your ideas. I think when you say use the cross tab you mean not use a matrix? I can't really do that becuase the number of columns in the report is dynamic, depending on how many progrms there happen to be at the time. (Unless I'm not understanding what you meant in that idea?)

Also, the total number I use as a denomintor is also dynamic, depending on how many counselors there are and there's a total for each counselor.

I will look more into the diferent scope idea. I've never used that before and don't know much about it. Do you think I can refer to something like the last scope - referring to the last column's cell contents? For each row, that would be the number I want to use as a denominator - the last column's contents for that row.

Terry

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

When I say do the crosstab (or pivot) in the dataset I suppose thinking back on it that most of the reports I've done that way have had mostly static columns (regional offices, fiscal quarters etc) and the rows varied not the other way round. So I can see that as a problem.

Here's a link to using the built in aggregate functions that may help. http://msdn.microsoft.com/en-us/library/ms155830.aspx Since you have a row group of Counselors I would think you could use that group as the scope for a sum function to use as your divisor.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

terrynelson711 SSC Rookie Points: 41 More actions · Answer 4

Luke,

I have a column group at the far right with the counselor totals. I would like to use those totals as my denominators. I assume that can work as the scope for the sum? I will take a look at the link you sent me.

Thanks!

Terry

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

While I don't believe you can reference the total itself (at least in 2005), by referencing the appropriate scope you'll get the same value. Just make certain you account for any divide by 0 issues you might come across.

-Luke.,

To help us help you read this[/url]For better help with performance problems please read this[/url]