[Solved] Change the place of a dynamic sum cell

[bogdaboco]

Hello,

When I make a list ( or a crosstab) and I make a sum on one of my columns, the dynamic sum cell would appear on the bottom of my column.

I am trying to put it on the top, but it doesn't work...

Can anybody help me?

Thx for your responses...

[JoeBass]

If you want the sum at the top and the details below, I think you'll have to create a second query that will provide the sums for you and insert a new list above your details.

[bogdaboco]

Ouch! All that for that?
Thx for your response, JoeBass, but anybody has another solution more simple?

[JoeBass]

I'd love to hear an easier solution but, I don't see how its possible - when reports are run, the entire result set is not necessarily retrieved when run to html.

[CoginAustin]

There isnt an easier way. Create a tabular set. Add you new sum row to one side with the proper grouping(eg "sum of group")
Add a field called SortORder to each tabular model. Set Sortorder='1' for you sum side
and sort order='2' for your data side.

Add this sort order manually to your Query Dimension and set your Sort to ASC.

If you need the line seperating the sum then you just need to add a boolean variable that looks at the sort order. If sortorder='1' then show line and bold it.

[JoeBass]

Hi CogninAustin, I've tried to put your solution together but, I haven't been able to get the total function to work as you describe in "Add you new sum row to one side with the proper grouping(eg "sum of group")". 

Basically, I have no experience with tabular sets and I'd like to know how you put it together if you can explain it a little further.

Thanks

[CoginAustin]

Add a Query
Add a Tabular Set to this query
Add a Tabular model

Get the data you need in your report done in this TM. This will be your entire crosstab minus the summary line(The easy way is to create your crosstab, copy the TM, and paste it here)

Lets say you fetch ID, Group, Name, Amount from this TM(tabular model)

Now, copy this TM to the other side of your set
Change your Name field to equal 'Total' (you just want this single value) as it is going to add this row(total) to each Group

Now move your query items to your query level dimensions and facts. Now create your crosstab just as you would normally but make sure you use Query items and not model items.

[H_Goetz]

Hi bogdaboco,

Here comes another way to solve your problem, at least in list objects.
Create your list and add the summ value as you did (at the bottom).
Then open the List Headers & Footers in the Structure menu.
The overall footer might be checked (there is your calculation at the moment).
Check also the Overall header and close the dialogue.
Now you have a new line at the top of your list.
Simply move the calculated value to the top row with Drag&Drop and there you go.
That's all.

But I haven't done it with crosstabs yet.

Good luck
Harald

[JoeBass]

I guess what I'm going to go ahead and do then is, shut up.  Thanks for the how-tos CogninAustin and H Goetz! 

Here's an attachment of their approaches.

[bogdaboco]

Ok.
Thanks to every body for your replies
Especially to CoginAustin : your solution is not easy, but works very well!

[bogdaboco]

Hello,

When I make a list ( or a crosstab) and I make a sum on one of my columns, the dynamic sum cell would appear on the bottom of my column.

I am trying to put it on the top, but it doesn't work...

Can anybody help me?

Thx for your responses...