Formatting imported data from OLAP Cubes

[rheilman]

I posted this on the OLAP Analysis Services forum and thought that I might need to hit it from the Excel end of things.

I have an OLAP Cube built from three shared dimensions. When I reference the OLAP Cube from an Excel XP spreadsheet, the individual fields from one of the dimensions are inaccessible except from the "Hide/Show Details" commands in the pivot table. The problem is that the format of the DateTime field from the Cube/Dimension includes the zeroes in the Time portion. I'm looking for a way to make this presentable (ie: MM/DD/YYYY) in the pivot table and resulting pivot chart.

The paradox is that the dimensions and cubes appear to deal with data and not formats. In Excel where formatting should be addressed, I can't seem to "touch" the data to format it.

Any ideas?

Thanks!
Ray <><

 

[xlbo]

Not sure I understand completely - are you saying that you do or do not have a Date field in the pivottable (that you can see)? If you can SEE the field, you should be able to change its format. To do so, follow:

Right click on the field header (the grey cell with the field name)and choose "Field Settings"

Click the "Number" button

Choose "Custom" from the "Category" box and enter mm/dd/yyyy in the "Type" textbox

et voila


Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[rheilman]

Hi Geoff!

When I go to "Field Settings" for the column in question, those choices are in the diabled grey color. I believe that the reason that I can't touch the Date field is because it is a field from a shared dimension used to build the OLAP Cube that feeds the pivot table.

When I build the pivot table, the actual Date field is not availble to drag into the left side of the layout section. The date field only shows when I "Hide/Show" details on the outermost column of the dimension. This is similar to clicking on a plus to expand the pivot, or double clicking on a field to see the details.

I think that I might be caught up in the red tape involved with the way OLAP Cubes present and guard their data.

Thanks!
Ray <><

 

[xlbo]

I think it is more to do with it being a shared dimension - it's similar to an issue that happens when you use "Multiple Consolidation Ranges" to create a pivottable

Was going to say that you're gonna be a bit stuck unless you can access the OLAP cube BUT I may have a way round it. When you "Show / Hide Detail" to view the dates, can you then select that (Date) field header and choose Group & Show Detail>Group ??

If you can do this and excel recognises your dates as true dates, form there, you should be able to choose the option to group by DAYS. This should reformat the field to be a DAY specific format (removing the 00:00:00)

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[rheilman]

Thanks Geoff,

I tried it and got the "grey" Group as well.

Here's a possible solution that I've found. In the Basic tab of the Properties for the Date field in the Dimension Editor, two of the properties are Member Key Column and Member Name Column. My understanding of these is that the Key column is dealing more with the actual data, and the Name column deals more with presentation. I look forward to hearing this put more simply from someone with more experience at this.

My value for Member Key Column is the one that came by default when I built the dimension...

"dbo"."Organization_Calendar"."Date"

My value for the Member Name Column is...

LTRIM(
RTRIM(
CAST(MONTH("dbo"."Organization_Calendar"."Date")
AS VARCHAR)))
+ '/'
+ LTRIM(
RTRIM(
CAST(DAY("dbo"."Organization_Calendar"."Date")
AS CHAR)))
+ '/'
+ LTRIM(
RTRIM(
CAST(YEAR("dbo"."Organization_Calendar"."Date")
AS CHAR)))

So far it appears to present the data the way we want, while not changing the grouping and aggregation on the pivot or the chart.

Thanks!
Ray <><

 

[xlbo]

Good news - glad you found a solution. Sorry I couldn't be of more help

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[rheilman]

Better still, Member Name Column is...

CONVERT(CHAR(8), "dbo"."Organization_Calendar"."Date", 1)

...compliments of my boss.

Ray <><