show highest of a multi-select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

show highest of a multi-select

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-16 : 09:51:30

I have a matrix which displays data for 2 years. The year is a multi-select. The Year is a column group, e.g.

Value_A
Yr_1 | Yr_2
98.7 | 45.2

I also am displaying a value to the left as a row group. This is the expression I want to edit. I want this value (Value_B) to only show for the most recent year (Yr_2).

Because it's value is shown for both years the following is how matrix looks:
................|Value_A
Value_B.....|Yr_1 | Yr_2
100...........|0.00 | 45.2
30............|98.7 | 0.00

I want it to look like this:
................|Value_A
Value_B......|Yr_1 | Yr_2
100...........|98.7 | 45.2

The expression for Value_B is: =Fields!Value_B.Value.
Can I edit this expression to show value associated with the highest field chosen in multi-select?
Note: not the highest Value_B, but the Value_B for Year_2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 10:04:43

SELECT MAX(Col1), MAX(Col2), MAX(Col3)
FROM ....

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-16 : 10:47:04

Is that not SQL? I need an expression in Report field / Group to show a single item from the multi's selected on report.
I can merge them in a text box with: Join(Parameters!Value_B.Value, ","), but I need to compare them and get highest. Are the selected items stored in an array etc I can access?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-16 : 10:59:24

I think I've answered my own question - put into a hidden text box and parse its contents using =ReportItems!txtYearsChosen.value
Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 11:25:24

quote:
Originally posted by darkdusky

I have a matrix which displays data for 2 years. The year is a multi-select. The Year is a column group, e.g.

Value_A
Yr_1 | Yr_2
98.7 | 45.2

I also am displaying a value to the left as a row group. This is the expression I want to edit. I want this value (Value_B) to only show for the most recent year (Yr_2).

Because it's value is shown for both years the following is how matrix looks:
................|Value_A
Value_B.....|Yr_1 | Yr_2
100...........|0.00 | 45.2
30............|98.7 | 0.00

I want it to look like this:
................|Value_A
Value_B......|Yr_1 | Yr_2
100...........|98.7 | 45.2

The expression for Value_B is: =Fields!Value_B.Value.
Can I edit this expression to show value associated with the highest field chosen in multi-select?
Note: not the highest Value_B, but the Value_B for Year_2

isnt it anough to add a rowgroup on value_B and take MAX()?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-16 : 12:05:25

I tried putting Max(Year) into the Group expression of rowgroup Group_Value_B but I got an error saying cannot use aggregates in group expressions.
Out of nowhere I put "=(1=1)" into the group expression and it works!

I'd been trying:
=IIF(Fields!Year.Value=(Join(Parameters!Year, ",").Substring(0, 9)),Fields!Value_B,Nothing)
- the two parts worked seperately but not together.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:17:34

quote:
Originally posted by darkdusky

I tried putting Max(Year) into the Group expression of rowgroup Group_Value_B but I got an error saying cannot use aggregates in group expressions.
Out of nowhere I put "=(1=1)" into the group expression and it works!

I'd been trying:
=IIF(Fields!Year.Value=(Join(Parameters!Year, ",").Substring(0, 9)),Fields!Value_B,Nothing)
- the two parts worked seperately but not together.

not group by maxyear but put Value_B as group value

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-19 : 05:34:28

The "=(1=1)" gives correct format but gives the lower parameter rather than the higher. I tried changing order in dataset but no effect.
Value_B is a group value. Entering Max(Value_B) in the cell expression gives same output as without it.
I was not able to use textbox value in Group expression, so I created a calculated field (Top_Year):
=Join(Parameters!Year.Value, ",").Substring(0, 9)
and in cell expression of Value_B I used:
=IIF(Fields!Year.Value=Fields!Top_Year.Value,Fields!Value_B.Value,Nothing)
This correctly shows the highest year and blank cell for the lower year, for Value_B - so it still has the extra row which I want to remove.
And when I put same expression into Value_B's group expression there is no change.
So my matrix now looks like this:
................|Value_A
Value_B.....|Yr_1 | Yr_2
100...........|0.00 | 45.2
(Blank).......|98.7 | 0.00

I want it to look like this:
................|Value_A
Value_B......|Yr_1 | Yr_2
100...........|98.7 | 45.2

Do you know why a group expression for Value_B of =(1=1) works? This is correctly merging the 2 rows but not for the correct Year.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-19 : 07:13:15

Found a work around.
Deleted the Value_B cell (so the grouping also) and concatenated its (highest) value to cell to its left:
=Fields!type.Value & " " & Last(Fields!Value_B.Value)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources