SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Filter in Excel for pivot table with cube data source not filtering


Date Filter in Excel for pivot table with cube data source not filtering

Author
Message
adzymcfadzy
adzymcfadzy
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 270
hello all,

after much googleing and setting of parameters , member values etc, i am at a dead end

I am trying to get the date filtering for a pivot table working

my time dimension is set to Time

The attribute Date os set to a Type of Date
It's key column is set to a pk value (integer
It's name column is set to a field called dim_date (Wchar but date time in the table structure)
It's value column is set to the same field above dim date (date) Data type is date, and format is short date.

I have processed and deployed my cubes, however when trying to filter using between or any of the other builtin features of excel 2010 to filter by date, it results in no results .

I'm unsure what other setting i need to set to make the filtering work properly. any help is much appreciated.
adzymcfadzy
adzymcfadzy
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 270
ok
maybe an easier question, has anyone got the filtering functionality to work in excel 2007 or excel 2010, other than in Adventure works?
:-)
mmikolyski
mmikolyski
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 3
I have the exact same problem, what gives? is this some sort of bug? I can't believe this could be a real problem and go undetected for years. this has got to be simple, doesn't it? I can get excel to recognize my datetime field with a direct sql query but not in an analysis services cube time dimension?
adzymcfadzy
adzymcfadzy
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 270
i agree, i dont think it is a bug, but i cannot for the life of me figure out why it is doing this,

will be lodging a call with ms tomorrow about it, hopefully more detail to follow, (this might be a good first blog post for me ).
richardc 90782
richardc 90782
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 4
Has anyone got this to work? I'm stuck too.

Some people seem to have no trouble at all e.g.
http://stackoverflow.com/questions/12532393/excel-pivot-table-date-range-filter
richardc 90782
richardc 90782
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 4
Fixed this problem. For anybody out there Googling: If you are having trouble getting Excel 2007 or Excel 2010 pivot tables to work with date filters (showing blank or empty or no data) from an SQL Server Analysis Server (SSAS) cube, here's how you fix it:

1 - Download AdventureWorksDW2008R2 database and attach it in on your localhost:
http://adventureworksdw2008.codeplex.com/downloads/get/372236

2 - Download, deploy, and process the Adventure Works 2008 (AW) cube:
http://msftdbprodsamples.codeplex.com/releases/view/88252

3 - Open Excel 2010 - Point to AW cube - Make a pivot table and see for yourself that date filters work.

4 - Open the AW cube - Open your cube - Compare every single property in every single part of your Date Dim to their Date Dim and make yours match theirs.

5 - Rebuild your cube.

6 - Your date filters now work! :-)



P.S. Here are some things I changed in my cube, I don't know which one exactly fixed it:

Dim:
Set Unknown member to None

Key:
grouping behaviour: discouragegrouping
instanceselection: filtered list
null processing: automatic
set namecolumn
type: calendar -> date type
datasize 100
valuecolumn : date (maybe should be datekey ??)

Date:
grouping behaviour: discouragegrouping
instance selection: filteredlist

other fields:
instanceselection: dropdown
set value column
set attributehierarchyvisible

changed all nullkeyprocessing to automatic in cube design matrix
adzymcfadzy
adzymcfadzy
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 270
wow!

interesting...

i never tried that..

thanks for the response..unfortunately i have moved on from that, could never get it working perfectly.
RonKyle
RonKyle
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7028 Visits: 3621
This has stymied me for some time as well. Based on the question, I took a relook. There are only two things required, as I found in Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables:

1. To enable date filtering in the PivotTable, first set the Type of the dimension to Time.

2. Set the ValueColumn property of the key attribute hierarchy in the time dimension to point to a date data type column in the source database. The DataType property should be set to Date.

I had already had number 1. #2 turned out to be the missing ingredient.



kiranjack81
kiranjack81
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 309
I think its the value Column that enabled the Filtering on dates
metralgia
metralgia
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 247
Thanks RonKyle! #2 was the issue for me as well.

RonKyle (10/19/2012)
This has stymied me for some time as well. Based on the question, I took a relook. There are only two things required, as I found in Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables:

1. To enable date filtering in the PivotTable, first set the Type of the dimension to Time.

2. Set the ValueColumn property of the key attribute hierarchy in the time dimension to point to a date data type column in the source database. The DataType property should be set to Date.

I had already had number 1. #2 turned out to be the missing ingredient.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search