[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Adding a parameter to Crystal Reports CurrentDate

Posted on 2010-08-21
11
Medium Priority
?
765 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 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