Data Format in Cross Tab and Parameter window about numeric and date

[Former Member]

Hello,

I got a problem to show Year 2008 as 2,008.00, it's rediculous...

My PC and Notebook both show "2008", but my three co-workers' PCs all show "2,008.00"

the group field is: CStr(Year({SJ_Inv;1.TaxDate}))+ ' Sales Analysis'

How to convert such a numeric into text without decimal? CStr looks not workable.

also,

the numeric var in parameter window, sometimes I don't want it to show up comma and decimal, how to format it?

And, the datetime var in parameter window from store procedure, can it be a "Date" without Time for user to entry only?

I will be very appreciate for your helping.

~Michelle

[Former Member]

Hi, Raghavendra,

Thanks for your information. I'll keep that.

(a little comlicated, I'd like to know if any Declare Date var !)

~Michelle

[Former Member]

I think to declare any variables better to use stored peocedures

Regards,

Raghavendra

[Former Member]

Hi, Raghavendra,

The reason to use stored procedure is to save server room -- limited data for output; this is why we declare variable in Store procedure, and got problem there -- datetime but not date.

On the other hand, if we declare variable in Crystal report, it's allowed date type, but how to defaut the Date-from as 1st day of current month, and Date-to as Today in Crystal Report parameter setup ?? And then how to pass the parameter to store procedure to limit the data output volume?

(I am also trying to get this done in Store procedure right now..)

The pros and cons exist in Store procedure and Crystal report, which is better is not definite!

Best regards,

[Former Member]

Try this,

totext(Year({SJ_Inv;1.TaxDate}),"##")+ ' Sales Analysis'

which gives you the output as "2008 Sales Analysis".

For the date prompt you need to select the data type as Date but not DateTime

Regards,

Raghavendra

[Former Member]

Hi, Rag.

Good ! You solved the decimal problem.

But the Declare variable in stored procedure looks not accepted Date in Crystal Report!?

Also, parameter window , how to elimiate the comma and decimal if my parameter is integer ?

~Michelle

Edited by: Allen Tsao on Sep 20, 2008 11:19 PM

[Former Member]

Hi Allen,

If you want the date value from datetime field then you need to convert the datetime to char at database level like this

select convert(char,datetime field,101) from table

same for the decimal field to remove commas use

select cast(field as int) from table

which gives you the output in string format and while using these fields you need to convert it to date field or numeric field using formulas in crystal.

Regards,

Raghavendra