[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list

Question

I have an analysis that includes a column with this formula:

IFNULL('@{pv_FileName}',"ReportTable"."File Name")

"ReportTable"."FileName" is part of a hierarchy in my analysis.  I also include this field as a hidden column on my report.

@{pv_FileName) is a presentation variable I set via a prompt.  It is being populated correctly.

I am getting the error in the subject line when I run this.

One posting I saw on this subject said that a bug was open for this issue (Bug 12664636), although that was eight years ago.

Another posting I saw suggested unchecking the box "Report-Based Total (when applicable)".  I tried this on both my "IFNULL" column as well as the hidden "ReportTable".FileName" column.  But I am still seeing the error.

Any suggestions?  Thanks!

Dennis

Jerry Casey · Answer

I hope I'm not being pedantic, but @{presvar}{default value} is how you specify a default value for your presentation variable.

I only say that because I don't think of that as "concatenating the two".

My only remaining suggestion to try is the alternate syntax for a presentation variable that seems to work in some situations: @{dashboard.variables['pv_FileName']}{optional default value}

Dennis Hancy · Answer

Hi Jerry,

The only way pv_FileName can be null is if the user does not select any value for the prompt (which seems to be the same as a "select all" option).

I did try your suggestion of concatenating the two, but it also gave me the same error.

Dennis

Jerry Casey · Answer

I'm just throwing out ideas here, but can you use  @{pv_FileName}{"ReportTable"."FileName"} instead of the IFNULL statement?

If pv_FileName is being correctly populated by the prompt, how is it ever null?