We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Start Date and End Date

picasothakkar
on
Medium Priority
261 Views
Last Modified: 2012-08-14
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

Comment
Watch Question


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....
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
I think he might be want the report to create all dates that fall within that range, but I am assuming.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

"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.  
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Took me a few minutes to figure out what it is.

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

mlmcc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.