Solved

Adding a parameter to Crystal Reports CurrentDate

Posted on 2010-08-21
11
714 Views
Last Modified: 2012-05-10
Hi,

I have a report where I'd like to use a parameter field I get from a database to + or - days from the current date.

I've tried: CurrentDate + {?Days}

but this just says "The remaining text does not appear to be part of the formula"

Any ideas how I can get this to work?

Any help would be much appreciated.

Regards,

Ken
0
Comment
Question by:kenuk110
  • 5
  • 4
  • 2
11 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33494128

Hi if you want the days + or - from your date parameter you need

This for days
datediff('d',{?STARTDATE}, currentdate())

Kj
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33494137

To explain a bit further
<like to use a parameter field I get from a database

'd' = the interval part
{?startdate} = put your date field from  your database in here
currentdate() =  currentdate

datediff('d',yourdatefield, currentdate())

Hope thats a little clearer ;)

Kj
0
 

Author Comment

by:kenuk110
ID: 33494170
Hi,

I tried that but it doesn't work. I think I may have explained it wrong, I have a parameter in a 'command' which is linked to a table on my database. This 'command' has a parameter:


declare @d datetime
set @d = getdate() + {?Days}
select * from dbo.MyVMF( @d )

The {?Days} part just asks me to tell it how many days forward or back, so 1, 2, 3 etc. I wanted to use this same parameter in the design of the report itself but attached to the date as I need the date to be the same as the data it has retrieved.

Not sure if that explains it a little better, maybe you suggestion works but I'm just not getting it??
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 11

Expert Comment

by:tickett
ID: 33494463
That looks fine, although i'm not quite sure about the select bit at the end (is that a stored procedure?)

I would expect something like:

declare @d datetime
set @d = getdate() + {?Days}
select * from dbo.MyVMF where some_date_column = @d

If it's a table/view...

L
0
 

Author Comment

by:kenuk110
ID: 33494490
Hi,

I think I may be confusing you all here.

I have a function on my MSSQL DB. When I run a report in Crystal I attach the {?Days} parameter to it so it gives me the correct data back for the day I want; today, tomorrow....up to 7 days. I need to be able to have a date appear on the report which corresponds with the day I have retrieved.

So if the paramater I pass to the MSSQL function is +1 day then I need Crystal to use CurrentDate +1 too.

I'm pretty stuck though.
0
 
LVL 11

Expert Comment

by:tickett
ID: 33494503
Ah sorry! Easy:

datediff('d',{?Days}, currentdate())

In a new formula and you're all set!
0
 

Author Comment

by:kenuk110
ID: 33494512
It says:

"A date is required here" when I try to accept it in Formula Workshop and it highlights {?Days}

Tricky one this one!
0
 
LVL 11

Expert Comment

by:tickett
ID: 33494514
woops- helps if i look what i'm copying/pasting :)

dateadd('d',{?Days}, currentdate())
0
 

Author Comment

by:kenuk110
ID: 33494531
Lol,

Well I tried the next one and it says:

A number is required here - again highlighting the {?Days} field.
0
 
LVL 11

Accepted Solution

by:
tickett earned 500 total points
ID: 33494532
What data type are you capturing with {?Days} it should surely be a number parameter?
0
 

Author Closing Comment

by:kenuk110
ID: 33494571
I really have to apologize, it was a string value the parameter was set too!!

You figured it out though, fantastic, it all works now, brilliant!

Thanks for your patience.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 28
error in my cursor 5 41
Parameter Requester showing comma in number field 7 36
Complex Query - help please 5 52
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question