How to use Date Picker as a Slicer to restrict what data to retrieved in PivotTable

[jgwhitfield2]

I am not sure if this is one or two issues. I am pulling data from my TSQL DB into a PowerPivot table and the field is formatted as such: CONVERT(char(10), IVH_DELIVERYDATE, 101). The data type in the PowerPivot table is also set to Date. When I create the pivot table from the PowerPivot dataset, the date data is being presented as the "General" format. When I attempt to change it via the "Format Cell's" function in Excel, I get no change in the data format.

I am not sure if this is causing my inability to select date range based on "Year, Month, and Day or not. Ideally, I'd like the PivotTable (PowerPivot) user to use the Date Picker to just select the dates that they desire the information for. If the DatePicker is not able to be used, I am happy to settle for the DateFilter function, but right now the only thing I am getting is a listing of EVERY SINGLE date in the DataSet.

 

[jgwhitfield2]

ok, I'll give it a go. Once I add the dates table, then I do a join with my data table? Then when I add the "Date column into the pivot table in Excel I need to use the "Date" from the date table not the Data table?

 

[miguel.escobar]

correct, join the new table to the existing table using the SHIP_DATE and your dates column on your new table. Then, when creating the pivot table, use the dates column from your new dates table and you'll see how magic takes place...
try taking a screenshot but sometimes you can't capture magic in a simple picture

 

[jgwhitfield2]

Miguel,

Much your assistance has been much appreciated. I am quite new to the PowerPivot world. I am having trouble marking the date table as the "Date Table". from what I read this option should be on the Design Tab, but as this pic shows, I don't have anything that refers to "Date": https://docs.google.com/file/d/0B_grSMhsyyr6MF9fNU1za203dUk/edit?usp=sharing

My head is beginning to hurt :-\

 

[Jacob Barnett]

Are you using PowerPivot v1? If so upgrade immediately!

 

[jgwhitfield2]

Version 10.50.4000.0 is what I am running. Is this v1?

 

[miguel.escobar]

it is. Do you really need to use this version? I'd recommend upgrading if possible

 

[Siraj.Samsudeen]

The current version number of v2 is 11.0.3129.0 - this was a problem I also had. Uninstall your current version and reinstall from http://www.microsoft.com/en-us/download/details.aspx?id=29074

 

[jgwhitfield2]

My desktop guy just sent me this screenshot: https://docs.google.com/file/d/0B_grSMhsyyr6dEgybmd6T1RPOGc/edit?usp=sharing

The one he is telling me that I have is v2 and the one listed as 11.0.3129.0 is v3, according to the Microsoft signature. So, you guys are saying that prior to v3 the "make date table" function wasn't available?

I really appreciate all of the input.

 

[miguel.escobar]

[TABLE="width: 100%"]
<tbody>[TR]
[TD]PowerPivot version
[/TD]
[TD="width: 319"]Version number
[/TD]
[/TR]
[TR]
[TD="width: 319"]SQL Server 2008 R2 PowerPivot - initial release (V1)
[/TD]
[TD="width: 319"]10.50.1600.1
[/TD]
[/TR]
[TR]
[TD="width: 319"]SQL Server 2008 R2 PowerPivot - updated release (V1)
[/TD]
[TD="width: 319"]10.50.2500.0
[/TD]
[/TR]
[TR]
[TD="width: 319"]SQL Server 2008 R2 PowerPivot - current release (V1)
[/TD]
[TD="width: 319"]10.50.4000.0
[/TD]
[/TR]
[TR]
[TD="width: 319"]SQL Server 2012 PowerPivot (V2)
[/TD]
[TD="width: 319"]11.0.2100.60
and above
[/TD]
[/TR]
[TR]
[TD="width: 319"]Office 2013 PowerPivot - consumer preview (V3 or 2013)
[/TD]
[TD="width: 319"]2011.110.2809.6
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jgwhitfield2]

Removed v1 and installed v2.

Made "Calendar" table the "Date Table" based on the "Date" Column

Created a relationship from Calendar table (on Date column) to Orders table (Order Complete date) - Order table has multiple orders per day so joining from Orders --> Calendar gave me a duplicate record error: https://docs.google.com/file/d/0B_grSMhsyyr6c0dEbjdyNGZVNVE/edit?usp=sharing

Added Calendar.Date to a Flat Pivot Table and was able to access Date Filters

Attempted to add OrderData.Order# to the pivot but received an error (The command was canceled.": https://docs.google.com/file/d/0B_grSMhsyyr6ZElwRXJJTjRxMUk/edit?usp=sharing

This made me curious about the "join" so I added the joined column to the pivot table (OrderData.OrderCompleteDate). This returned every record from the OrderData table (300 distinct dates) for each day in the Calendar Table (1828 dates) 548,400 rows of date data: https://docs.google.com/file/d/0B_grSMhsyyr6dFh1ZXJYbDMtQzA/edit?usp=sharing


This makes me wonder if my I did my join incorrectly. heehee, we addressed one issue but now there is another. Feels like a Monday.