blank values not being passed as NULL into live reports in new version

Hi:We are currently using the following version of the live report and extentions module:Livelink LiveReports 2.2.0 Livelink LiveReport Extensions 3.0.1 We did not previously have Livereport Extensions. After we installed Livereport Extensions, a blank parameter is not being passed to the livereports as NULL. In our reports, we have an ALL value in additional to specific values for one of the filters. When the user selects ALL, a blank value is passed through the url to the livereport. In our stored proc, we do thisWHERE (blah=@input or blah is NULL) so that we can use it to toggle whether we want to output ALL the data or just a specific value that the user selected. When we execute the stored proc directly on the db and put NULL in the parameter we get all results returned. However, when we run the report with ALL selected, we get no results. This was not the case in 951.

Find more posts tagged with

Comments

Darren_Hodder

Hi Kristy,We are currently not aware of any issues relating to what you are seeing in this version of LiveReport Extensions, but to be sure would you be able to send us a bit more information about your setup? It would be useful to see your LiveReport setup, could you take a screenshot of the LiveReport edit page, and we will try to recreate what you are seeing here? You can post it to this discussion or emal us directly at support@resonatekt.com or myself at dhodder@resonatekt.com.Cheers,Darren HodderResonateKT Supporthttp://www.resonatekt.com

Kristy_Shen

Thanks for your response Darren. I've opened a ticket (470010) for this issue with opentext support. The report and stored proc has been attached to this ticket. I've tried uninstalling the livereport extensions module in our env and this resolves the issue. Thus, I'm certain that it is the reason why the "ALL" option on our report is not returning any values.

Suncor_ECM_Platform_Support

At Suncor, we had a similar issue, where without Live Report extensions, passing an empty string in would capture the nulls, but afterwards it wouldn't. In SQL Server anyway, my solution was to use the IsNull() function, where the first argument is the column you are checking, and the second argument is the default value you want to replace NULL with. In this case, I chose an empty string.

So if you have a WHERE clause like WHERE MYVALUE=%1, where %1 is a user input string, and you pass in an empty value, you would rewrite this as

WHERE ISNULL(MyValue, "")=%1 in your Live report, and that should work. It depends on whether your database is storing nulls or truly is storing empty strings.

-Hugh Ferguson

Suncor