Suppress Bar Chart Legend Marker

[crogers111]

CR XI
SQL

I have a Bar Chart that is showing counts for each month across mulitple years. Months of different years are side-by-side for year over year comparison. i.e. Jan 09 and Jan 10 are 2 bars side by side of differnt colors, then Feb shows, them March. so each year is a different color bar and the legend shows each year as a different color.

Withing the Chart Expert
On Change of is: @MonthName
Show Value: Sum of @Year1, Sum of @Year2, Sum of @Year3

@Monthname
MonthName({@MonthNumber}, True)

@MonthNumber
month({Table.Date})

@Year1
if year({Table.Date}) = year({?@fromdate}) then 1 else tonumber({@null})

@Year2
if year({Table.Date = year({?@fromdate})+1 then 1 else tonumber({@null})

@Year3
if year({Table.Date})= year({?@fromdate})+2 then 1 else tonumber({@null})


This report prompts the user for a date range:
{?@fromdate} & {?EndDate}


PROBLEM: I have the Chart set to 'Show' up to 3 years of data (up to 3 bars for each month) however if the user only enters a date range of say, 1 year and 5 months, the Legend still shows all 3 years' markers.

I need the Legend to only show the years included in the date range entered by the user, not the #years defined in the chart experts' "Show Value"

Is there a way to accomplish this or a better way I could go about creating this type of chart ?

 

[briangriffin]

Keep the @monthname formula, but change the @year1 formula to year({tabledate}).

In the Chart Expert, On Change Of should be @monthnumber, then @year1.

Show values: Count of {tabledate}.

This solves your legend problem, but the months are arranged by name, not number.

Go back to the On Change Of, select @monthnumber to hilite it, click Order, then Specified Order. Create specified order groupings - Jan. would be @monthnumber = 1, etc.

 

[crogers111]

That does the trick. I just couldn't see the forest for the trees on this one.

I did need to format the @year1 formula a bit to remove the decimals

totext(year({VbasLogins;1.AddDte}),0,"")

Thanks for the help !