Creating aggregated report output ?

[Lars I]

Hi,

I've used SSRS in the past, and here it was simple to group together details data with a "group by" clause.

This is very different i TRS, and I'm stumbling on how to do it. Apparently "groups" in TRS is all about showing things grouped, not about aggregating.

I have a database view that offers all details, and I have a single report parameter to filter the view (based on a single comparison).

I want to group together the details rows based on 4 keys, having the rest of the fields being SUM()'d or aggregated by other aggregate functions.

How do I basically do that in TRS ? It's not very obvious.

I've tried using Crosstab's, but they just add one table for each original details row.

Cheers

Lars

[Lars I]

My initial problem was trying to adapt a report made by the "Band Report" template. I've now utilized the "Crosstab Wizard", which got me somewhat further, but I'm not there yet.

To exemplify my predicament, I made a screen dump of my problem (see link below).

My Crosstab has the key values in the first 7 columns (shown in gray as per the "Corporate" style template).

I want all the details in the colored boxes aggregated.

I want to "SUM" the red values, "FIRST" the green values, "MIN" the blue, "MAX" the purple, and "SUM" the yellow.

How do I do that ?

Picture via Dropbox

[Lars I]

Maybe I ought to mention, that I deleted all rows that the wizard created, except the first and the last. I don't need the sub-totals on individual keys.

[Lars I]

So, I actually instead need to ditch the details row, and keep the sub-total on the lowest/inner-most level. This is what I need.

I was able to delete all sub-total rows just fine before, but I'm apparently unable to delete the details row without also deleting the remaining sub-totals row !?!

How can I get rid of the details row altogether ?

[Lars I]

I found a way thru the maze. I'm leaving bread crumbs in case others are attempting the same.

Use the Crosstab wizard, and choose the "Stepped Layout" option.

In the Crosstab setup add the aggregation/key values to both the RowGroup and Details panes. Set the latter to FIRST().

Add other relevant values to the Details pane as usual.

After the wizard finishes, delete the first column, and all rows except the next-to-last, which is the one which contains the wanted aggregations.

Cheers.

[Lars I]

*all data rows*