Using Excel Filtering on a Cognos Genereated Excel File

[jv_oz]

Hi all,

A client of ours wants to be able to run some of their reports to output as an Excel file. That's fine, however columns with dates (originally string measures in a TM1 dimension) output as strings rather than dates. They can filter them, however the filter displays every date as a list, rather than the usual Year, down to Month, down to Day - which is far easier to use.

Any ideas gurus on how to make this work?

Thanks

JV

[BigChris]

Well...the obvious solution would be turn them into dates in the model. Is that possible?

[jv_oz]

Hi @BigChris. Thanks for your suggestion. I don't think I could turn them into dates in the model because they are all currently measures in a TM1 dimension. I could make them all dimensions, however that would mean adding three new date based dimensions which I believe would make the cube too complex.

[sjdig]

Would this work?

Code Select Expand

cast ( ( substring ( cast([Field] as varchar(10)) , 1 , 2) || '-' || substring ( cast([Field] as varchar(10)) , 3 , 2) || '-' || substring ( cast([Field] as varchar(10)) , 5 , 4) ) as date )

In our system, a flex field is stored as a string rather than a date, using this allowed the output to be a date rather than a string. You'd just have to replace [Field] with your relevant item.

I probably actually found how to do this right on these forums from someone else.  :)

[RickHenderson]

I was looking at dates exported from Cognos 10 into Excel just yesterday, because I've had problems with Excel date formats before. Excel date formats can be tricky because the acceptable date formats in Excel differ depending on the localization (country/ English US / English CA / Chinese etc.) of the user's computer.

For example, I've configured a report to display and export a date in 'YYYY-MM-DD' format which looks fine, intuitive, and human readable. When imported, opened, or typed into an Excel field, Excel (on my computer) will convert the date to DD/MM/YYYY but recognize it as a Date format. So date formatting and operations work correctly.

So your problem may be as simple as choosing a different date format for the report unless I'm over simplifying your problem.

Sig concept: Remember the only data type in a .CSV file is "Text".

[jv_oz]

Hi all,

So the resolution was rather complicated. The dates are stored in string elements in the TM1 measures dimension. I initially thought that I would just dissect them into the component parts of Day, Month and Year and then re compile them into a Cognos date function. This, I thought woudl be relatively straight forward. It wasn't. I needed to all sorts of case and cast statements to do multiple conversions of each element. When I am boakc on site I will paste the actual code I used to resolve it.

JV