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

Dennis Hancy

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

Hi Dennis,

If there are any pivot tables involved, have you placed any aggregation-related columns in the EXCLUDE area?  I know that has generated that error for me in the past.

Jerry

Dennis Hancy

Thanks Jerry, for your reply.

I did have one aggregate column in the exclude area.  I moved it to columns and measures when I saw your reply, and marked it as hidden instead.  Unfortunately I am still getting the same error.

It seems that any function I apply to the stand-alone ReportTable"."File Name" column results in the same error.  Just for fun, I've tried things like UPPER and TRIM in the formula, but none of those seem to work either.

Dennis

Jerry Casey

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?

Dennis Hancy

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

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}