Oracle Business Intelligence

Products Banner

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

Received Response

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!



  • Jerry Casey
    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.


  • Dennis Hancy
    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.


  • Jerry Casey
    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
    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.


  • Jerry Casey
    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}