Hi ,
As per my requirement i need to take a part of prompt value (i.e part of string) and multiply the partial value with another value in a tabular sql built report.
I tried using select amount*substr(?promptparameter?,4) but its throwing an error saying invalid charecter.Any ideas how to use prompt parameter for calculation in a report built with tabular sql.
Thanks in advance
QuoteI tried using select amount*substr(?promptparameter?,4)
substr(?promptparameter?,4) -- What does it return?
i don't think you can use a string in a mathematical equation. Substring will most certainly return a string data type. You'll need to cast it to the appropriate number data type.
Having said that, this doesn't sound like a great design approach to me. Why would you have a user enter something this way? This will be prone to failure if any non-numeric values in the portion you substring out and then cast to number.
If you would like further unsolicited advice don't hesitate to let me know....oh wait....never mind.....i don't often wait for invitation to offer suggestions :)
Thanks for your suggestions.Currently the above expression is showing invalid charecter error .But the above expression seem to work fine using cognos.Just wanted to check if ?parametername? can be used in the above calculation or is there any other syntax to be used I can convert it to number and use but is it possible to use a prompt parameter in caculations of an sql
You can use a prompt macro in a SQL based query in Report Studio. Refer to Chapter 10 of the Report Studio user guide for details on the syntax.