How to filter a report by the products of a Calculated Measure (dimensional)

[Corrigon]

Hi again, I have a crosstab that is built out of a Sales Cube.

Left edge is [Parent Customers] (these can be drilled down to their child Customers).
I have measures for the [Value] of Sales in the Current Month (chosen by Prompt.). Then there is a measure for the [Value] of sales in July 2015.
A third Column Calculates the [Variance] between the two columns by simply subtracting the July2015 value from the Current Month value.
A fourth column uses a Layout expression to show the words 'Increase', 'Decrease', 'No Change' or 'No Sales' depending on whether the Variance is greater than zero, less than zero, equal to zero, or Null. (see attached for pic of the crosstab)

All of the above works fine.

However, I have now been asked if a filter (attached to a Reprompt) can be added to the report, so that the user can filter the report to only show the 'Increases', 'Decreases', 'No Changes' or 'No Sales'' Customers.  Obviously the Layout calc will not be of any use, so essentially I'm trying to segment the Customers based on whether the tuple between the Customer member, [Value] and [Variance] is >0, <0, 0 or null. These would then need to go into a set (presumably? I'm guessing here), and then be made available for the on-screen prompt.

I have no idea if this can be done or not, nor how to approach it. If this was relational, I would use a case statement in a data-item and then count the results, but this is dimensional and I'm still pretty green.

Also, I have been asked to provide a summary table that sits above the crosstab in the layout, which will show a count of the 'Increases', 'Decreases', 'No Changes' or 'No Sales''. I'm a little more confident about this one being possible, but am still stuck on how to approach it.

Thanks in advance for any replies. If you need clarification on any of the above in order to provide advice please do not hesitate to ask.

Cheers
Dave

[sdf]

sorry for this question, but will there be a problem if you base your filter by the Variance, since your layout calc is using the variance as basis.

[Corrigon]

I'm not sure about that, as I have no clue as to how to go about meeting this requirement.  :-[

It would definitely not want to lose the text description of what category the Variance is in (as the users love it).

[sdf]

by the way, how are you going to control the filtering?
Are going to use a value prompt? so for example in the value prompt you chose "Increase" the expected output in the crosstab should be Parents with Increase as text description.
If so you can use case and filter your parent ;like : (there are many ways to do this)

1. You can create a data item that actually filters for the variance.
  e.g for [Increase] , filter([Parent],[Variance] = >0)
            [Decrease] , filter([Parent],[Variance] = <0)
             and so on..
2. Create the case statement for the rows(Parent)

#case prompt ('Filter','string')
    when 'Increase' then ([Increase])
    when 'Decrease' then ([Decrease])
else... #


OR you can include all in one expression

case #prompt('Filter','string','Increase')#
when 'Increase' then filter([Parent],[Variance] = >0)
when 'Decrease' then filter([Parent],[Variance] = <0)
....
end

[Corrigon]

Thank you very much for your response sdf, I really appreciate it!

I'm heading off now but will give your proposed solution a try tomorrow.

[Corrigon]

Hi sdf - can you check the syntax I am using for a data item here please? I keep getting a parsing error.

data item1

Code Select Expand


case #prompt('VarianceGroup','string','Increase')#
when 'Increase' then filter([Parents],[Variance (Opening to Current)] = >0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] = <0)
end


Parsing error
"QE-DEF-0261 QFWP - Parsing text: case Increase
when 'Increase' then filter([Parents],[Variance (Opening to Current)] = >0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] = <0)
endQE-DEF-0260 Parsing error before or near position: 19 of: "case Increase
when"QE-DEF-0261 QFWP - Parsing text: case Increase
when 'Increase' then filter([Parents],[Variance (Opening to Current)] = >0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] = <0)
endRSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: etc etc etc.

Any idea of what I am doing wrong?

[sdf]

can you finish the statement and make sure to provide value for the "else".

[Corrigon]

Ok - I've completed the case statement (I think)

Code Select Expand

case #prompt('VarianceGroup','string','Increase')#
when 'Increase' then filter([Parents],[Variance (Opening to Current)] = >0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] = <0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
end

Still the same parsing error

[Lynn]

Perhaps you need single quotes around the prompt response. Not sure but easy enought to try.

Code Select Expand


case #sq(prompt('VarianceGroup','string','Increase'))#
when 'Increase' then filter([Parents],[Variance (Opening to Current)] = >0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] = <0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
end


[sdf]

or you can try this :

case #prompt('VarianceGroup','string','Increase')#
when 'Increase' then filter([Parents],[Variance (Opening to Current)] > 0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] < 0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
end

also might want to check your "Null", is this really null in the values or '' ?

[Lynn]

Oh yeah, I think sdf is on the right track.

I didn't notice the spaces and the order of the comparison operators. You wouldn't do "= >" but rather ">=". No space and the equal sign after the less than or greater than.

Sorry for my red herring.

[Corrigon]

Hi again (and thanks Lyn for your input as well, much appreciated).

I copied the case expression exactly as it appears in sdf's last post and still got the parsing error:

Code Select Expand

QE-DEF-0261 QFWP - Parsing text: case Increase
when 'Increase' then filter([Parents],[Variance (Opening to Current)] > 0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] < 0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
endQE-DEF-0260 Parsing error before or near position: 19 of: "case Increase
when"QE-DEF-0261 QFWP - Parsing text: case Increase
when 'Increase' then filter([Parents],[Variance (Opening to Current)] > 0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] < 0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)


So it looked like the default value of 'Increase' is where it was tripping up.

I tried Lyn's suggestion and wrapped single quotes around it:

Code Select Expand

case #sq(prompt('VarianceGroup','string','Increase'))#
when 'Increase' then filter([Parents],[Variance (Opening to Current)] > 0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] < 0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
end

No parsing error!

I replaced the existing [Parents] data item on the crosstab with [Data Item1] containing the case expression.

When I attempted to run the report however, it failed. It just spun until the 10 minute time limit we have on reports was up. (for reference, the current version runs in less than 5 seconds)

Not sure where to go from here.

[sdf]

#case prompt('VarianceGroup','string','Increase')
when 'Increase' then filter([Parents],[Variance (Opening to Current)] > 0)
when 'Decrease' then filter([Parents],[Variance (Opening to Current)] < 0)
when 'No Change' then filter([Parents],[Variance (Opening to Current)] = 0)
else filter([Parents],[Variance (Opening to Current)] = Null)
end#

just a little experiment, you can try this.. as i said before there are plenty more ways to go about this.

[sdf]

filter([Parents],[Variance (Opening to Current)] #case
prompt('VarianceGroup','string','Increase')
when 'Increase' then  ('> 0')
when 'Decrease' then ('< 0')
when 'No Change' then ('= 0')
else ('= Null')
end#)

im not sure about this but you can try

[Corrigon]

Hi sdf - tried both of the above, still get Parsing errors. Can post the content of the errors if that helps.

Thanks again for your help on this.

[sdf]

yes, it'll be better if you post the details.. if i have time i'll try to run this at my end and will let you know. I maybe missing something as well.

when you created the VarianceGroup valueprompt, have you indicated all possible choices Increase,decrease,no change ?