1

I have a field with several Null values and several text values. I am using BI Publisher in Word.

How do I apply something like this:

If the values are all null, the result = "N" If the values has even one not null, the result = "Y"

And should this logic be held in the data model in BI Publisher's web interface or in Word's conditional formatting?

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Null2
  • 43
  • 1
  • 2
  • 5

3 Answers3

2

Count of LINE 

<? count(//LIST_LINE/LINE)?>

Count of LINE that has an ELEMENT NULL

<? count(//LIST_LINE/LINE[ELEMENT=""])?>

Compare counts. 

<? If:count(//LIST_LINE/LINE) = count(//LIST_LINE/LINE[ELEMENT=""])?>Y<?end if?>

<? If:count(//LIST_LINE/LINE) != count(//LIST_LINE/LINE[ELEMENT=""])?>N<?end if?>
EdHayes3
  • 1,777
  • 2
  • 16
  • 31
0

Better you set this in data model query only. you can use it in case statement.

case when "field" is null then 'N' else 'Y' end

mona16
  • 46
  • 8
  • I understand you want me to set this on the data model query. I understand you want me to use a case statement. I do not know how or where to apply case statements, nor do I know the required syntax. Please elaborate. – Null2 Mar 31 '16 at 19:55
  • While creating data set in data model you can use sql query to create it. there only you can use "case when...end" syntax. Normaly it will be select statement for columns. check the syntax in below link. http://www.techonthenet.com/sql_server/functions/case.php how you can create Data set in data modeel,plz check below link. https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM147 Hope it will be helpful. – mona16 Apr 01 '16 at 09:16
  • In data set edit the data set and then go to structure tab.There one column will be there with "if null" there u can put N for the. – mona16 Jun 13 '16 at 08:03
0

While creating data set in data model you can use sql query to create it. there only you can use "case when...end" syntax. Normaly it will be select statement for columns.

check the syntax in below link. http://www.techonthenet.com/sql_server/functions/case.php

how you can create Data set in data modeel,plz check below link. https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM147

Hope it will be helpful.

mona16
  • 46
  • 8