To give some background I'm bringing some data from SQL Server into my excel workbook, which I'm then using in a pivot table to display a report summarising 12 months worth of data. To do this I've setup a data connection and have an excel table updating via a view I've setup in my database. This all works as expected.
Due to the issues with SQL - Excel dates, rather than a date column, the view has a year column (@Yr) and a month column (@Mnt) and I've then added one more column to my excel table where I've used =DATE([@YR],[@Mnt],1) to derive an excel date. Again this works as expected. (so 2013, 3 = 01/03/2013)
In my pivot table I'm using this 'Excel Date' column as a column label and have formatted it to show MMM YYYY (Jan 2013) formatting. Again all ok.
The issue now is that the view is a rolling 12 months of data showing last month and the next 11 months.
So April 2013 (this month) will show March 2013 - Feb 2014
This seemed to work fine when it was setup, but as we've moved into the new month and so the 12 months have shifted, it is still showing the older months in addition to the new ones, even though those dates not longer exist in the table.
So in my current report I have as column labels Jan 2013 - Feb 2014, when it should only be March 2013 - Feb 2014. So I'm displaying more columns that I wish in the report.
I'm hoping I've explained this clearly and I'm sure it's something simple that I'm missing, but if anyone can point me in the right direct it would be much appreciated.
Due to the issues with SQL - Excel dates, rather than a date column, the view has a year column (@Yr) and a month column (@Mnt) and I've then added one more column to my excel table where I've used =DATE([@YR],[@Mnt],1) to derive an excel date. Again this works as expected. (so 2013, 3 = 01/03/2013)
In my pivot table I'm using this 'Excel Date' column as a column label and have formatted it to show MMM YYYY (Jan 2013) formatting. Again all ok.
The issue now is that the view is a rolling 12 months of data showing last month and the next 11 months.
So April 2013 (this month) will show March 2013 - Feb 2014
This seemed to work fine when it was setup, but as we've moved into the new month and so the 12 months have shifted, it is still showing the older months in addition to the new ones, even though those dates not longer exist in the table.
So in my current report I have as column labels Jan 2013 - Feb 2014, when it should only be March 2013 - Feb 2014. So I'm displaying more columns that I wish in the report.
I'm hoping I've explained this clearly and I'm sure it's something simple that I'm missing, but if anyone can point me in the right direct it would be much appreciated.
