Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula for a date equaling a number?

Status
Not open for further replies.

Crystalguru

Technical User
Oct 4, 2001
303
US
Hello,

My report allows users to select ONE date. From that date, I would like the corresponding unit_count to appear. For instances:
if user selects {?Start Date} of 2/9/2003, then give me the unit_count for that day.

Any ideas?

 
In the record selection formula (Report->Edit Selection Formula->Record) place something like:

{Table.datefield} = {?StartDate}

Now the rowset is limited to just that day, so you can display the record, or summ the records if there are more than one.

-k kai@informeddatadecisions.com
 
Ah yes, if it was a date field...

Here's what I'm looking at. The fields unit_count is a number field. In plain english, this is what I need.

When the user selects {?Start Date} print the corresponding unit_count for that day on the report. But DON"T limit the data.

???
 
Hmmm, are you saying that you don't have a date field, or that the date field is not a date type, or???

If you don't have a field that represents a date in some fashion, obviously you can't do this, so I'll assume that what you're trying to say is that you have a field that contains date information, but needs to be converted.

I understand that you want to show a number, and now understand that you want to not limit data, but you do want to show a number/summary for just a given date.

For future reference, supplying the columns to be used, their type, the versions of the database and Crystal is very helpful.

I'd create a formula to convert the date (or use a SQL Expression, that's much more efficient). Now you can reference the formula as a proper data type.

Date(YYYY,MM,DD) is a standard type of conversion, not knowing whether you have a numeric, or string, I won't go any further until I learn some specifics.

-k kai@informeddatadecisions.com
 
I apologize for such horrible information. I have been staring at this ALL day...

Here's some additional info.
CR 8.5
Oracle 8i
My data comes from a view. I am working with a cross tab.
Here's what my crosstab looks like:
date_day fields.
Name unit count 12/29/02 12/22/02 12/15/02
Lease up 2624 93.5%
2869 90.2% 90.7%

As you will notice the UNIT_COUNT field is a number.
Name=string, date_day= datefield.
My selection formula calculates 12 weeks from the selected {?Start Date}.
{OCCUPANCY_WKLY_TIER_V.DATE_DAY}in {?Start Date} to dateadd("ww",-11,{?Start Date})

Now, what I would like to happen is- when I select a start date of 12/29/02, the unit count would be 2869. Is this possible?

Since I have a crosstab I am unable to do a max. which would work...any ideas?
 
Ahhh, I see, it's a crosstab...

You are a bit shy about details ;)

Since you have the proper amount showing, I can only assume that you mean that you want only the 12/29 unit count.

I suspect that you may have to use a subreport for the crosstab and limit the rows to just that date by passing the parm, providing what you seek is a single row of data for just one date.

-k kai@informeddatadecisions.com
 
Ah yes, the lovely crosstabs, it has ruined many marriages!... ;)

At this time, I don't think this is something that can be done. The report looks fine, except for these dates where the unit_count is different. You run it just for 2003 and there's one line of data. I think my manager may have to "deal with it" or he can work on it...

Quick question while I got you here..Similiar crosstab, but with dates going back a couple years. The crosstab runs landscape across multiple pages(horizontally), is there any way to get the column heading on each horizontal page? Just thought I would ask.

Thanks for all your ideas!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top