is the text on Chart legend can be changed?

Question

I have a requirement to show the statistic data such as sales, visitors of previous two months on a chart, of course the legend should show the name of the two months. And I decide to design the store table as: 1) Product Jan Feb

and Jan Feb can be changed for March, Apr with time goes. So, for this design, every month, the column name will be change and the dataset cannot be bind automatically I think. Is that correct?

2) Design as follow: Product M1 M2

M1, M2 stands for the previous month, and when select, use the column alias for the month name, but at this circumstance, the dataset columns will also change with time goes and I don't think SSRS chart can bind the dataset which columns name will change.

So, how to handle such requirement and is there any better way to do? I am currently use ssrs2005, is ssrs2008 can handle this?

Answer 1

So, for this design, every month, the column name will be change and the dataset cannot be bind automatically I think. Is that correct?

Correct.

M1, M2 stands for the previous month, and when select, use the column alias for the month name, but at this circumstance, the dataset columns will also change with time goes and I don't think SSRS chart can bind the dataset which columns name will change.

How will the dataset columns change with time? With the addition of M3, M4, etc? In that case, the best solution is to rotate the table as such: Product, Month, Amount.

If you can't change the table, then you will have to rotate the table yourself in the dataset query using a dynamic SQL statement. You would query the system tables for the column list, assemble a SELECT statement and execute it.