Topic
  • 2 replies
  • Latest Post - ‏2013-05-13T13:28:08Z by neillrd
neillrd
neillrd
2 Posts

Pinned topic Adding a date to X-Axis in a chart

‏2013-05-08T14:58:16Z |

I am running Cognos BI Report Studio 8.4.1.  I'm trying to build a report with a Bar chart (Combination chart).  For the x-axis I want to have a label that is the combination of a string and a date.  The date is in TDW in the format 1130506123234000, or CYYMMDDHHMMSS000.  I also have this date in a table, and was able to use a CAST statement to produce a date. Here is the statement:

cast((cast((cast(substring([Performance].[ABC].[Time of Incident],1, 3), integer) + 1900) as char(4)) + '-'
+ substring([Performance].[LGR].[Time of Relocation], 4,2) + '-'
+ substring([Performance].[LGR].[Time of Relocation], 6,2) + ' '
+ substring([Performance].[LGR].[Time of Relocation], 8,2) + ':'
+ substring([Performance].[LGR].[Time of Relocation], 10,2)+ ':'
+ substring([Performance].[LGR].[Time of Relocation], 12,2) + '.000') as timestamp)

The result is a date of the format May 3, 2013 11:34:45 AM

I tried the identical string for the expression for the X-Axis, and the result was that it did all the substring parsing, and come of the casting, as the result was: 2013-05-03 11:34:45.000   Everything but the cast to timestamp appears to have been done

So my question is:

Why didn't the cast to timestamp work?

I can't try and do this using the Data Format for the field (although I tried and failed) because I need to concatenate two other attributes to the date in order to make it unique.  ie. Name || System || Time

Thank you

Bob

  • vikky440
    vikky440
    1 Post

    Re: Adding a date to X-Axis in a chart

    ‏2013-05-10T07:24:30Z  

    HI Friend,

     

    Your question is not clear.

  • neillrd
    neillrd
    2 Posts

    Re: Adding a date to X-Axis in a chart

    ‏2013-05-13T13:28:08Z  
    • vikky440
    • ‏2013-05-10T07:24:30Z

    HI Friend,

     

    Your question is not clear.

    I apologize for the lack of clarity.  I will try to again.  I am pulling a date from a DB2 table.  The date is in the format CYYMMDDHHMMSS000.  I was able to use the statement above, with the embedded CAST functions, to show it in a standard date format, May 3, 2013 11:34:45 AM.  I did this in a table in my report.  

    I want to do  the identical formatting, to the identical field, as the X-axis value on a bar chart. When I used the same expression it did not give me the same result, instead it produced 2013-05-03 11:34:45.000,   This seems to indicate that it did all the parsing, but did not do the CASTing to a timestamp, as it did in the table.

    My question is, does anyone know if there is something I have to do differently if the data item is being used as an x-axis label, as opposed to being a data item in a table?

    Thank you,

    Bob