displaying the entire month when month starts on a weekend

[QueSpr04]

Using CR10 and ODBC[RDO] connection

Can anybody tell me how to report a date from the database that has no data? Meaning, the office is only open from Monday thru Friday so only data is collected on these days. I am trying to display on the report "Report for: August 1, 2004 To August 31, 2004", but of course August 1st is on a Sunday. I have been using the formula dateserial(year({cr_del_rpt.e_date}),month({cr_del_rpt.e_date}),1) for the first day of the month which starts on a Sunday, and dateserial(year({cr_del_rpt.e_date}),month({cr_del_rpt.e_date})+1,1-1) for the last day of the month. But when I select the program to run for only one day,(i.e. only for the day of August 10th) it will not report that day.

 

[synapsevampire]

I assume what you mean is that you don't have data for 8/10, so nothing is returned.

Here's a FAQ on resolving this long term:

faq767-4532

If you don't have data, you'll either have to fake it using an arry to build dates, or create a table.

By the way, saying ODBC connection is helpful, but you might want to include the database type and version, it's more important.

-k

 

[QueSpr04]

synapsevampire, we are using an informix database for our system. The data is given to me thru a ODBC[RDO] connection. And there is data on the 10th, but I guess what I am trying to say is if I choose to run the report for the entire month, the date would have to reflect the entire month including the weekend dates that have no data. In other words, the report header would say,"Report For: August 1, 2004 To August 31, 2004." But of course August 1 is on a Sunday which has no data. So the program, of course, goes to the next date that has data. But because of auditors that will review these reports they must have the entire month displayed even if it starts on a week end. Hope this clarifies the problem a little better. Any sugestions?

 

[synapsevampire]

I'm still not sure what you want, you say that it displays the entire month for the auditors, what is it that you want it to display?

To set the report up to use parameters, so that you can pass dates through as the criteria, create a date range parameter (Right click parameters in the field explorer and select New), make it of type date, select Range.

Now in the Report-<>Edit Selection Formula place something like:

{table.date} = {?MyDateParm}

Now to display the range selected use:

Minimum({?MyDateParm})&" thru "&Maximum({?MyDateParm})

If you want to show that they selected only 1 date, use:

If Minimum({?MyDateParm})= Maximum({?MyDateParm}) then
"Date selected = " & Minimum({?MyDateParm})
else
"Dates selected = " & Minimum({?MyDateParm})&" thru "&Maximum({?MyDateParm})

Hope this helps.

-k

 

[QueSpr04]

That worked great but only one small setback, I cannot use parameters on my reports because the users select the dates from the .Net program. So the date field filters down to my program. So is there a way that I can display the dates in the Page Header that comes doe from the .Net program?