Start Date and End Date

Okay I have a small query

I have 2 parameters StartDate and EndDate .
Based on these parameters say I have given the StartDate is 12/12/2005 and the EndDate is 12/30/2005

So now what i want is in the report i want to display the date as

OpenDate
12/12/2005
12/13/2005
12/14/2005
12/15/2005
12/16/2005
12/17/2005
----
---
----
12/30/2005

I hope ull got what i want because in the end based on each day i want to query the database to get information related to those dates

thanks in advance

picasothakkarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

janmariniCommented:

What is your question?  Are you asking how to use parameters, how to format dates, SQL query to extract dates within a range?  Not enough info given to determine the problem you are seeking advice on....
0
mlmccCommented:
Simply use the date parameters in the select expert

{OpenDateField} >= {?StartDate}  and {OpenDateField} <= {?EndDate}

If you rproblem is there are dates missing then you need to provide them in the database by either having a record for each date or creating a separate table of dates and using it as the master table in a LEFT OUTER JOIN to the data.

mlmcc
0
wykabryanCommented:
I think he might be want the report to create all dates that fall within that range, but I am assuming.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mlmccCommented:
I agree, thus the second suggestion to add a table to the database.

This may also work
http://support.businessobjects.com/library/kbase/articles/c2008492.asp

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
picasothakkarAuthor Commented:

The link that you posted here shows the following info:-

How to Display All Dates When Gaps Between Dates Exist in the Database
The information in the article refers to:
Seagate Info, Seagate Crystal Reports 8

Applies to:

Reported version and higher
Displaying Dates Not in the Database
Basic Syntax
Seagate Info

Synopsis

A report is created in the Crystal Reports designer to show dates of the calendar year with corresponding data. These dates are based on a date field in the database.

When the report is previewed, some dates are missing. This occurs because there are gaps in the data for this field, that is, some dates do not exist in the database so some dates do not display on the report.

This particular report however, must display all dates of the calendar year even though some dates may not exist in the database.

How can you display all dates in a date range even though some dates may not exist in the database?


Solution

To display all dates in a date range even though some dates may not exist in the database, follow these steps:

1. Right click the Details section and from the fly out menu select 'Insert Section Below'. You now have a Details A section and a Details B section.

2. Place your date field into the Details A section. Right click the date field to select it and from the fly out menu select 'Format Field'.

3. From the 'Format Editor' select the 'Common' tab and then select the 'Suppress if Duplicated' check box.

4. Format the Details A section and select the 'Suppress Blank Section' check box.

5. Create a basic syntax formula similar to the following and insert it into the Details B section:

' @date
' thisdate and nextdate will be used to determine if gaps exist in the database
dim thisdate as date
dim nextdate as date

'output will be used to create the on-screen formula display
dim output as string
--------------------------------------------------------------------
Problem  with Report2_txt.date??????????
--------------------------------------------------------------------
thisdate = {Report2_txt.date} + 1
nextdate = nextvalue({Report2_txt.date})
--------------------------------------------------------------------
--------------------------------------------------------------------
do while nextdate - thisdate > 0
output = output + totext(thisdate, "MM/dd/yyyy") + chr(10)
thisdate = thisdate + 1
loop

formula = output

' output is creating a string running total to create the dates which do not exist
' the chr(10) in output creates a new line for each new date created

6. Right click @date and from the fly out menu select 'Format Field'. From the 'Format Editor' select the 'Common' tab and then select the 'Can Grow' check box.

7. Right click the Details B section and from the fly out menu select 'Format Section'. From the 'Section Expert' select the 'Suppress Blank Section' check box.

When you preview your report, all dates display, whether or not there is any corresponding data in the database.

NOTE ======

· @date cannot be used for grouping or summaries because it is a print-time formula.

· @date creates only one Details B section per gap in the data.

For example:

A gap of 6 days in a row in the data source will produce one Details B section that is 6 lines high
##########################################################


I don't understand the use of Report2_txt.date field in the above procedure?

Plz advice in this regard asap.

Thanks

0
wykabryanCommented:
"I have 2 parameters StartDate and EndDate .
Based on these parameters say I have given the StartDate is 12/12/2005 and the EndDate is 12/30/2005

So now what i want is in the report i want to display the date as

OpenDate
12/12/2005
12/13/2005
12/14/2005
12/15/2005
12/16/2005
12/17/2005
----
---
----
12/30/2005"

You initial post says that you want all dates the fall in between the two parameter dates.  This will give that information.  It will fill in the areas where it does not exist in the database.  
0
mlmccCommented:
Took me a few minutes to figure out what it is.

{Report2_txt.date} is the date field from your database

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.