Frustrated with Nested Groups/Lists subtotals

I have a set of three nested lists.

Office_List header sum(fields!Cost.value,"Office_List") sum(fields!Access.value,"Office_List")
Clerk_List header sum(fields!Cost.value,"Clerk_List") sum(fields!Access.value,"Clerk_List")
Order_List header sum(fields!Cost.value,"Order_List") sum(fields!Access.value,"Order_List")
Order_ List detail: Fields!Cost.value Fields!Access.value Fields!Total.value
Order_List Detail: Table_Detail
Order_List footer
Clerk_List Footer
Office_List footer

Order_list groups by order id
Clerk_list groups by clerk id
Office_list groups by office id

Using the sample above for the list header Order_List, I have subtotals for each list grouping. Every single one displays a grandtotal for the whole report, NOT by grouping, even though I've indicated a scope value. Please what am I doing incorrectly here? None of my summaries are recognizing my scopes, spelling is correct. If I have a simple single grouping I do not have any issues, Order_list contains the only detail, one table and three standalone fields.

Again I am indicating scope for each sum but the scope is being ignored. I want a sum of the order cost by clerk and by office, instead I am getting grandtotals.

Thank you for any assistance.

I am just clarifying your question. Please correct me if I am wrong.

I think you want to add the distinct row values by group.At the grop footer you want the grand total of the values displayed.

|||

I do want to add the distinct row values by List group, and maybe that is where the problem lies. I am treating the list group in a similar fashion as I would a table group,and possibly it does not evaluate the same way.

My innermost nested list conatins 6 free form fields, and one table with a group, and a subreport linked by parameter to the order id - the list is grouped by Order ID and works perfectly.

The Next list contains the first list, plus in addition 6 free form fields. It is grouped by Clerk ID, and the free form fields are sums of the free form fields in the previous list based on scope. <for instance a field in the innermost list would be Fields!Cost.value), in the Clerk List I am doing Sum(Fields!Cost.value,"Order_List")>

The last list contains the previous two lists, is grouped by Office ID and contains again 6 free form fields that are sums of the original fields based on scope: ie Sum(Fields!Cost.value,"Office_list")

Below the collection of lists I have 6 grand total fields, for example Sum(fields!Cost.value, "Overview") these are the correct grand totals for the whole report.

When I run the report, the very first innermost list displays perfectly as do the final grand totals. But the totals that are displaying are identical to the grand totals instead of being totals based on group.

Ie List A Office1 4
List B ClerkA 4
List A Office2 4
List B ClerkB 4
GrandTotal 4

Instead of :
List A Office1 1
ListB Clerk 1
List A Office 2 3
ListB ClerkB 3

Grand Total 4

Friendly DataSet Table Names

I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.

I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.

I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)

Thanks,

Josh

Have you tried giving alias to the returned tables (result sets) as well as columns? For example:

create proc sp_RtnAlias
as
select EmployeeID EID from Employees as EmployeeID
go

Then you can access the result set using EmployeeID.EID

Hope I haven't misundstood you.

|||

Here's what I do... and I haven't decided if it is cheesey or not...

declare

@.tbltable(Ordinalintidentity(1,1),tableName varchar(50))

/* Insert a row into the temp table for each table returned by the query*/

INSERTINTO @.tblVALUES('Prospects')

INSERTINTO @.tblVALUES('AccountExecs')

INSERTINTO @.tblVALUES('Regions')

INSERTINTO @.tblVALUES('Products')

INSERTINTO @.tblVALUES('ProspectDescriptors')

INSERTINTO @.tblVALUES('Steps')

INSERTINTO @.tblVALUES('ProspectTypes')

INSERTINTO @.tblVALUES('VendorTypes')

INSERTINTO @.tblVALUES('Clients')

-- Index table

SELECT Ordinal, TableName

FROM @.Tbl

ORDERBY Ordinal

The rest of the sproc is a series of selects in the same order as they appear in the index table. I have method in the program that loops through the table objects and assigns each one the appropriate name from the index table. Note that the index table does not contain a reference to itself and can be disposed of when the other table names have been assigned...