Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Start Date and End Date

Posted on 2006-04-04
9
Medium Priority
?
243 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

0
Comment
Question by:picasothakkar
7 Comments
 
LVL 7

Expert Comment

by:janmarini
ID: 16377452

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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16377871
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16380807
I think he might be want the report to create all dates that fall within that range, but I am assuming.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 16388313
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
 

Author Comment

by:picasothakkar
ID: 16392492

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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16392619
"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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16397249
Took me a few minutes to figure out what it is.

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

mlmcc
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

578 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