Solved

Adding a parameter to Crystal Reports CurrentDate

Posted on 2010-08-21
11
707 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 65
I need to average the totals based on groups in my crystal report. 14 27
SQL Server syntax question 13 30
SQL Error - Query 6 24
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now