Date range problem in crystal report 8.5

Hi all

I have a problem in using date range

If i select a date range,need to get values for proveious years june 1st to may 31st of current year

ex: 02/01/2006 - 02/28/2006(Current)

need to get values for 06/01/2005 - 05/31/2006 date range

could any one help me in writing formula for this

Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply

i would like to give you more specific

In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006

need to get the values of preveious years of June 1st - current years May 31st

01/06/2005 - 31/05/2006 values should display

actually we have two fields in that in first field need to display the first date range values and in second one need to do like that

We have two fields named

current and year to date

current means date range the user selected

Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365

include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field

formula =DateAdd ("yyyy",-1 , {YOUR DATE })

and use this formula wherever you want to use same date of previous year

Best Luck...|||Thanks for your reply

its useful for me

could you pls tell how to take fixed date 1st june to end date 31st may

Date Range Parameter Help

Crystal Reports XI-

I have entered a date range parameter into my report to pick a range for Appointment Dates. When I choose a start date of 01/01/2007 and an end date of 01/31/2007, my report has all of January's appointments, but also has the last appointment of the previous month (in this case December). The same happens if I pick a date range from Feb 1st to the 28th. I get all of Feb appointments, but also the last day in January.

Am I missing something? Or has this happened to anyone else?Are you wanting the dates between your 2 date paramenters, and if so did you reference your date ranges in the record selection?

{table.appointmentdate} In {?StartDate} To {EndDate}

This will give you everything between the 2 dates.

GJ

Date problem in Selection formula

Hi,
I am using Crystal Reports 9 and i am facing some problem in creating a selection formula to select some records in between a range of given dates.

MY DATE PARAMETER IS A STRING AND I NEED TO CONVERT IT TO DATE TYPE

Actually I don't know how to make to needed selection formula. What i really needed is to select record sets in between a range of dates which is given as parameters to the reports

Pls help
JOBINuse record selection formula

urfield >=dateparameter1 and urfield<=dateparameter2

Date Problem

Hi...

i wannna thank about this hard work ur doing in this forum.

actually, i'm using vb6 and crystal report 4.6

the problem is that i want to generate a report searching about 2 specified dates from the user.
I tried these codes

1) CR.SelectionFormula = "{SoldItems.Date} in ('" & Date1.Value & "') to ('" & Date2.Value & "')"

2) CR.SelectionFormula = "{SoldItems.Date} Between DateValue('" & Date1.Value & "') And DateValue('" & Date2.Value & "')"
but they don't work

For example, i want to get a report about the sales from
03/15/2004 to 05/10/2004

can u help me with that and how that could be coded in vb6 plz
?where r the expert users?|||We've all been on holidays over Christmas, or at the Betty Ford Clinic, or undergoing electroconvulsive shock therapy............it's the price we pay for involvement with Crystal reports...:-)
Me, I've been thanking the Great Universal One that I wasn't in Phuket or somewhere in that region. Tragic.
On to your comparatively small problem on the pathway to Crystal Enlightenment.

Firstly, why not use parameters for the report, like pStartDate, pEndDate and allow the user to supply these when report is run? Maybe you want to automate it and that is the reason. If so, fair enough.

Secondly, the following has your dates encased in single quotes, which means they are going to be evaluated as strings by the SQL parser. Is that correct?

1) CR.SelectionFormula = "{SoldItems.Date} in ('" & Date1.Value & "') to ('" & Date2.Value & "')"

Try this:
CR.SelectionFormula = "{SoldItems.Date} >= " & Date1.Value & " AND {SoldItems.Date} <= " & Date2.Value

This would evaluate to the following string being passed;
"{SoldItems.Date} >= 2004/01/06 AND {SoldItems.Date} <= 2005/01/06"
which means the parser should see the dates as dates, not strings.

dave|||thx so much springsoft

i'm gonna try what u wrote and i hope it's gonna work

thank u again and sorry about being late

Date Parameters show as Strings when using Analysis Services as a data source

We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.

The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.

I have two quick questions:

Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes? Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.

Code Snippet

SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@.ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@.LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.

Thanks very much in advance for your help.

Regards,

Steve

Steve,

i've been there, done that, and it can be done.

For question 1, change your parameter type to a DateTime (no dataset required), and you will get the calendar control - you are getting a dropdown probably because you have got it as a string type and because you are supplying a dataset of values from the date heirarchy of your cube. The real trick to this is converting the output of the calendar control to something that resembles the members of your date heirarchy.

For question 2, you need to change the way your date is represented so that you can include it as a parameter in your MDX. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1480488&SiteID=1 for some actual examples i posted for someone else.

|||

Thanks for your help. I'm still missing something, because I'm still throwing an error. Could you take a look and see where I went wrong?

1. Opened the parameters created by the Wizard. Changed the Data type to DateTime. There are no boxes checked under Properties. Available values was changed to Non-queried; left the Label/Value matrix blank. Default values: Null.

2. Added Format(CDate(@.parameter),"yyyy-MM-ddT00:00:00".... to the MDX query. See below:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER(Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) : STRTOMEMBER(Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00"), CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

3. For reference, I ran the query under the Dataset that the Wizard created for the parameter FoLeadCreationDateCalendarDate. The results of that query show the ParameterValue field to be populated with values of the format [LeadCreationDate].[Calendar Date].&[2000-01-02T00:00:00]. This is why I selected "yyyy-MM-ddT00:00:00" as my format string.

4. The error I am throwing is:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

The following system error occurred: Type mismatch. . (Microsoft SQL Server 2005 Analysis Services)

BUTTONS:

OK

After re-reading your post, I also tried:

Code Snippet

SELECTNONEMPTY { [Measures].[Lead] } ONCOLUMNS, NONEMPTY { ([Store].[Store Region Type].[Store Region Type].ALLMEMBERS * [Store].[Store Region Name].[Store Region Name].ALLMEMBERS * [Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.FromLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) : STRTOMEMBER("[LeadCreationDate].[Calendar Date].&[" + Format(CDate(@.ToLeadCreationDateCalendarDate),"yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) ) ONCOLUMNSFROM [Referral Leads]) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Also gave the same error.

Any help on this would be tremendously appreciated.

- Steve

|||

OK - it took me a few days but I figured out how to do it:

1. Develop the report using the Wizard.

2. As sluggy mentioned, change the Data Type of the date parameters to DateTime, and set Available Values to Non-queried.

3. On the Data tab, edit the cube's Dataset (click on the ... button).

4. Click on the Parameters tab of the Dataset dialog box. Replace the Value of the date parameters with ="[Dimension].[Member].&[" + Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") + "]" where [Dimension].[Member] and ParameterName are the dimensions and parameter names of your particular cube. Depending on how your dimension is set up, you may also need to change the format of the date.

5. There is no need to change the MDX of the query.

sql

Date Formula Problem

I am a complete noob with crystal reports and am in real trouble with monthly billing, so any help would be greatly appreciated.
our billing period is from 7/31 - 8/30, and the reports are run on 8/31. We found a bunch of mistakes and had them fixed but now I need to be able to rerun
the report for that time period today. All my time formulas are below, please help.

Report Date

"For the Month of " & ToText(CurrentDate - 15, "MMMM") & " " & ToText(CurrentDate - 365, "yyyy") & " & " & ToText(CurrentDate - 25, "yyyy")

Current Year

If {orders.DATE} in Maximum(LastFullMonth) to CurrentDate - 1 then 1

Previous Year

If {orders.DATE} in Maximum(LastFullMonth) - 365 to CurrentDate - 366 then 1That's the problem with using CurrentDate - it only works on the actual date!
Replace the use of CurrentDate with a formula. Make the formula return the date you want to simulate running for. Then you'll only ever need to change one thing in the report to get it to run for whatever date you like.
Or take it one step further and use a parameter so you don't even have to change the report. You could still use an intermediate formula so that you can make it work however you want without having to alter the report much at all.|||How would I go about setting it up to just use a specified date range, I tried using DateValue and for some reason it would always come up with the year 1898.

Date formatting problem

I have a crystal report that is populated by the results of a SQL stored procedure. The data returned is grouped by year and this appears along the x axis of the chart. The problem is that it sees the years as numbers and changes them to 2,000 2,001 2,002 etc. I have tried defining them as dates in the stored proc but this makes no difference and the chart options >grid >group axis does not allow me to alter the numbers category to date as it is greyed out.

Does anyone know how I can display these dates correctly.

Thanks for any help in advance.Try formatting the field to show without the comma.

Example in CR 8.5 using RDC... Right-click on the field and click Format, there's a tab called Date/Time or Number or something like that.|||I am also having this problem, expect I have used a formula field to combine two date fields to give me a date range (February 15-20, 2004), but I am getting February 15.00-20.00, 2,004.00. I can't change the formatting since the formula field is a string. Any suggestions?|||Try changing the default Number Format. In CR 8.5 using RDC, right-click to bring up the menu, Click Designer, Default Settings. Click the Fields tab. Click the Number button. This will allow you to set the default format for the Crystal Reports Program, not just the report you're working on.

Date format display problem.... :(

Hi gurus,

I'm having problem with the date format while displaying report. I'm using Crystal Report 8.0 and have a report with field EFFECTIVE_DATE. I'm using a ttx file to display data in the report where this field has been declared as Date. I have set the format for this field as MM,DD,YYYY but, when the report displays in the application in which I'm using it, the field shows as YY,MM,DD. Can anybody suggest me what shall I do to get it back to MM,DD,YYYY. I'm using C# and .NET 2003 IDE for my application.Right-click on the field on the report and select 'Format Field' option.On the 'Date-time' tab, set the format you need.
From the menu, select File->Options->Fields. Click 'Date' button and set the Order option to MDY.

Hope this helps.

Rashmi

Date Format

hello

I'm having problem with date format.I have CRYSTAL 9 and using CRYSTAL ENTERPRISE 9 to view reports.
default system date format is dd/mm/yyyy.when the report is viewed with CR, the date format is system date format and this is what i want.but when viewed by networked users using CE some are veiwing date as 'mm/dd/yyyy'.even if user date setting is 'dd/mm/yyyy' and server setting is 'dd/mm/yyyy'.

Please advice
RegardsHi,

Could you use a fixed format for your date fields? This would be the simplest solution. Personally I'm working an environment with several regional settings and I have defined date layout parameters in my date base. I fetch the data in a subreport into shared variables in the report header and then I use the variables to define the date format. This works fine.

- Jukka

Date display is incorrect

Hello,

I have a vb program that loads a Crystal Report. I am using VB 6 and Crystal 9. When I run the report from within Crystal Reports, my dates display correctly (6/14/2005). When I run the report from within my VB program the dates do not display correctly on the report. They display like (6.00/14.00/2,005.00).

What could be different from running the report from within Crystal Developer and running it from a VB application? I've been banging my head on this one for some time now so I really appreciate any suggestions!

Thanks,
StephanieOpen the report and right click on that datefield and select Format Section
Then Goto Date and choose the format you want

Date creation in SQL

Hi,
I have a parameter that is being input as a String via Crystal Reports.
It's called 'School Year'
and a typical value is '2006'. I need to be able to create a 'Date'
that is compared to a field value in a where clause. In this case the
db field is called 'requisition_time_stamp' in the T_ORDER table. The
'requistion_time_stamp' file is of type 'datetime'.

Below is pseudo_code for what I need to do:

T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
AND
T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
1)

The 'Date' method is a mock-up and is problematic because the 'School
Year' field needs to be converted to a number so it can be incremented
in the second statement, so I also need a conversion method, something
like 'ToNumber(School Year)' .

I've looked at the Date functions in the Sybase documentation and I
don't see anything that fits what I'm trying to do.

Any help would be greatly appreciated!wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I have a parameter that is being input as a String via Crystal Reports.
> It's called 'School Year'
> and a typical value is '2006'. I need to be able to create a 'Date'
> that is compared to a field value in a where clause. In this case the
> db field is called 'requisition_time_stamp' in the T_ORDER table. The
> 'requistion_time_stamp' file is of type 'datetime'.
> Below is pseudo_code for what I need to do:
> T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
> AND
> T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
> 1)

It's as simple as:

T_ORDER.requisition_time_stamp >= @.year + '0101'
T_ORDER.requisition_time_stamp < dateadd(DAY, 1, @.year + '1231')

YYYYMMDD is one of the few date formats that are always interpreted
the same in SQL Server.

> I've looked at the Date functions in the Sybase documentation and I
> don't see anything that fits what I'm trying to do.

Sybase? This newsgroup is for Microsoft SQL Server. Thankfully, the
solution above works for Sybase as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You could do something like this (in a stored procedure):

-- the INPUT parameter would be

@.school_year CHAR(4)

-- then use this in the WHERE clause

ORDER.requisition_time_stamp >= @.school_year + '0601'
AND ORDER.requisition_time_stamp < CAST(CAST(@.school_year as int)+1 as
char(4)) + '0601'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFkvk4echKqOuFEgEQJROwCfRjD+rzabv+WXmUD22z4RtR czyy4An1eh
sCSLmD3GVmLEk9RTEtCoPQo3
=TuKz
--END PGP SIGNATURE--

wgblackmon@.yahoo.com wrote:
> Hi,
> I have a parameter that is being input as a String via Crystal Reports.
> It's called 'School Year'
> and a typical value is '2006'. I need to be able to create a 'Date'
> that is compared to a field value in a where clause. In this case the
> db field is called 'requisition_time_stamp' in the T_ORDER table. The
> 'requistion_time_stamp' file is of type 'datetime'.
> Below is pseudo_code for what I need to do:
> T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
> AND
> T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
> 1)
> The 'Date' method is a mock-up and is problematic because the 'School
> Year' field needs to be converted to a number so it can be incremented
> in the second statement, so I also need a conversion method, something
> like 'ToNumber(School Year)' .
> I've looked at the Date functions in the Sybase documentation and I
> don't see anything that fits what I'm trying to do.
> Any help would be greatly appreciated!

Date Conversion

I'm hoping someone here can help me out because I am incredibly stuck on an issue in Crystal. I'm currently trying to take a string similar to the following "15:39:40" and change it into a time or numeric format so I can do some calculations on it. Unfortunatly every attempted to convert this using functions like Mid to seperate only the numbers then ToNumber to do the numeric conversion has met with total failure. I'm not new to coding but I am at this point very very lost as to how crystal wants to allow you to convert this data can anyone explain to me how in the heck to do this?Why not fetch the data as a string from you database using a to_char function and then use mid etc. for editing. If you use CR 10 or newer you can create SQL functions from Crystal Reports

- Jukka|||Hi,

I'm new to Crystal Reports 10, but not to SQL and I'm having an issue. I can't use SQL to create a stored procedure to be run by Crystal, so I'm having to go to the database directly from Crystal.

I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.

The date will then be used to compare to today's date to determine if the record is returned or not.

Anyone have any thoughts?

Thanks!|||try out this


local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)

or what are you not telling us when you wrote 'take a string SIMILAR to'?|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)

or what are you not telling us when you wrote 'take a string SIMILAR to'?

sorry :)

I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.



local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||If its a string while fetching it from database you can convert it to datetime.|||Hensa, I was answering the thread's original question, not Bentley's one.