select a field within a parameter

[Former Member]

Hi, I have searched SAP forums but can not find an answer to the following:

Is there any way that you can select which field to report on as part of a parameter?

I have a report which displays a line chart with markers. The chart only has one field of data to display with a timeline on the bottom axis. There are a possible 10+ fields of data to report on and I want the user to input the field they would like to view from a drop-down list. I then want the value selected from this list to be dynamically inserted into the select statement.

I hope this has made sense, any help would be much appreciated.

Thanks, Julie P

version used: Crystal Reports Developer 2008 version 12.2.0.290

[AndrewBaines · Accepted solution]

You can give a list of optional fields in the parameter so:

Parameter1 has options of field1, field2, field3

The have a formula:

if parameter1 = "field1" then

else if parameter1 = "field2" then

else if parameter1 = "field3" then

Then just place the formula where you'd have placed the field.

[Former Member]

Thanks for all your help and suggestions. I have used Andrews suggestion as bringing back all of the data is not an issue as there isnt a huge amount.

Julie

[Former Member]

Andrew's idea can work if you don't mind bringing in the data from all of the fields... but there is a more efficient method available if you can write a little SQL.

It takes advantage of the fat that CR will pass parameter values to Command exactly as they are written.

So for example:

SELECT
tn.{?ParameterName} AS FlexField
FROM TableName AS tn

So now if you choose "TableID" as the value of the parameter, the SQL being passed to the database will look like this...

SELECT
tn.TableID AS FlexField
FROM TableName AS tn

HTH,

Jason

[AndrewBaines]

Jason

I like that!

Just a shame you can't do it with a SQL Expression instead of a SQL command - much easier to modify the report.

I guess you need to weigh up losing server side grouping and ease of modification vs a few extra columns returned. Best option is going to depend upon how many columns you're choosing from.

Need to watch the data types too as there'll be no checking with a SQL command.

[Former Member]

100% agree! A SQL Expression field would be much easier. Unfortunately, every time I've ever tried to add correlated subquery into a SQL Expression, it generates an error. Apparently the SQL Expression fields aren't embedded directly into the CR generated SQL.

It would be nice if it did though...

Data types will always be an issue when doing something like this. Odds are, if the results are going to be graphed all available fields would be a a numeric datatype. Otherwise converting to text would solve it...

CAST as a generic floating point number

SELECT
CASTtn.{?ParameterName} AS Float) AS FlexField
FROM TableName AS tn

or CAST as text

SELECT
CAST(tn.{?ParameterName} AS VarChar(400)) AS FlexField
FROM TableName AS tn

[AndrewBaines]

Going off topic, but to use a SQL Expression as a correlated subquery, just put brackets around it and alias it:

(SELECT SUM(a) from b WHERE b.y =c.y) AS XYZ

I may have the b.y = c.y the wrong way round, I can never remember. It then gets placed into the SQL as expected.

Still can't put parameters in it as far as I can tell though.