Error Trying to Format Date

[dxwallace]

I have a page set containing a page. The query for the page set contains a field representing a date, as in 20070614.
I can move the field into the page header but I am having trouble figuring out how to format it into  June 14, 2007. Can someone tell me how it's done


thanks

[JoeBass]

You should be able to highlight the field on the report, choose data format, choose Date/Time and formatting options.  RS may have trouble if it doesn't think the field is actually a date field.  You can check the field type in FM.

[dxwallace]

the field is defined as a date in FM. When I highlight the field and attempt various Date Formats, Long Name, Short Name, YMD, MDY, nothing happens. The field continues to display as 20,070,617

[JoeBass]

Any chance RS formatting is competing with formatting you set in FM? 

[wilbolite]

I think that even if you've set a data type in Framework, it doesn't override the actual data format in the underlying database.

I've got a framework built around a mainframe DB which has tables with date values all stored as long integers (just like your 20070501 example).  I had developed some functions to transform this into an actual date value, but found that this had an enormous performance hit when I attempted to perform filters by date.  This is because the DB engine literally has to go through every record transforming the integers to dates to match against the filter date value.

The final solution was to create a nice Date Dimension table on the source system where for each day a record existed with various formats of the date (Date, yyyymmdd, yymmdd, mmddyy, etc).  After placing indexes on each of the columns, I then used JOINS in my framework to link the numeric date  in the source data table with the numeric date in this Date Dimension table.  Then I could query off of the Date Dimensions' actual Date field and get perfect & quick results.

I hope conceptually, this makes sense to you - as the actual implementation will vary from system to system.

[JoeBass]

I didn't know that you could set the data type in FM.  In my experience, it just reads the database to determine the type without a means to override it.  I was referring to setting the format in FM, since dxwallace confirmed that its a date type.  I didn't know if it might be possible for formats to get competitve between FM and RS.

Your lookup table sounds like a good idea for your situation Wilbolite.  Another approach might be to convert the date parameter to an integer in the filter and query with that.   This would eliminate the need for the additional table, but each of the non-date dates in the resultset would need to be converted to date fields to get the same results.

[wilbolite]

I tried the approach of converting a date parameter to an integer prior to setting the filter, but no matter what I did, the DB engine still wanted to evaluate each and every record in the table.

In the WHERE clause, you'd see something like:

WHERE integer_date_value = fnConvertToIntDate(date_parameter)

Obviously, this is something of a simplification, but it's essentially what I had set up - yet the DB engine would still read all records.  Only by joining to a date cross reference would I get the performance I was looking for. 

For reference purpose, this is a Framework over an iSeries DB2 engine - so the same discussion might not be true for SQL Server or Oracle.

[JoeBass]

Yeah, that is strange.  I don't know my DB2 but, did you try setting the processing as database only with the filter you showed? 

[wilbolite]

I tried it both ways, didn't make a difference.  Still, I think it's best practice to utilize a Date Dim table - it provides so much more flexibility in terms of reporting on calender months, fiscal periods, week numbers, etc.

There's a couple of options in both FM and RN that I've come to believe are decoration only.  Especially anything concerning rollup processing (see my earlier post on that subject)  :}

[JoeBass]

I'm going to have to do some experimentation when I get a minute.  I swear I've used the formula on filter approach before. 

As far as best practice, the lookup table is a good idea, we have used them and you may be right but, avoiding an additional table to maintain and keeping FM as simplified as possible is more to my liking.  For many projects, I've created views and stored procedures to avoid FM's joining techniques entirely. 

About the decorative buttons, I haven't experienced that.  There have been a number of occasions where things don't perform as I expect them to in RS.  They've all been solvable by luck and by changing the order that I assign the grouping / aggregation methods.

Good to hear your thoughts on this.  Thanks.

[dxwallace]

I think this could be some issue pertaining to the underlying data in the database, That is because I have verified that I can change the format of the field in FM and RS. In FM I can change it to a text field and see it on my report as text. In RS I seem to be able to manipulate it as a numeric field, changing the separator from commas to dashes and periods. I can make it a percent or add decimal places. The end I formatted the field in FM, setting the "use thousands separator" property to yes, making the group size equal to four, and making the separator a dash. This puts a dash instead of a comma after the forth digit, this the date in the header will look like "2007-0617". That will be enough for the user, he's not into aesthetics.

[ntmyslftdy]

I had a silmilar issue, and it involved the data transferring in as text. To combat this, we put the following expression under Data Item:

to_date([parameter],'yyyymmdd')

Works well and allows for date formatting to work.

[dxwallace]

I got the following message when setting the "to_date" adn running the page

Initially, in data source type(s) 'pattern', function 'providerQuery' is not supported in 'OlapQueryProvider'. After decomposition, in data source type(s) 'OL', function 'fn:to_date' is not supported in 'RelationalQueryProvider'.

Any Ideas what that is?