Issue with prompt created from Universe

[mdieckman]

I am attempting to do a passthrough prompt from my universe and I'm experiencing some strange results in Crystal.

In the Universe Designer, I have the prompt setup like this:

dbo.table1.YEAR >= '{@year}'

The variable year refers to the prompt:

{?year_prompt}

which is setup as a string.

I put the prompt in a function so that I could put functions on it if needed, but right now it just passes the value entered through to the database.

It's on a SQL Server database and the value entered is just the year and it's a number field on the db.

Crytal is showing the value as 2009 (if I enter 2009), but it is giving me every year when I really should return just 2009.

I suspect Crystal is slipping in a comma sort of like 2,009, but I can't see that in the variable in the report.

Is there some sort of function where I can ensure that is is just passing a 4 digit year with no commas, decimals or any other such garbage. I can't figure this out for the life of me.

 

[Madawc]

If you're conpairing string to number, that would explain the odd results. Try using a formula field with either ToText or ToNumber to bring them into line.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mdieckman]

Well, I tried doing a cast on the field:

CAST(dbo.table.YEAR AS VarChar(4)) >= '{@year}'

So that I was comparing text to text, but it still has the same result.

And on SQL server, just a raw SQL query over the db has the same result whether or not you say year >= '2009' or year > 2009.

I took the cast statement out and did a tonumber({?year_prompt}) and it had the same result. I'm don't get this at all...

 

[Madawc]

Display the fields on the report. Right-click to see what they are.

Also try Database > Show SQL Query, see how the test translates into SQL

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[AnthonyMJ]

Can you just do the usual way of setting parameters in the universe? Something like this :-

dbo.table1.YEAR >= @prompt('Year','D','List of Values Here',mono,constrained)

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[mdieckman]

Thanks - I actually did end up doing this before your post and that works fine, but I still don't get why it works with prompt created from universe, but does not work with prompt created from Crystal. I guess in the end it doesn't matter much, but I would have thought it would work either way...