Get dimension value on each row

[#1]

Hi everyone,

I want to achieve the following but just cannot think of how to do it - am sure its probably very simple and obvious!

I need to display a table like the following; however I need the 3 Dimensions to always display ALL values, regardless of the selections the user makes.

Initially I brought in this dimension table and linked it to the FACT in my data model, but the minute any selections that were made where no FACT data exists, then those rows disappeared off this table, even with "Show All Values" selected.

Dept	Area	Ratio	Revenue	Margin
X	A	1.0	234.00
	B	2.5	535.00	54.00
	C	3.5
	D	4.5	3,534.00	4,654.00
Y	A	5.0	1,344.00	456.00
	B	5.5		788.00
	C	6.0	54.00	2.00
Z	A	7.0	54,353.00
	B	7.5
	C	8.0	3,541.00
	D	8.5	788.00	354.00
	E	9.0	65.00
	F	9.5	654.00	345.00

So to try and fix the issue above I brought this dimension table in as a data island table. So it was no longer linked to the FACT, instead I used Set Analysis to take the users filter selections into account within the measures, which are expression variables.  This way user selections did NOT impact the display of the table; they only impacted the values displayed in the data cells.

This works perfectly however, its quite a bit more work maintenance-wise as I have to create a measure variable for every combination of Dept and Area.

e.g.

vRevenue_DeptX_AreaA = sum({$<Dept={'X'},Area={'A'}>} [Revenue]);

vRevenue_DeptX_AreaB = sum({$<Dept={'X'},Area={'B'}>} [Revenue]);

etc.

Ideally I'd like to be able to make these expression variables more dynamic so that I only have to define them once and when the variable is evaluated for each row of the pivot table, make it pick up the correct value for Dept and Area for the set analysis expression, from those dimensions on that row i.e. something like

vRevenue_Dept_Area = sum({$<Dept={'$(vThisRowsDeptValue)'},Area={'$(vThisRowsAreaValue)'}>} [Revenue]);

However this is where I hit a wall - is there anyway to define a variable to determine the value of a dimension for the current row of a dimension in a pivot table like this?  It's like above/previous only for the current row.

I've tried just referring to the dimension's name, with various combinations of = and dollar sign expansions etc, but to no avail.

i.e.

vRevenue_Dept_Area = sum({$<Dept={'$(=Dept)'},Area={'$(=Area)'}>} [Revenue]);

vRevenue_Dept_Area = sum({$<Dept={'=Dept'},Area={'=Area'}>} [Revenue]);

vRevenue_Dept_Area = sum({$<Dept={'=$(Dept)'},Area={'=$(Area)'}>} [Revenue]);

etc.

If anyone could help - it would be brilliant.. even if its just to say "no it cant be done!"

thanks

Roz

[Carlos_Reyes]

I'm not sure If Iget the whole picture of what you're trying to achieve, but if you want to always show all the values in a field, why don't you use { ' * ' } ?

For instance:

sum( { $ < Dept={ ' * ' } , Area={ ' * ' } >} [Revenue] );

That will evaluate the correct Revenue value for each row but will always show all values for fields Dept and Area disregarding any selections you may do on those fields. However if you do selections on other fields, only related dimension combinations will be shown, as by default.

Other than that, I think you will need to use 1 in Set Analysis to disregard all selections and then define which fields must be taken into account in order to evaluate the expressions... however when you select values in those fileds, rows will also be reduced... unles you use the asterisk method I'm suggesting...

Hope it helps!

[#3]

Thanks for your answer, not sure it's what I need ..

Basically regards the sample table I pasted above, I want ALL values/rows for Dept and Area to be shown at ALL times. So if a user selects some values, which results in NULL values for some combinations of Dept/Area, then I want that row to remain in the table with a null value - e.g. in my example above see rows Dept X/Area C and Dept Z/Area B

I don't follow how adding the syntax Dept={ ' * ' } to my set analysis expressions would force the table to show all rows? I thought this syntax just tells the set analysis expression to retrieve data for all members of that set.

thanks!

Roz

[Carlos_Reyes]

Exactly, the * indicates that all posible values for that field must be selected, however it's POSSIBLE, not ALL ALWAYS.

There is probably a way to do what you need using Set Analysis... probably... but what it's eassier is to add a expression with 1 as value... that will be valid for all rows.. so al rows wil be shown all the time.

Try:

sum( {1} 1)

Then.. you only have to camouflage the expression/column setting the label and text color so that it doesn't appears to be there.

[#5]

Adding the sum({1} 1) worked a treat.. thanks so much for that workaround!

I suppose I wasn't very clear in that I didnt want all values of that dimension to be physically selected in a listbox, nor did I want the expression to be calculated for all values of the dimension - I just wanted all values to be displayed in the table at all times.

On an aside, going back to my first post - do you know what syntax to use to get the current row's value for a particular dimension, within a set analysis expression?

Using a simple example - say I have a data island table like below in my model which is not linked to any other table in the model.

ShipDate	ShipQty
16/03/2013	15
25/03/2013	22
26/03/2013	23
30/03/2013	14

I then have the following pivot table and I want to add an expression to this that displays on each line the Shipping Qty where the current row's OrderDate =ShipDate.

The last column "Expression" shows what the results would be.

Customer	OrderDate	OrderQty	Expression
J Bloggs	16/03/2013	13	15
	18/03/2013	12	0
T Murphy	16/03/2013	15	15
	25/03/2013	22	22

I have a sample app to demonstrate this further but cant see where to attach it to this reply - so I attached it to the original message I posted.

FYI I know I could link the tables on date or through a link table, which would automatically create associations and filter the data but for my own real application I cant do this.

thanks

Roz

[Carlos_Reyes · Accepted solution]

I don't think Set Analysis may help in this case since it's calculated once per chart... but a simple IF should work.

Review the attached.

[#7]

Thanks again Carlos, for your reply.

I was using a long IF statement as you suggested but just wanted to check if there was a more efficient method of doing it such as with Set Analysis - but your statement regards Set analysis being "calculated once per chart" clears that up..

cheers