Reporting off of a subreport field

[hhiebert]

The way my database is set-up, the database date is stored within the database in a table called system. This table has no relationship with any of the other tables.

I need to use this date to determine what records to retrieve from my database.

How is...and is...this possible?

NOTE: I'm working with Crystal Reports 9 and SQL Server 2000. hh...

 

[IdoMillet]

I believe you should be able to use the NOW() function to create an "SQL Expression" field in Crystal to retrieve this value directly.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

Additionally, if you're trying to access sys just to get sysdate, or the system date, you can use Crystal's own function: CurrentDate to do this instead.

I doubt you actually need to query the system table at all.

Naith

 

[IdoMillet]

Naith,

There could be a difference between SQL Server's system time and the PC's local time...

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

I presumed from the initial post that the system table was being used to derive the date, not the time.

But, if time is a factor and not just the date, then Ido's right - you should not rely on using Crystal's CurrentTime or Now functions, as there's no guarantee that they'll sync with the databases internal clock.

Naith

 

[hhiebert]

I'm not concerned with time. Just the date. The date of the database though could be off from the current date. It could be a restore of a previous database (this happens periodically) and so I don't believe Now()would work and CurrentDate definetely wouldn't.

Any other options?

Also, not only is the record selection based on this date but this date also needs to be appear on the report. And remain static.

If I used CurrentDate then wouldn't the value always change even if the reports remains static (as in saved with data)?

NOTE: I'm using Crystal Reports 9 and SQL Server 2000. hh...

 

[Naith]

Eh? If you had to restore your database from yesterday's backup, the querying the system date would not give you yesterday's date.

The system details is from a pseudo table. It's really reading the date from the server.

Incidetally, when Ido suggested 'Now', he was referring to a SQL Expression, not Crystal's function. If you use this, it'll be the same as using Now() on the server.

When you say you want the displayed date to remain static when the report is static, then Now() and PrintDate do this. I would suggest building the SQL Expression as per Ido's suggestion, and then see where you are.

Naith

 

[hhiebert]

Thanks for the feedback. I'll check it out on Monday as I have a winter storm to drive through right now.

I'll let you know if I get the desired results. hh...

 

[hhiebert]

I think we're all talking about different things....or else I'm just thoroughly confused.

Both Now() and CurrentDate retrieves the date 07Apr2003 (today's date and the date on the server). The database, however, is static and contains 06Apr2003's database ...this is how we do most of our reporting. That date, 06Apr2003 is contained in a table called zzsystem in a field called DatabaseDate. That's the date I want.

Both Now() and CurrentDate do not retrieve the date I'm looking for. hh...

 

[Naith]

Make a subreport which queries zzsystem. Place it, unlinked, in your report header. Store the date in a shared variable, and pass the shared variable back to the main report.

 

[hhiebert]

Naith,

Thanks for all your hard work...and for putting up with me. One more question though (and I hope that's it). Can I base my record selection on a shared variable? I'm thinking that I can't....but I need to...somehow. hh...

 

[Naith]

I'm not putting up with you. I'm currently on notice at my current contract, so all you're distracting me from is documentation - which is a very good thing, indeed.

You can't use a shared variable in your selection criteria, because the shared variable value will be assessed way after the selection criteria is processed.

If you want to drive the report on a parameter which queries the zzsystem date, you would invert the main report and the subreport. Basically, the subreport which currently just queries the zzsystem table would become your main report, and you would place the current main report inside this. This enables you to use a parameter on zzsystem date, and also to pass this value to the main-turned-sub-report.

The downside to this is that you would not be able to have any subreports in the main-turned-sub-report.

All the best,

Naith