How to aggregate several rows into one

views:

228

answers:

+1 Q:

How to aggregate several rows into one

Sounds like you may want to look into creating your own calculated fields that would contain the aggregated totals.

Edit: Like in my comment to the other answer: create a different dataset which will aggregate the data in the manner you want. Unless you are dealing with monstrous rows returned, this should not be much of a performance issue, and in the long run it's probably a lot better than trying to hack together a solution off of a dataset which may not be suitable.

TheTXI 2009-06-10 14:52:21

The data is fine as is, and s/he doesn't need any calculated fields. Yes aggregation would help but s/he just needs to learn how to use the table control.

Peter 2009-06-10 16:00:32

+3 A:

Why don't you just drop the taxid from the select and grouping clause of the SQL query? That will get you what you want.

msvcyc 2009-06-10 14:55:21

Yeah, changing the queries (or just using a different dataset with the different queries if you need that information elsewhere) would be by far the simplest solution. I originally thought of suggesting that myself but wasn't sure if it would be feasible for her.

TheTXI 2009-06-10 15:03:36

Unfortunately, I can't drop the taxID because its used by other data sections on the report.

Juliet 2009-06-10 15:12:28

Then do what I suggested and create a different dataset which will aggregate the data in the manner you want. Unless you are dealing with monstrous rows returned, this should not be much of a performance issue, and in the long run it's probably a lot better than trying to hack together a solution off of a dataset which may not be suitable.

TheTXI 2009-06-10 15:15:17

@TheTXI: The elements are not properly positioned on the table control.

Peter 2009-06-10 15:17:21

TheTXI: unfortunately, the report is a monster which takes around 40 minutes to run. The bulk of the runtime is caused specifically because it executes a different versions of the same stored procedure, each of which returns with and without specific columns. I've re-written my query to return everything used on the report in one big dataset, and although it takes 10 minutes to run, its a huge improvement over what we already have.

Juliet 2009-06-10 15:27:42

Looks like you put your headers in the grouping row and your data in the detail. Put the headers in the header row, the data in the grouping row, and delete or hide the detail.

Peter 2009-06-10 15:15:35

This does exactly what I want, thank you :)

Juliet 2009-06-10 16:12:26

By the way, it looks like someone downvoted this (I can tell because I've upvoted it and the score is still zero). I've upvoted one of your other answers to compensate. One last thing: using your suggestion I've removed some of the nasty hackage in this report and its runtime down from 40 minutes to 8 minutes. Thank you again :)

Juliet 2009-06-10 20:23:49

Glad to be of assistance.

Peter 2009-06-10 20:43:17

I thought you might have been trolling, honestly.

Peter 2009-06-10 22:00:13

ansaurus

tags:

views:

answers:

How to aggregate several rows into one

related questions