Counting Dates..

[funkmonsteruk]

I have a BO report which calculates the number of items produced by each department during each month. I'm using a date field which comes from a database in dd/mm/yyyy hh:mm format, which i have formated in the universe to be mmmm/yy. At the end of each section i'm countuing the number of items to give a figure for the number of items produced each month....Simple

However, the problem i'm having is despite formating the date object in the universe to be mmmm/yy, when i set the date as the master cell results are being split into there dd/mm/yyyy hh:mm sections.

This has meant that instead of having a neat sections representing the items produced by each department during a month, i'm getting lots of very small sections which show the number of items produced by each department during each minute......

Any suggestions....?

 

[blom0344]

Hello funk,

Internally there still seems to be a timestamp format present. Try creating a variable that sets the date to a string, like FormatDate(<date>,&quot;dd/mm/yyyy&quot and use this in the section. Formatting to a string should remove the time component.
I'll leave the solution at universe level to someone else, sorry, got too much on my hands for the moment ...... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[skrandel]

You don't want to bring back the time portion. Simply formatting the column that way doesn't do the trick.

I don't know what your database is. If it's Oracle, just do trunc(datecolumn)and the time will be set to 00:00:00. I'm sure all of the databases have some way of doing this. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[promero]

The solution at universe level can reduce the number of rows returned by the database (using SQL agregation), improving BO performance.

There's also solution at report level. Create a new variable called <Year/month> with the following expression:

=FormatDate(<Date>, &quot;yyyy/mm&quot