Thank you!  I appreciate it!!

Thanks both,

As I am a complete novice wasn't quite sure how to use your solution @dougp, but @MF's answer worked a treat!

However, I did run into a separate issue where when I add a new data item column to the list (not a filter), the number of rows reduce significantly in the report.

e.g. I have created the below list using filters:
ID    | Name
1      | A
2      | B
3      | C
4      | D
5      | E

From here I add a 3rd column, data item called Variable, and the resulting table looks like:
ID    | Name  | Variable
3      | C        | No
4      | D        | Yes

The above I'm guessing resulted as there were no "Variable" data found/assigned (null?) for IDs 1, 2 and 5.
Instead, I would like these null results to show, so that the list is complete as per below:
ID    | Name  | Variable
1      | A        | Null
2      | B        | Null
3      | C        | No
4      | D        | Yes
5      | E        | Null

Can this be done?

Many thanks!

Hi,

Apologies, I'm not quite following the exact steps you took here. Did the filter change when adding the Variable column, and/or is the Variable column referenced in the filter at all?

Cheers!

MF.

This was a lot harder in Cognos than in straight SQL.  Still, only about 10 minutes.

https://pastebin.com/GaKPt5Cv

Hi all,

I do not really have much of a technical background on this and would really appreciate your help.

Column 1     | Column 2
111             |A
111             |B
111             |C
222             |B
222             |C
333             |A
333             |C

In the above Cognos List Report, how do I filter out the unique identifiers in column 1 which include "B" in column 2 (i.e. 111 and 222)?

I would like a result where any numerical identifiers in column 1 which has a "B" in column 2 to be excluded, showing only 333 as per below (given both 111 and 222 have "B" associated):
Column 1     | Column 2
333             |A
333             |C

Thank you!

Hi,

You could take the approach that counts 1 for a 'B' value in Column 2, then filters out Column 1 values that have a maximum flag value greater than 0 in Column 2

eg

maximum(if ([Column 2] = 'B') then (1) else (0) for [Column1]) = 0

Does this work for you?

Cheers!

MF.

I'm unsure about the two first basic questions, as I was not involved in the import process. It seems like it's a dimensional model, though. But I can answer the rest.

[Z] is indeed a level. The related ?p_z? parameter (prompted on the request page) is a multiselectable list of all the third dimension values (as [X] and [Y] are parent ones, between them as well).
The two current basic detail filters for [X] and [Y] are

Code: [Select]

[X] = ?p_x? and

Code: [Select]

[Y] IN ?p_y?.

Finally, if I employ the syntax

Code: [Select]

set([Your level or set of members] -> ?Your Parameter?) I get an error, the full query being:

Code: [Select]

total([Measure] within set ([Z] -> ?p_z?)) / [Measure]
The error isn't too descriptive, it's just a QE-DEF-0260 one saying the syntax failed after the -> token.

Hi,

You can refer to the FAQs post below to find out how to identify a relational package vs a dimensional package:
https://www.cognoise.com/index.php/topic,27563.0.html
The UI has changed a little since the post was added, but the basic concepts still apply.

Let's focus first on [X] and [Y]. You have detail filters for these, and we really need to get rid of them.

Are you displaying [X] and [Y] as items in your report? If so, do the following:

Remove the detail filter for [X], then replace the [X] item being displayed in your report with a query calculation that has the expression [X] -> ?p_x?
Remove the detail filter for [Y], then replace the [Y] item being displayed in your report with a query calculation that has the expression set([Y] -> ?p_y?)

Now on to your expression

To get a prompted set of members for [Z], you use the expression set([Z] -> ?p_z?)
You now want to total your measure across the members of this prompted set, so you use syntax that says total([Measure] within set <your set of members>)

So the full expression for this would be total([Measure] within set set([Z] -> ?p_z?))
Notice the word 'set' occurs twice within the expression - once for 'within set' and once as a set() function.

You should then be able to divide that by your [Measure], giving you an expression

total([Measure] within set set([Z] -> ?p_z?)) / [Measure]

Does this work for you?

Cheers!

MF.