How to skip null values in sap.viz.ui5.data.FlattenedDataset

[Former Member]

Hi Experts,

I need to skip null values from Viz Charts in SapUI5. Please post solution for this.

my code is ,

var oModel = new sap.ui.model.json.JSONModel({

  businessData : [

  {Country :null,revenue : 0,profit: 0, population:0},

  {Country :"Canada",revenue:410.87,profit:-141.25, population:34789000},

  {Country :"China",revenue:338.29,profit:133.82, population:1339724852},

  {Country :"France",revenue:487.66,profit:348.76, population:65350000},

  {Country :"Germany",revenue:470.23,profit:217.29, population:81799600},

  {Country :"India",revenue:170.93,profit:117.00, population:1210193422},

  {Country :"United States",revenue:905.08,profit:609.16, population:313490000}

  ]

  });

    // A Dataset defines how the model data is mapped to the chart

  var oDataset = new sap.viz.ui5.data.FlattenedDataset({

  dimensions : [

  {

  axis : 1,

  name : 'Country',

  value : "{Country}"

  }

  ],

  measures : [

  {

  name : 'Profit',

  value : '{profit}'

  },

  {

  name : 'Revenue',

  value : '{revenue}'

  }

  ],

  data : {

  path : "/businessData"

  }

  });

  var oBarChart = new sap.viz.ui5.Bar({

  width : "80%",

  height : "400px",

  plotArea : {

  },

  title : {

  visible : true,

  text : 'Profit and Revenue By Country'

  },

  dataset : oDataset

  });

  oBarChart.setModel(oModel);

  return(oBarChart);

In output page i am getting a  value as NO VALUE . I need to skip this from my output vizchart.

Thanks,

Sandeep

[Former Member · Accepted solution]

Hi Sandeep You can use SAPUI5 Filter For Solve your problem.

Please refer the following modified code.

			var oModel = new sap.ui.model.json.JSONModel({
			businessData : [
			{Country :null,revenue : 0,profit: 0, population:0},
			{Country :"Canada",revenue:410.87,profit:-141.25, population:34789000},
			{Country :"China",revenue:338.29,profit:133.82, population:1339724852},
			{Country :"France",revenue:487.66,profit:348.76, population:65350000},
			{Country :"Germany",revenue:470.23,profit:217.29, population:81799600},
			{Country :"India",revenue:170.93,profit:117.00, population:1210193422},
			{Country :"United States",revenue:905.08,profit:609.16, population:313490000}
			]
			});
			var filter = new sap.ui.model.Filter("Country", function(oValue){
			return oValue!==null;
			});
			// A Dataset defines how the model data is mapped to the chart
			var oDataset = new sap.viz.ui5.data.FlattenedDataset({
			dimensions : [
			{
			axis : 1,
			name : 'Country',
			value : "{Country}"
			}
			],
			measures : [
			{
			name : 'Profit',
			value : '{profit}'
			},
			{
			name : 'Revenue',
			value : '{revenue}'
			}
			],
			/* data : {
			path : "/businessData"
			}*/
			}).bindData("/businessData",null,null,[filter]);
			var oBarChart = new sap.viz.ui5.Bar({
			width : "80%",
			height : "400px",
			plotArea : {
			},
			title : {
			visible : true,
			text : 'Profit and Revenue By Country'
			},
			dataset : oDataset
			});
			oBarChart.setModel(oModel);
			return(oBarChart);

Please Check Once.And if you face any problem.Ask Me....

Always Happy To Help You...

[Former Member · Accepted solution]

Thank you Siby Augustine 🙂

This works fine .

I am new to sapUI5. I have one more query.This is a part of my previous question .It will very helpfull if you provide some suggestion on this.

You have got value of Country and skipped null values. Same way I need to get the value of other variables.

i am sharing snippet of my code to bind odatavalues in JSON model.

var url = "/xxx/xxx/services.xsodata/uid(xid=" + xid + ",y_id=" + y_id + ",xx_id=" + xx_id +

  ",yy_id=" + yy-id + ",xxx_id=" + sessionStorage.getItem("xxx_id") + ",yyy_id=" + sessionStorage.getItem(

  "yyy_id") + ")/Results/?$format=json";

var JsonModel = new sap.ui.model.json.JSONModel();

JsonModel.loadData(url);

sample structure of odata i am recieving through this url  is ,

{"d":{"xxx":

[

{"__metadata": {"type":"xxx.yyy.XType","uri":"https://xxx/X('11679083434033441')"},"STATUS_VALUE":"11679083434033441","X_ORDER":1,"STATUS":2,"STATUS_TEXT":"Completed","PD_ID":null,"COUNTRY":null,"CNT":"0","TOT_CNT":"14885"},

{"__metadata": {"type":"xxx.yyy.XType","uri":"https://xxx/X('11679083434033442')"},"STATUS_VALUE":"11679083434033442","X_ORDER":2,"STATUS":1,"STATUS_TEXT":"In Progress","PD_ID":null,"COUNTRY":null,"CNT":"0","TOT_CNT":"14885"},

{"__metadata": {"type":"xxx.yyy.XType","uri":"https://xxx/X('11679083434033443')"},"STATUS_VALUE":"11679083434033443","X_ORDER":3,"STATUS":0,"STATUS_TEXT":"Not Started","PD_ID":253,"COUNTRY":"GSS Singapore","CNT":"14885","TOT_CNT":"14885"}

]

}

}

you have filtered country from this. Same way  i need to get value of STATUS_TEXT too.

because i need to assign colorPalette in plotArea based on the value of STATUS_TEXT .

Thanks,

Sandeep

[Former Member · Accepted solution]

I didnt understand the question properly.But any how  i modified your first snippet code for your new requirement(as per my perception).Please check the code once if you face any issues or other requirement let me know

/*You Can You formatter function for manipulate each values in the model */.

var oModel = new sap.ui.model.json.JSONModel({

    businessData : [

   {Country :null,revenue : 0,profit: 0, population:0},

   {Country :"Canada",revenue:410.87,profit:-141.25, population:34789000},

   {Country :"China",revenue:338.29,profit:133.82, population:1339724852},

   {Country :"France",revenue:487.66,profit:348.76, population:65350000},

   {Country :"Germany",revenue:470.23,profit:217.29, population:81799600},

   {Country :"India",revenue:170.93,profit:117.00, population:1210193422},

   {Country :"United States",revenue:905.08,profit:609.16, population:313490000}

   ]

   });

   var filter = new sap.ui.model.Filter("Country", function(oValue){

  return oValue!==null;

   });

     // A Dataset defines how the model data is mapped to the chart

   var oDataset = new sap.viz.ui5.data.FlattenedDataset({

   dimensions : [

   {

   axis : 1,

   name : 'Country',

   value : "{Country}"

   }

   ],

   measures : [

   {

   name : 'Profit',

   //value : '{profit}'

   value : {

   path : "profit",

   formatter : function(oVal){

   if(oVal>10){

   oBarChart.getAggregation('plotArea').setColorPalette(['#00B050','#CCFF66']);

   }

   return oVal;

   }

   }

   },

   {

   name : 'Revenue',

   value : '{revenue}'

   }

   ],

  /* data : {

   path : "/businessData"

   }*/

   }).bindData("/businessData",null,null,[filter]);

   var oBarChart = new sap.viz.ui5.Bar({

   width : "80%",

   height : "400px",

   plotArea : {

   'colorPalette' : ['#FF0000','#FFC300']

   },

   title : {

   visible : true,

   text : 'Profit and Revenue By Country'

   },

   dataset : oDataset

   });

   oBarChart.setModel(oModel);

   return(oBarChart);

[Former Member · Accepted solution]

Hi tried your code .But it doesn't works properly as per my condition.

I have 6 countries . In which 3 countries have profit less than 0 and 3 greater than 0.

I have made condition like if profit is greater than 0 then color is "green" orelse "red".

But my code applies red color to all countries irrespective of profit.

I need color to be assigned based on the value of profit, can you please suggest some solution for this ???

var oModel = new sap.ui.model.json.JSONModel({

  businessData: [{

  Country: null,

  profit: 0,

  population: 0

  }, {

  Country: "Canada",

  profit: -141.25,

  population: 34789000

  }, {

  Country: "China",

  profit: 133.82,

  population: 1339724852

  }, {

  Country: "France",

  profit: 148.76,

  population: 65350000

  }, {

  Country: "Germany",

  profit: -117.29,

  population: 81799600

  }, {

  Country: "India",

  profit: 117.00,

  population: 1210193422

  }, {

  Country: "United States",

  profit: -109.16,

  population: 313490000

  }]

  });

  var filter = new sap.ui.model.Filter("Country", function(oValue) {

  return oValue !== null;

  });

  // A Dataset defines how the model data is mapped to the chart

  var oDataset = new sap.viz.ui5.data.FlattenedDataset({

  dimensions: [{

  axis: 1,

  name: 'Country',

  value: "{Country}"

  }],

  measures: [{

  name: 'Profit',

  //value : '{profit}'

  value: {

  path: "profit",

  formatter: function(oVal) {

  if (oVal > 0) {

  oBarChart.getAggregation('plotArea').setColorPalette(['#00B050']);

  } else {

  oBarChart.getAggregation('plotArea').setColorPalette(['#FF0000']);

  }

  return oVal;

  }

  }

  }]

  }).bindData("/businessData", null, null, [filter]);

  var oBarChart = new sap.viz.ui5.Bar({

  width: "80%",

  height: "400px",

  plotArea: {

  },

  title: {

  visible: true,

  text: 'Profit By Country'

  },

  dataset: oDataset

  });

  oBarChart.setModel(oModel);

  return (oBarChart);