Dates in a Cross Tab

[aap96126]

Hello

All I need to do is create a cross-tab which has the dates Nov 2003, Dec 2003, Jan 2004, ......, March 2005 as the rows in the cross-tab. My database has no such fields. Does anyone know of a formula/method which will do this for me?

Many thanks.

 

[ShortyA]

Hi,
does the database have any date fields ? If not, you could create a table with 01/03/2005...12/03/2005 and then create the crosstab from there. Are you able to provide more information on what you are trying to achieve ?

ShortyA

 

[aap96126]

Yes it does have date fields, but I need to calculate a sum and if I use any of the date fields that already exists in the database it invalidates my calculation. I am not sure how to go about creating a table with dates as you suggest.

I'd be very grateful for more info.

Cheers.

 

[ShortyA]

What sort of database are you reporting off of as this will make a difference when it comes to creating a table ?

ShortyA

 

[aap96126]

I'm no expert on databases sorry - I think it's an ODBC. Does this help?

 

[lbass]

You have to provide more information about your fields. It sounds like you have more than one date field. Please specify the date fields available to you, what datatype they are (date, string), and then explain what you mean by
"if I use any of the date fields that already exists in the database it invalidates my calculation."

You should be able to add a date as the column field, and while it is highlighted, go to "group options" and select "Print on change of month". You can then use date formatting to present the result in the MMM yyyy format.

-LB

 

[ShortyA]

Are you able to go into the ODBC Administrator (Start Menu/Run and then type in "odbcad32"). Click on the "System DSN" tab and then find your connection. Next to it will be "Driver"..what does it show eg SQL Server, DB2, Oracle etc ?


It may be that you will need to ask your database administrator to setup this table for you once but it can be re-used. This type of table is commonly used in data warehousing.

 

[aap96126]

Yes there are several datefields in my database. But they are for claims received or claims entered etc. But I need to use these fields in my calculation. For example, for each monthly date that I noted in my first post I need to find those records such that:

claim received date field <= monthly date and
claim entered date field > monthly date.

I cannot use any of the above two fields as my row data in the cross tab. I need an arbitrary date field.

I have looked as you suggested and I am on an SQL server.

Thanks for your help.

 

[lbass]

SynapseVampire has an faq on creating a periods table: faq767-4532 which might be helpful. If you cannot create a periods table, then you could use a manual crosstab where you create formulas of the following form:

//{@previousmo}:
if {table.claimreceiveddate} <= dateserial(year(currentdate),month(currentdate),01)-1 and
{table.claimentereddate} > dateserial(year(currentdate),month(currentdate),01)-1 then {table.amt}

//{@twomonthsago}:
if {table.claimreceiveddate} <= dateserial(year(currentdate),month(currentdate)-1,01)-1 and
{table.claimentereddate} > dateserial(year(currentdate),month(currentdate)-1,01)-1 then {table.amt}

It wasn't clear exactly what you intended by "month date"--the above formulas reference the last day of the month.

You would make as many formulas as necessary, place them in the detail section, right click on each and insert summaries, and then suppress the detail section. If you had planned to use a row field in your crosstab, then insert a group on that field, and when inserting summaries, specify that you want summaries for all groups.

-LB

 

[aap96126]

Ok thanks - I'll try this this morning.

I think all I basically need is some kind of calendar field, but my database doesn't seem to have this.

Cheers