Map an aggregated measure to a value on chart axis

[Dineshpullagura]

Hello,

I am using cube as a data source. I have a chart report(line chart) displaying a measure whose values ranging from 1-4 across a dimension. The aggregation rule set for the measure in average. In my chart i am getting the values on y-axis from 1-4 as expected. Is there a way i can change the scale on my y-axis to low,medium, high and very high instead of 1,2,3 and 4.

I am looking for a way to map the aggregated measure to a string.

Please suggest.

Thanks for your time.

[TheFrenchGuy]

Hi,

had you tryed to creat a data item which convert your values (1/2/3/4) in your output needs (case when =1 then Low etc.).
Then you change report expression in properties in data item value and select this item.

[Dineshpullagura]

Yeah, I have tried the case statement. But  it is not returning any data. I am using the data item containing the case statement as the default measure.

[TheFrenchGuy]

And have you tryed a case statement within a report expression source type.

Something like :

Code Select Expand

case
when (substring ([Query1].[Data Item1],1,1)='1') then ('Low')
when (substring ([Query1].[Data Item1],1,1)='2') then ('Medium')
when (substring ([Query1].[Data Item1],1,1)='3') then ('Hard')
else ('NA')
end

[Dineshpullagura]

I tried something like this ..


Data Item :
case (_round ([Category],0))
when 1 then 'Low'
when 2 then 'Medium'
when 3 then 'High'
when 4 then 'Very High'
end

and i am using this data item as the default measure in the report. I am getting the expected output when i check the tabular data of the query. But my report is showing nothing.

My apologies, i didn't get that "within a report expression source type". Please explain.

Thanks a lot for your time.

[TheFrenchGuy]

you can find the report expression in properties panel (as see in attachment).

If you replace in my previous expression your real data item, it should work :

Code Select Expand

case
when (substring (_round ([Category],0),1,1)='1') then ('Low')
when (substring (_round ([Category],0),1,1)='2') then ('Medium')
when (substring (_round ([Category],0),1,1)='3') then ('High')
else ('Very High')
end

You perhaps get issues about the "substring(_round" cause round refers to number and substring to text. But try my expression before you had "round" to test.
Report expressions are hard to understand, don't worry.

[Dineshpullagura]

Hi,

I replaced the query in my data item with the expression you've provided. As you said, i got an error using sub string because my category is an integer. I am getting the expected output when i check the tabular data of my query even without sub string.

The value i am trying to map here is a measure, and i don't see the option of report expression under the default measure axis for a chart. If i try to use the Data Item with that expression in the default measure axis , my report is blank.

Please suggest.

Thank you for your time.

[TheFrenchGuy]

Can you provide a report studio chart screenshot pls ? In order to see how to proceed

Thank you