[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

Adding a parameter to Crystal Reports CurrentDate

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
kenuk110
Asked:
kenuk110
  • 5
  • 4
  • 2
1 Solution
 
kingjelyCommented:

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

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

Kj
0
 
kingjelyCommented:

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
 
kenuk110Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tickettCommented:
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
 
kenuk110Author Commented:
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
 
tickettCommented:
Ah sorry! Easy:

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

In a new formula and you're all set!
0
 
kenuk110Author Commented:
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
 
tickettCommented:
woops- helps if i look what i'm copying/pasting :)

dateadd('d',{?Days}, currentdate())
0
 
kenuk110Author Commented:
Lol,

Well I tried the next one and it says:

A number is required here - again highlighting the {?Days} field.
0
 
tickettCommented:
What data type are you capturing with {?Days} it should surely be a number parameter?
0
 
kenuk110Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now