asked on

Want to run report in Crystal Reports Server using current date

I am trying to run a report in Crystal Reports Server XI that would use the current date as a default parameter.

The trouble is, when I set it up in CR Management Console, I can't get the system to recognize any terms for current date (e.g., "sysdate", "today()", "date()").

Whenever I try to enter these terms in the report parameters (e.g., in the Crystal Reports Server report folder under the Process tab, selecting Parameters) or in CR InfoView, I get an error message saying "A request was cancelled. The necessary security privileges could not be verified. This indicates a problem with the security server." However, this security problem disappears if I just enter a date in the requested format: yyyy-mm-dd.

Now, I suspect the reason for this problem may be rooted in the field type. The DateThru field apparently comes to me via stored procedure as a string, instead of a date. So I'm suspecting I need to not only to determine the current date, but also match this date to the string field called DateThru.

Any ideas how I can set this up?

ASKER CERTIFIED SOLUTION

bdreed35

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

denver5o

ASKER

Okay, I've gotten a friend to change the stored procedure as requested, so that it is now showing up in Crystal Reports as a DateTime field. (I actually only wanted a Date without a specific time, but I guess this works too.)

But now what I really want to do is make it so that the Crystal Report always uses the current date... and I can't do that.

I've tried putting in terms like "now()" or "getdate()" into the parameters when Crystal Reports asks for them, but it will only accept the specific DateTime format (e.g., 2008-04-30 00:00:00). I've also tried creating a formula where UpToToday is simply CurrentDateTime, and/or setting the report parameter DateThru equal to CurrentDateTime, but no dice.

I wonder if it's easier to change things on the Crystal Reports side, or on the Stored Procedure side? I've attached the initial snippets of the Stored Procedure code as well, in case that helps.

CREATE PROCEDURE dbo.labs_rpt_ArStatement_sp(
/*
**   NOTES:	
	- Epicor stores dates as integers; I'm entering the date parameter as varchar(10) 
	and converting; Parameter can be accepted as something other than varchar(10) 
	- Multi-currency is not addressed by this script 
**  
** bon_rpt_ArStatement_sp @iDateThru = 729754, @vcCustomerCodefrom ='000', @vcCustomerCodeThru ='zzz'        
**    
*/
    @dtDateThru          datetime,    --   @vcDateThru          varchar(12) = '12/31/2010'
    @vcCustomerCodeFrom  varchar(8)  = '0',
    @vcCustomerCodeThru  varchar(8)  = 'ZZZZZZZZ',
    @vcCompany           varchar(40) = ''
)
 
AS	
 
SET ARITHIGNORE ON
SET NOCOUNT ON
 
/* 
** Save the following code in case it is decided not to initiate report from within the Epicor application.
** Convert Crystal Date Parameters to Integers to compare with Epicor db values 
   DECLARE	@iDateThru  int
   SELECT	@iDateThru = (DATEDIFF(dd, 'Jan 01 1753', @vcDateThru) + 639906)     
*/
 
/*  
** Convert the date to something readable.  
*/
  DECLARE  @iDateThru 	int, @vcDateThru varchar(12)
 
  SELECT   @iDateThru = datediff(dd,'1900/01/01', @dtDateThru ) + 693596
  select @vcDateThru = convert(varchar(12), @dtDateThru, 101)  -- keep this date format uniform with all other date fields

Open in new window

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial