Parameters To Include Null Or Empty String Values

[Adam_Alsop]

I have a table that has both Nulls & Empty Strings that I want to use as a Dynamic Parameter. Even though there is an indication of the Null/Empty String value when the parameter prompt comes up, selecting the value of Null/Empty String results in no records being returned. I have a formula to display the values in the report (where I substitute "Unknown" for the Null/Empty String values), but I am not able to do a parameter on that formula.

I guess in general, this is a situation of needing to be able to use a Dynamic Parameter based on a formula field.

Thanks for any assistance!

Edited by: Dragon77 on Apr 2, 2010 9:39 AM

[Former Member · Accepted solution]

I'd use a SQL Command as the data source for the list of values. Something like this... (MS SQL Server)

SELECT DISTINCT
CASE WHEN t.StringField IS NULL OR  t.StringField LIKE '' 
     THEN 'Unknown' ELSE  t.StringField END AS  StringField
FROM TableName AS t
ORDER BY  StringField

Then use a formula like this in the selection criteria.

IF {?Parameter} = "Unknown"
THEN (ISNULL ({TableName.StringField}) OR {TableName.StringField} LIKE "")
ELSE {TableName.StringField} = {?Parameter}

HTH,

Jason

[Adam_Alsop · Accepted solution]

Interesting - i never thought to create a separate Data Source from a command and link it with the other tables. Seems to work fine - I don't even need to add the extra formula for the Record Selection, just {@formula} = {?Param}, since I'm already declaring the Null & "" values as "Unknown" in the {@formula}

Thanks much! I think this will help out with a lot of the parameters I need to use.

Do you have any idea what the performance impact would be with several parameters in one report created like this?

[Former Member · Accepted solution]

Don't link the Command. It's only there to provide the list of values. Yes CR will try to convince you that you should... but don't.

Performance impact... That really depends on the number of rows in the table and weather there is indexing on the string field. A simple select statement like that one shouldn't be too bad in "most" circumstances.

Jason

[Adam_Alsop · Accepted solution]

I would never have thought to not link it. I think it seems to return the same results, linked or not (at least it does for this first report).

Not sure about the indexing - unfortunately, since the end users are SO used to using Excel (ugh!) for their reports, they want to be able to select things any which way. Thinking I may migrate those aspects to BOE BI.

Also found it interesting that, even though I have the "Database is Case-Insensitive" option selected (SQL Server 2005), it IS still case sensitive for the Command - ex, if the db field is ASSET_STATUS, then it has to be that way in the Command, Asset_Status will not work.

[Former Member · Accepted solution]

Linking shouldn't hurt anything per se, based on the fact that the command is based on the table you are linking to. The linking does cause a a comparison of records between the command and the table though and that is causing CR to do more work than is necessary... The command is only providing the lov's. It's not actually contributing to the actual result set, so no need to link.

As far as the case sensitivity thing... SQL Server 2005 is case insensitive in it's default configuration. I do believe that there is a collation setting that will allow the DBA to change that. If you have a DBA you can check w them or contact the software vendor to see the defaults were altered.

For the most part... the SQL in a Command is passed back to the server exactly as it's written, so there shouldn't be anything in CR that affecting that.