PowerPivot is awesome. I've been wondering for a long time why Microsoft didn't move Excel and SQL Server closer together; it seems they're doing so now.
However, there is still one thing I have not been able to find a way to do; I have many pivot reports on SSRS that can be rendered after the user enters a start and end date.
I have a series of fact and dimension tables on my SQL Server, among them is a Time Dimension table. In SSRS I can write SQL code that will quickly 'filter' the contents of that dimension
tbl leaving only dates between the start and end dates the user inputs. That drives the datekeys to feed data from a fact table (such as sales) to the pivot table in the SSRS report.
I've never been able to figure out how I could reproduce this functionality in Excel, was hoping there would be something in Powerpivot; but alas I do not see a way to do this.
If only I could see how to do this in powerpivot I could easily convince my company to make the move from SSRS to PowerPivot for excel.
Is there a way to do this?
(I considered slicers, but that would get really cluttered - a slicer to pick the year(s), a slicer to pick the months, a slicer to pick the days - and that wouldn't do it anyway. Consider a start date of 12/1/2012 and an end date of 3/1/2013. The slicers would be 2012/2013; 12,1,2,&3; and 1 through 31. That wouldn't produce the desired results.)
Thanks in advance for any suggestions.
(Just a bit about my skills - I'm quite proficient in SQL, learning DAX, somewhat competent in basic MDX. I'm not a VBA developer though.)
However, there is still one thing I have not been able to find a way to do; I have many pivot reports on SSRS that can be rendered after the user enters a start and end date.
I have a series of fact and dimension tables on my SQL Server, among them is a Time Dimension table. In SSRS I can write SQL code that will quickly 'filter' the contents of that dimension
tbl leaving only dates between the start and end dates the user inputs. That drives the datekeys to feed data from a fact table (such as sales) to the pivot table in the SSRS report.
I've never been able to figure out how I could reproduce this functionality in Excel, was hoping there would be something in Powerpivot; but alas I do not see a way to do this.
If only I could see how to do this in powerpivot I could easily convince my company to make the move from SSRS to PowerPivot for excel.
Is there a way to do this?
(I considered slicers, but that would get really cluttered - a slicer to pick the year(s), a slicer to pick the months, a slicer to pick the days - and that wouldn't do it anyway. Consider a start date of 12/1/2012 and an end date of 3/1/2013. The slicers would be 2012/2013; 12,1,2,&3; and 1 through 31. That wouldn't produce the desired results.)
Thanks in advance for any suggestions.
(Just a bit about my skills - I'm quite proficient in SQL, learning DAX, somewhat competent in basic MDX. I'm not a VBA developer though.)
