Does Dataset will sum the same item value added to it?

[bryan523]

Hi all,

I got one problem which the dataset would not sum the value of the same item add to it. I have a SQL statement :

Code: Select all

Select item, s_type, color, sum(total_item) From storeTable Group By item, s_type,color 

• item s_type color sum(total_item)
  Book Math Red 10
  Pencil Sharp Black 2
  Book Math Yellow 5
  Book Music Red 8

My BarChart category-value = item; vertical value = sum(total_item); series = s_type.

Then the result Bar chart show that the data Book, Math is only 5 but not (5+10) 15.

The SQL statement is fix because its also use to show at table, the only problem is the bar chart problem which show the wrong result.

Can someone please guide me.

RGDS,
Bryan

[Taqua]

Hi,

summing up your data is not the responsibility of the DataSet. The database will do this.

your results perfectly match your SQL statement. The sum-function of SQL sums all items in a group. The group's contents are defined by the "GROUP BY" clause, in your case it is item, s_type and color.

As the color of both books is different, these rows define different items. Now, if you suddenly change your view and use the resultset as if the GROUP BY clause would have been "item, s_type" (note the missing color in the comparison), then it is no wonder that your results are wrong.

My assumption is that you use a simple for-loop to fill the dataset, and that your (Book,Math,Yellow) item replaces the (Book, Math, Red) item when populating the dataset.

Fix your query or your chart definition and your problems will go away.

Regards,
Thomas

[bryan523]

Hi Taqua,

Thanks for ur reply. Yes its definitely the Group by problem that i need to used this query to show the different data in the table by "color" but for chart just the "item" and "s_type" only. I only can used one query to do this table and chart because its display at the same page of my report.

I have leak of idea how to implement the for loop at the dataset, can you please guide me the way of do it? thanks!


RGDS,
Bryan