?
Solved

Adding a parameter to Crystal Reports CurrentDate

Posted on 2010-08-21
11
Medium Priority
?
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 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