Report Design: Passing Date Parameters into the report

Print Page | Close Window

Passing Date Parameters into the report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12466
Printed Date: 01 May 2024 at 12:18pm

Topic: Passing Date Parameters into the report

Posted By: Nav522
Subject: Passing Date Parameters into the report
Date Posted: 01 Mar 2011 at 12:22pm

Hello Folks,

I have a report that i have created using the COMMAND option.Basically am retreiving the Count for all the batches with status.

I actually am bringing in the date from the batch id

i.e batch id is like 201029110038 which means YYYYDDMM0038.

I have a formula to extract the month part as below which am using it as Date criteria

@Extract = Right(left({Command.CPS_REQUEST_BATCH_ID},8),2)

Finally am creating the formulas to get the Counts as below and placing them in the RF

If @Extract = 11 and Status = 'Y' then Count(Batchid).

My requirement is i want to run the report only for a particular year such as 2010 or 2011.AM getting confused in having the logic created i.e passing the date parameters.

Can anyone have any ideas on how to start?

Regards,

Nav

Replies:

Posted By: DBlank
Date Posted: 02 Mar 2011 at 4:41am

do you want to pass a date range, a year value or something else?

Posted By: Nav522
Date Posted: 02 Mar 2011 at 5:38am

Hi there. am looking more for a specific Year. i.e. user shud be entering either 2009,2010 or 2011 etc

Regards,

Nav

Posted By: DBlank
Date Posted: 02 Mar 2011 at 6:09am

one option,

create a param called 'year' as numeric with min and max values to force a user to stay in the rang you want

use that in the select expert as

{?year} = tonumber(left({Command.CPS_REQUEST_BATCH_ID},4))

or if you just want to leave it a string make a param 'year' as a string type

select statement as

{?year} = left({Command.CPS_REQUEST_BATCH_ID},4)

Posted By: Nav522
Date Posted: 02 Mar 2011 at 6:49am

You da man that worked. I cant believe that i missed this simple logic.But anywayz thanks

Posted By: Nav522
Date Posted: 02 Mar 2011 at 12:10pm

Hi,

I have another question which you might have a solution. In the Report Footer I have placed the cumulative total for each month.

i.e Jan feb mar apr may

100 0 0 0

Is there anyway that i can suppress the totals which are after february?? Because they would all be zeros.

May be using something like

month(currentdate) = 03 > Extract then supress? Am not quite sure.

I dont want to display apr and may and next month i want to show totals until april and suppress all other month and so on.

Regards,

Nav

Posted By: DBlank
Date Posted: 03 Mar 2011 at 5:34am

How did you insert the summary? Running Totals, Cross tab, formula fields or something else?

Do you want all zeros suppressed or just all future months?

Posted By: Nav522
Date Posted: 03 Mar 2011 at 6:54am

The summary are basically formula fields.Basically i need all the futures to be suppressed.

Regards,

Nav

Posted By: DBlank
Date Posted: 03 Mar 2011 at 6:55am

can you give me an example of one formula?

are there only 12 - one per month of this year?

Posted By: Nav522
Date Posted: 03 Mar 2011 at 7:50am

I have 12 for each month. Basically am having these formulas in the detail section and suppress the detail. Finally doing a summmary of this formulas in the report footer

@janC =

If mailto:%7b@Extract - {@Extract } = '01' and {Command.REQ_STATUS} = 'D' then ({Command.COUNT(1)})

Posted By: DBlank
Date Posted: 03 Mar 2011 at 7:53am

mailto:%7b@Extract - {@Extract } = '01'

the month number?

Posted By: Nav522
Date Posted: 03 Mar 2011 at 9:09am

Yes '01' is the month that am looking.

Posted By: DBlank
Date Posted: 03 Mar 2011 at 9:12am

suppression formula to be applied to each sum as:

tonumber( mailto:%7b@Extract - {@Extract }) > month(currentdate)

Posted By: Nav522
Date Posted: 03 Mar 2011 at 9:24am

I had the above mentioned condition placed in the suppress formula for every month summary. Its not suppressing any of the months. Am thinking in this angle..... how would it know to place which month in the

( mailto:%7b@Extract - {@Extract })??

Posted By: DBlank
Date Posted: 03 Mar 2011 at 9:29am

I am assuming that the @extract is the formula to use to get the month number as text and it is used in each of your summaries, is that correct?

if so it the formula I gave you converts that from text to a number and compares it to the currentmonth number. If it is > then it should suppress.

What exactly is the @extract formula?

Also have you considered just using a crosstab to do this for you?

Posted By: Nav522
Date Posted: 03 Mar 2011 at 9:49am

I am assuming that the @extract is the formula to use to get the month number as text and it is used in each of your summaries, is that correct?

Yes thats correct

What exactly is the @extract formula?

Right(left({Command.CPS_REQUEST_BATCH_ID},8),2)

well i wud think the formula should suppress the fields. Am not sure why it didnt do that. Well anywayz thanks for the idea. I will try to look into the cross tab. Thanks a bunch

Posted By: DBlank
Date Posted: 03 Mar 2011 at 9:57am

ahhh...

that won't work because that formula is referenceing row level data and so in the footer only references the last row in the report.

depending on what you are summing a more elegant solution might be to convert your data into a date field and use a crosstab

that will only show what exists.

Posted By: Nav522
Date Posted: 03 Mar 2011 at 10:00am

Awesome Understood. Thanks for the input!

Posted By: DBlank
Date Posted: 03 Mar 2011 at 10:03am

also the ct will dynamically grow over the year so make sure you leave enough room for all 12 months.

Print Page | Close Window