Hi, i have a problem.
I am creating a series of 6 subreports in a main report where the data is displayed as grouped by year and month from 2010 onwards. So first group is year(shows 2010 and 2011) with a yearly chart, second group is month(shows the 12 months for 2010 and 5 months for 2011) with a monthly chart and the metrics for those.
I have a new request for showing the top graph as monthly and not yearly. So i created a year-month field in the command statement using the date field and created the stacked bar chart.
Worked perfectly. shows 17 bars from 2010-01 to 2011-05.
Now my problem is that as the months will go by, the chart will become unreadable. So i just want to limit the chart to last 12 months. I cannot use select expect for the rolling 12 months as that will limit the whole subreport to last 12 months. I j ust want the top chart to be year-month for last 12 months. And then i want to see data grouped by year and grouped by months underneath.
I approached it in two ways.
One i created formula for
l@ast 13 months as follows:
if {Command.DT} > dateadd("m",-13, currentdate) then {Command.MM}
but what it did was give me an extra bar at the beginning for the data that i have skipped. So i changed it to
if {Command.DT} > dateadd("m",-13, currentdate) then {Command.MM} else "2099-01-01" and now i have my chart but i have an additional bar for 2099-01-01. How do i get rid of it?
I tried another way where i will create a new metric which is 1 if @last 13 months is 2099-01-01 else 0 and then put it in the chart and summarized it as maximum. then sorted by Bottom N where bottom N is 12 and got the results. This worked fine as in it got rid of the extra bar and then i could format it in white such that the line does not show up. the problem is that that messed up the order of the year-month in my x-axis. what is the point of taking out that bar if now my 2010-04 shows up before 2010-01?
I am at my wits end to solve this. any help will be most appreciated. I can get the last n months as i want but since i am not using it in the select statement, i cannot get rid of the unused data.
How can i say that i have last 17 months but get me only last 12 months and get rid of the rest, not show it up as some other bar?
IP Logged
