Hi Sophie,

When working with dates in calculations in IMDB or other SQL database based model, Pyramid makes use of a SQL Date type field that represents dates as an number (usually the number of milliseconds since a base date). This number is used internally in date functions.

However, the caption of the Date member can be formatted in any number of ways, default being YYYY-MM-DD.

When we export the grid of data including a date, it is the text string "YYYY-MM-DD" that is exported, like "2022-06-21" etc., rather than this internal number representation.

Excel interprets this as a string and not a date, and hence does not apply it's internal date handling logic in how it represents the dates in filters etc. It just treats them as series of names if you like. In fact, Excel uses a similar internal number format for its date types, although this is not consistent with the SQL standard.

In order or Excel to handle the strings as dates, they would need to be converted to Excel Date formats. Excel does this automatically if you type a date into  a column that is formatted as a Date, but currently we do not set the Excel data type as Date when we generate the spreadsheet.

Therefore Excel treats the column as a column of text strings and shows them accordingly  in the Filter dialogue. You can convert the string to a dates in Excel using the Excel function DATEVALUE():

 

However, there is a much better way to get your Pyramid report data into Excel automatically and where it can be refreshed with  the click of a button and that will result in date columns being recognise and managed as dates and not text strings.

That is using the oData link for the Pyramid report  in question.

From your saved report tab or right click on the report  in the CMS, select the oData Feed URL for that report:

In Excel, open the oData access dialogue from the Data ribbon:

Paste the oData Feed URL from Pyramid into the dialogue and hit OK:

At this point you may be prompted to log into Pyramid. Use your usual credentials if so. You will then see the currently defined oData links. Select the one you just entered and you will see a preview of the data:

Chose "Load" and the data will be loaded into your worksheet. Setting a Filter  on the Date column you will see the dates represented grouped as expected for dates:

 

The great thing about using oData feeds is that once they have been set up, you can trigger the running of Pyramid Discover Queries directly from inside Excel through the Data Refresh ribbon option:

This will run the query, download the data and replace the old data with the new, also respecting any changes in the Pyramid report like added measures, rows or column dimensions.

If you do not see the oData URL Link in the Discover tab or CMS right click context menu, then your organisation has not licenced the oData option. oData feed enablement is a paid for Pyramid server option. Please contact your account representative if you would like to upgrade to use this feature.

Hope that helps.

Ian