2

Date Formats in Exported Excel Files

I've had a user complaining that when they export a discovery to Excel, they are unable to filter on date columns- normally in Excel when filtering dates, they are grouped together by year, month etc, but in exported Pyramid files the dates show individually as text -see the picture

 

I have tried changing the date formats in my model but no matter what I do, the dates won't filter properly in the exported Excel file- is there a particular date format I should use that I haven't tried, or is it not possible?

 

Thanks :)

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi, wondering if anyone has an answer to this please? User is asking again!

    Like
      • Ian MacdonaldAdmin
      • Team Lead - Product Management
      • Ian_Macdonald
      • 5 mths ago
      • Reported - view

      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

      Like
Like2 Follow
  • Status Answered
  • 2 Likes
  • 5 mths agoLast active
  • 2Replies
  • 39Views
  • 2 Following