Combine Chart Keywords (like #VALY) with expressions?

• BaldNomad

  SSChasing Mays

  Points: 658

  More actions

  September 27, 2012 at 4:20 pm

  #267393

  Using SSRS 2008R2, building reports using BIDS.

  I have a column chart with week numbers as the category on the X-axis. Basically it's your standard weekly-totals graph over time. to this I have added an Average line by adding a Calculated Series and selecting Mean as the formula.

  I want to set the Legend text for the Mean data series to show a text label, plus the mean value represented by the line. #AVG has the mean I want, but I can't figure out how to combine it with the text so it comes out like this:

  Avg Vol/Wk: 1000

  If I type this in to the Custom Legend Text of the calculated series:

  Avg Vol/Wk: #AVG

  then it works, but unfortunately I need the number to be rounded to zero decimal places.

  I tried various forms of adding format specifers like this:

  Avg Vol/Wk: #AVG {D}

  Avg Vol/Wk: #AVG (D)

  but I can't seem to get this to work correctly. The documentation on using these chart keywords like #AVG is pretty sparse...and in one place it says use braces, another parentheses. It also says they don't recommend mixing expressions and chart keywords.

  I could probably write an expression instead but my dataset is currently grouped at the day level, and the chart is already calculating the weekly average for me (because my chart category group is defined using a week number column) so I thought this would be easier. If I just use Avg() in an expression I get the daily average, not the weekly average.

  I don't want to just show and format the data labels for the series because it's always a flat line on my chart, and it would show one data label for each point in the parent series, which would be pointless. I saw one solution online that simply hid the data label unless it was the last one in the series...but I'd rather just show it in the legend if I can.

  any ideas/alternatives appreciated!