create an expression on Field Heading in Crystal Reports

[karnd]

Hi,

I am new to Crystal so i have no clue how should create an expression on Field Heading?

2nd, here is the scenario-
When i prompted for 2005 (using Filters) then the report provides the prior year(s) data also

Eg:For 2005
-----------------------------------------------------------
Sno Ename CurrYear-1 CurrYear-2 CurrYear-3
-----------------------------------------------------------
1 XYZ $20000 $18000 $15000


So the CurrYear-1 should displays as 2004 and CurrYear-2 should displays as 2003 and etc..based on the above prompt value ie 2005

Please provide your valuable ideas.

Thanks a lot,
Regards,

 

[kskid]

Assuming your year parameter is numeric, then create formula for each year

//@CurrYr
totext({?YearPrompt})

//@CurrYr1
totext({?YearPrompt}-1)

//@CurrYr2
totext({?YearPrompt}-2)

//@CurrYr3
totext({?YearPrompt}-3)

Or you could use crosstabs and summarize by years and place in column

-lw

 

[lbass]

When you use kskid's formulas, add a 0 condition for the number of decimals and "" to remove the comma separator, as in:

//@CurrYr1
totext({?YearPrompt}-1,0,"")

-LB

 

[karnd]

Hi All,

I would appreciates of your solution but i dont know how it works in Crystal Reports when wanted to reflect the above solutions(which you provided) on "Field Heading" .

Eg:
Supposed, the field heading is CurrYear-1 then it changed to 2004 for the prompt value='2005' and correspondingly for others.

I tried creating as you said but i dont know how it affects to the field headings.

I hope you got my question.

Thanks a lot,
Regards,

 

[lbass]

You need to remove the columns that appear when you add your detail fields, and then replace them with formulas that you create in the formula expert (field explorer->formula->new) like:

//@CurrYr1
totext({?YearPrompt}-1,0,"")

Place these formulas in your page header.

-LB

 

[karnd]

Hi ,

Yep! Got you and thanks a lot

I have one more question-
For creating a SQL Expression i have a date field which is in this format 03/31/2005 so i have to bring only this way like March,2005 to make use of it for a Prompt.

Please help me out.

Thanks a lot,
Regards,

Regards,

 

[lbass]

If you are saying you want to use a SQL expression to convert a date field to a string containing month and year, with a comma separating month and year, then create this SQL expression:

{fn MONTHNAME({fn MONTH(Table.`Date`)})}&','&{fn YEAR(Table.`Date`)}

-LB

 

[karnd]

Hi,

I have tried as you said but got the following error message:
SQL Parsing Error:
42000:[Microsfot][ODBC SQL Server Driver][SQL Server] Line1:Incorrect syntax near "full_date"

I have tried by changing the quotes but no use.
Somehow i am not able to getting it where am i doing wrong.

Please..
Thanks a lot.

 

[lbass]

My last post assumed that your date field was a date, but from another thread, I see that it is in fact a string. Using your string date, you could try this SQL expression. I'm not sure of the punctuation for your datasource, so you might have to change the punctuation around tables/fields.

{fn MONTHNAME({fn Convert({fn LEFT(table.`stringdate`,2)},SQL_INTEGER)})}&','&{fn RIGHT(table.`stringdate`,4)}

-LB