Receiving and sending a cursor with(in) a Stored Procedure

Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

PeterCursors are a really poor choice in MS-SQL. It would appear that you are trying to use Oracle-like logic in SQL Server, which is a receipe for disaster.

Can you explain in a bit more detail what you are planning to do? I suspect that there is a much better way to do the job once we understand what you are trying to accomplish.

-PatP|||Hi Pat:

Maybe I should have said "result set" or "table" - I'm fairly new with SS.

Here's more detail:

A web app has a screen where the user enters order header information - name, address, etc. - this would be the tblOrder - 1 record. Then they enter the order detail - say 6 individual sales items - tblDetail - 6 records. All these entries are done on 1 screen in the web app.

Upon save, a stored procedure is called that will do 2 things.

1. It will save the tblOrder record and generate the PK for this record
2. It will save the 6 records into tblDetail including stuffing the PK from the tblOrder into a FK field within the tblDetail records.

1 and 2 would probably be wrapped in a transaction in case either fails it could Rollback. Otherwise, Commit.

The approach within the SP is basically what I'm after. Does that make sense?

Thanks,

Peter|||Write one stored procedure that saves your order record and returns the generated key value to your interface.

Write a second stored procedure that that save the detail records, including the order key returned by the first procedure.

Your interface should save the order, and then loop through the detail records calling an insert for each one.|||Blindman:

I was hoping for some code to show the passing of the detail record set from the view tier to the SP.

Secondly - I'd rather fail both the Order record creation and Part records creation if either fails.

Wouldn't it be better to have both processes in the same stored procedure? Otherwise, end up with an order and no detail?

Is there anything preventing me from doing both in 1 stored procedure? If not, how do I pass the needed data - that's the key to my question.

Thanks for helping,

Peter|||The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.|||Hi - a coded example of the stored procedure would be most helpful to this newbie. I understand that detailed record set is passed as a parameter. My original question would be to see the code of the Stored Procedure handling the receipt and processing of the detailed record set.

Thanks,

Peter|||Create Procedure DetailInsert(@.OrderID as int, @.DetailInfo as varchar(50))
as
begin
insert into DetailTable (OrderID, DetailInfo) values (@.OrderID, @.DetailInfo)
end|||So detailInfo is a record set? It looks like a single field. If it is a record set, are the values comma delimited or is it simply a reference to a record set that was established in the calling program?

This is where I'm confused. I can handle a SP that inserts a single record into a DB. I'm trying to understand a situation where multiple records are submitted at once.

Thanks,

peter|||I have no idea what @.DetailInfo is. That is up to you. It is a dummy parameter that represents all the values you need to submit to the procedure. Submit each value as a separate parameter.

Peter, have you even TRIED to look up how to write Stored Procedure in Books Online?|||Blindman:

I have looked up many things with books online. I thought this might be a place to zero in efficiently for a solution. As someone new, I'm struggling. I apologize that I didn't understand the purpose of this board and to have taken valuable time away from you.

I have numerous stored procedures that work to insert and update single records. I have some stored procedures to query and return a record set. What I haven't had success in is having a stored procedure receive a group of child records along with parent and save both. If either fails, I want to rollback - that's why I thought there would be a benefit to combining the operations into a single SP transaction.|||The answer here is easy.

This is not Oracle

You can not do what you are thinking unless you use bcp or bulk insert.

In either case that means you'd have to create a file, which I don't think is a good idea.

You need to iterate through your rs and make a stored procedure call for each record set.

Now you could put all of the data in to 1 string, pass it to a sproc, the "unstring" it in the sproc...but that would be overkill.

Use 2 sprocs like the blind dude said.

Pass the id as an output variable from the first sproc.

In the second sproc use error checking. If anything fails, perform a delete of the initial record.

Sorry|||You can't set up a stored procedure that will recieve a group of records. They can only accept input parameters.

I recommend that you either change your application design to one that submits new data one record at a time, or look into other options available through your development interface for handling recordset. I'm not much of an interface programmer, so I can't help you with that, but you might check one of the other sections of DB Forums.

Sorry if I was short with you. Been sick today. I readily answer question such as "How do I write a stored procedure to do X", but I don't answer questions like "How do I write a stored procedure."

If you have specific questions, please do post them on this forum and I or somebody else will assist you.|||Thank you Brett and Blindman. I now understand that the best way to handle is from the interface, not at SQL server.

I hope you are feeling better Blindman.

thanks,

Peter|||Sick?

Don't you listen to anything I say?

A bottle of tequila will take care of all those germs|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?Have you ever tried using a bottle of Tequila to smash the germs? I'd like the video rights, and would make you a very handsome deal.

Scalable? How about many cases of Tequila?

-PatP

Receiving "Error: 18456" when for a specific user who has rights via AD Group

I have a user who is a domain admin and has rights based on a group
policy (OURDOMAIN\Developers) to access the SQL Server. He can access
other Sql Servers, and every other member of the group can access all
boxes. So its a case of a specific users cannot access a specific
box. Below is the error message he is getting:
TITLE: Connect to Server
--
Cannot connect to SQLDEV1.
--
ADDITIONAL INFORMATION:
Login failed for user 'OURDOMAIN\user'. (Microsoft SQL Server, Error:
18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
--
BUTTONS:
OK
--
SQL2005 Ent SP1 on Win03
Any ideas would be appreciated.Hi
"Keeper of the Bits" wrote:
> I have a user who is a domain admin and has rights based on a group
> policy (OURDOMAIN\Developers) to access the SQL Server. He can access
> other Sql Servers, and every other member of the group can access all
> boxes. So its a case of a specific users cannot access a specific
> box. Below is the error message he is getting:
>
> TITLE: Connect to Server
> --
> Cannot connect to SQLDEV1.
> --
> ADDITIONAL INFORMATION:
> Login failed for user 'OURDOMAIN\user'. (Microsoft SQL Server, Error:
> 18456)
> For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
>
> SQL2005 Ent SP1 on Win03
> Any ideas would be appreciated.
>
Maybe you are seeing http://support.microsoft.com/kb/925744/en-us I believe
3152 is the latest version you can download if you want to move past SP2.
John|||You can more details about login failures from the SQL
Server error log on SQL Server 2005. Check the log for the
State - there will be a line similar to:
Logon Error: 18456, Severity: 14, State:18
Then check the corresponding description for that state in
the following article:
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
-Sue
On 13 Apr 2007 12:06:22 -0700, "Keeper of the Bits"
<styree@.creditsolutions.com> wrote:
>I have a user who is a domain admin and has rights based on a group
>policy (OURDOMAIN\Developers) to access the SQL Server. He can access
>other Sql Servers, and every other member of the group can access all
>boxes. So its a case of a specific users cannot access a specific
>box. Below is the error message he is getting:
>
>TITLE: Connect to Server
>--
>Cannot connect to SQLDEV1.
>--
>ADDITIONAL INFORMATION:
>Login failed for user 'OURDOMAIN\user'. (Microsoft SQL Server, Error:
>18456)
>For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
>--
>BUTTONS:
>OK
>--
>
>
>SQL2005 Ent SP1 on Win03
>Any ideas would be appreciated.

Rebuild Master

Found this on the other day in this news group.
How To Rebuild the MASTER Database on a Virtual SQL Server
2000 Instance
http://support.microsoft.com/default.aspx?scid=kb;en-
us;298568
Regards
Chip
>--Original Message--
>Are there any 'gotchas' that one needs to consider before
running this on a
>single node - anything different than a stand-alone
server?
>Thanks!
>Darryl Pollock
>Squirrel Consulting
>
>.
>
Apart from KB 298568, you will need to reapply any SQL hotfixes and security patches. (You do not have to reapply any SQL Service pack though).
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

rebuild a database from backupfile only?

Hi NG,
in case this is not the right news group, please advise me to the right one
for my question.
How can I rebuild a database from a plain backup?
Here is my problem:
From within a C# Program I am generating backups of my database by sending
the following sql commands:
BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"
as well as:
BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT,
NAME
= N'OekoTemp-Transaktionsprotokoll backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
This works fine, and I am able to do a restore from within my C# program by
sending the sql command:
RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
But if the original .mdf and .log files are deleted, I can no longer do a
restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.
When ever and how ever I try to restore my database from the plain backup I
receive error messages like (translated from german):
The backup set contains backups for a other database as the present
'OekoTemp'-Database.
Message 3013, Level 16, Status 1, Line 1
RESTORE DATABASE terminated due to errors.
As said : the OekoTemp-Database is not existing any more at all so how can
it be present like mentioned in the error message?
Thanks for any help and hints!
Rainer QueckRainer Queck wrote:
> Hi NG,
> in case this is not the right news group, please advise me to the right on
e
> for my question.
> How can I rebuild a database from a plain backup?
> Here is my problem:
> From within a C# Program I am generating backups of my database by sending
> the following sql commands:
> BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
> DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
> backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"
> as well as:
> BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT
, NAME
> = N'OekoTemp-Transaktionsprotokoll backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> This works fine, and I am able to do a restore from within my C# program b
y
> sending the sql command:
> RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
> But if the original .mdf and .log files are deleted, I can no longer do a
> restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.
> When ever and how ever I try to restore my database from the plain backup
I
> receive error messages like (translated from german):
> The backup set contains backups for a other database as the present
> 'OekoTemp'-Database.
> Message 3013, Level 16, Status 1, Line 1
> RESTORE DATABASE terminated due to errors.
> As said : the OekoTemp-Database is not existing any more at all so how can
> it be present like mentioned in the error message?
> Thanks for any help and hints!
> Rainer Queck
>
>
Hi Rainer,
If a database with the name "OekoTemp" doesn't exists, there shouldn't
be a problem in restoring it with the command you issue. You say that
the original mdf and ldf files doesn't exists, but does that mean that
the files has been deleted manually but the database still exists in SQL
server?
If the database exists and you'd like to restore the backup to this
database, you'll have to use the REPLACE option of the restore command.
I'll suggest that you read up on the RESTORE DATABASE command/syntax in
Books On Line - that will hopefully help you.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in m
essage
news:ePsOMaFbHHA.3616@.TK2MSFTNGP05.phx.gbl...
> Rainer Queck wrote:
> Hi Rainer,
> If a database with the name "OekoTemp" doesn't exists, there shouldn't be
a problem in restoring
> it with the command you issue. You say that the original mdf and ldf file
s doesn't exists, but
> does that mean that the files has been deleted manually but the database s
till exists in SQL
> server?
> If the database exists and you'd like to restore the backup to this databa
se, you'll have to use
> the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in Bo
oks On Line - that will
> hopefully help you.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||Hello Steen,
thanks for your fast reply!
I didn't expect a answer that fast, since I have the same question placed in
a german ng since 4 days without a answer.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> schrieb im
Newsbeitrag news:ePsOMaFbHHA.3616@.TK2MSFTNGP05.phx.gbl...
> If a database with the name "OekoTemp" doesn't exists, there shouldn't be
> a problem in restoring it with the command you issue. You say that the
> original mdf and ldf files doesn't exists, but does that mean that the
> files has been deleted manually but the database still exists in SQL
> server?
Yes and no. My intention is to backup my database in intervals and then by
program execute a script which drops / creates my database.
This way I get backups for certain periods in time. Then if it is necessary
to load a "old" database I thought I could do a "backup current restore old
database".
Well this did not (yet) work. So I manually deletet my database in SQL
SERVER 2005 and then deleted the database files, hoping to be able to now do
a restore of the old database. This did not work (Yet) and I received the
errormessages mentioned before.

> If the database exists and you'd like to restore the backup to this
> database, you'll have to use the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in
> Books On Line - that will hopefully help you.
Thanks for the hints. But as you can see by my original post, I am using the
REPLACE option on my restore efforts, but may be there is still a wrong
selection of options in my sql?
Regards
Rainer Queck|||Hi Tibor,
thanks for the additional hint.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23xCFMfFbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.
How would the complete SQL string look like for my case?
Since the project I am working on is a weekend project, I won't be able to
continue with my tries before saturday.
Also the current database is not yet important since the project is under
developement and I am currently working with test data and therefore a
dataloss is not yet a pain ;-)
Still it would be great, if I could get this problem solved soon.
Regards
Rainer|||Hello Rainer,
I understand that you cannot restore a database when the existing mdf/ldf
is remmoved and the database does not exist on the server. If I'm off-base,
please let me know.
You may first run fist command:
restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak'
Please see the Databasename in the result, and this shall be the database
name you use in restore command. Please double check the name is correct.
From the error message you encounter, it seems the database name is not
correct in your restore command
In my sample, it is dbacc. If run the command you mentioned, you may see
the following error message:
RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc_log' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If so, you need to use "with move" statmend in restore command. For example:
RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
move 'dbacc' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'
If the issue still occurs, it might be the backup file is corrupted. You
may want to try a new backup/restore to test. Also, you may want to test
directly in QA or mangement studio(2005) to see if there is any difference
More related informaiton:
Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
http://support.microsoft.com/kb/304692/
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions or concerns. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
thank you very much for your detailed answer.
I will follow your suggestions as soon as I continue work on my project
(saturday) and let you know if I was successful or need more help.
Considering the answers I have up to now, there is hope ;-)
Regards
Rainer
"Peter Yang [MSFT]" <petery@.online.microsoft.com> schrieb im Newsbeitrag
news:bYXod$GbHHA.552@.TK2MSFTNGHUB02.phx.gbl...
> Hello Rainer,
> I understand that you cannot restore a database when the existing mdf/ldf
> is remmoved and the database does not exist on the server. If I'm
> off-base,
> please let me know.
> You may first run fist command:
> restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak'
>
> Please see the Databasename in the result, and this shall be the database
> name you use in restore command. Please double check the name is correct.
> From the error message you encounter, it seems the database name is not
> correct in your restore command
>
> In my sample, it is dbacc. If run the command you mentioned, you may see
> the following error message:
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
>
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file
> "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc_log' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous
> messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> If so, you need to use "with move" statmend in restore command. For
> example:
>
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
> move 'dbacc' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
> move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'
> If the issue still occurs, it might be the backup file is corrupted. You
> may want to try a new backup/restore to test. Also, you may want to test
> directly in QA or mangement studio(2005) to see if there is any difference
> More related informaiton:
>
> Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
> http://support.microsoft.com/kb/304692/
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
> Server
> http://support.microsoft.com/?id=314546
> Please let's know if you have any further questions or concerns. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Rainer,
Please feel free to post back if you have any update. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
thank you very much for your help!
Following your advise I can now (re)generate a database completely from just
a backupfile.
Doing these 4 step give me exactly what I need:
DROP DATABASE OekoTemp
GO
restore headeronly FROM DISK = N'<backupfile>'
GO
RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
GO
RESTORE LOG [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 2, NOUNLO
AD,
STATS = 10
Regards
Rainer Queck

rebuild a database from backupfile only?

Hi NG,
in case this is not the right news group, please advise me to the right one
for my question.
How can I rebuild a database from a plain backup?
Here is my problem:
From within a C# Program I am generating backups of my database by sending
the following sql commands:
BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"
as well as:
BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME
= N'OekoTemp-Transaktionsprotokoll backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
This works fine, and I am able to do a restore from within my C# program by
sending the sql command:
RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
But if the original .mdf and .log files are deleted, I can no longer do a
restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.
When ever and how ever I try to restore my database from the plain backup I
receive error messages like (translated from german):
The backup set contains backups for a other database as the present
'OekoTemp'-Database.
Message 3013, Level 16, Status 1, Line 1
RESTORE DATABASE terminated due to errors.
As said : the OekoTemp-Database is not existing any more at all so how can
it be present like mentioned in the error message?
Thanks for any help and hints!
Rainer QueckRainer Queck wrote:
> Hi NG,
> in case this is not the right news group, please advise me to the right one
> for my question.
> How can I rebuild a database from a plain backup?
> Here is my problem:
> From within a C# Program I am generating backups of my database by sending
> the following sql commands:
> BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
> DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete
> backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10"
> as well as:
> BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME
> = N'OekoTemp-Transaktionsprotokoll backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> This works fine, and I am able to do a restore from within my C# program by
> sending the sql command:
> RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
> But if the original .mdf and .log files are deleted, I can no longer do a
> restore. Not by my program, and not by the SQL SERVER MANAGEMENT STUDIO.
> When ever and how ever I try to restore my database from the plain backup I
> receive error messages like (translated from german):
> The backup set contains backups for a other database as the present
> 'OekoTemp'-Database.
> Message 3013, Level 16, Status 1, Line 1
> RESTORE DATABASE terminated due to errors.
> As said : the OekoTemp-Database is not existing any more at all so how can
> it be present like mentioned in the error message?
> Thanks for any help and hints!
> Rainer Queck
>
>
Hi Rainer,
If a database with the name "OekoTemp" doesn't exists, there shouldn't
be a problem in restoring it with the command you issue. You say that
the original mdf and ldf files doesn't exists, but does that mean that
the files has been deleted manually but the database still exists in SQL
server?
If the database exists and you'd like to restore the backup to this
database, you'll have to use the REPLACE option of the restore command.
I'll suggest that you read up on the RESTORE DATABASE command/syntax in
Books On Line - that will hopefully help you.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in message
news:ePsOMaFbHHA.3616@.TK2MSFTNGP05.phx.gbl...
> Rainer Queck wrote:
>> Hi NG,
>> in case this is not the right news group, please advise me to the right one for my question.
>> How can I rebuild a database from a plain backup?
>> Here is my problem:
>> From within a C# Program I am generating backups of my database by sending the following sql
>> commands:
>> BACKUP DATABASE [OekoTemp] FILEGROUP = N'PRIMARY' TO
>> DISK = N'<backupfile>' WITH NOFORMAT, NOINIT, NAME = N'OekoTemp-Complete backup, SKIP, NOREWIND,
>> NOUNLOAD, STATS = 10"
>> as well as:
>> BACKUP LOG [OekoTemp] TO DISK = N'<backupfile' WITH NORECOVERY, NOINIT, NAME =>> N'OekoTemp-Transaktionsprotokoll backup', SKIP,
>> NOREWIND, NOUNLOAD, STATS = 10
>> This works fine, and I am able to do a restore from within my C# program by sending the sql
>> command:
>> RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1, NORECOVERY, REPLACE,
>> NOUNLOAD, STATS = 10
>> But if the original .mdf and .log files are deleted, I can no longer do a restore. Not by my
>> program, and not by the SQL SERVER MANAGEMENT STUDIO.
>> When ever and how ever I try to restore my database from the plain backup I receive error
>> messages like (translated from german):
>> The backup set contains backups for a other database as the present 'OekoTemp'-Database.
>> Message 3013, Level 16, Status 1, Line 1
>> RESTORE DATABASE terminated due to errors.
>> As said : the OekoTemp-Database is not existing any more at all so how can it be present like
>> mentioned in the error message?
>> Thanks for any help and hints!
>> Rainer Queck
>>
> Hi Rainer,
> If a database with the name "OekoTemp" doesn't exists, there shouldn't be a problem in restoring
> it with the command you issue. You say that the original mdf and ldf files doesn't exists, but
> does that mean that the files has been deleted manually but the database still exists in SQL
> server?
> If the database exists and you'd like to restore the backup to this database, you'll have to use
> the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in Books On Line - that will
> hopefully help you.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||Hello Steen,
thanks for your fast reply!
I didn't expect a answer that fast, since I have the same question placed in
a german ng since 4 days without a answer.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> schrieb im
Newsbeitrag news:ePsOMaFbHHA.3616@.TK2MSFTNGP05.phx.gbl...
> If a database with the name "OekoTemp" doesn't exists, there shouldn't be
> a problem in restoring it with the command you issue. You say that the
> original mdf and ldf files doesn't exists, but does that mean that the
> files has been deleted manually but the database still exists in SQL
> server?
Yes and no. My intention is to backup my database in intervals and then by
program execute a script which drops / creates my database.
This way I get backups for certain periods in time. Then if it is necessary
to load a "old" database I thought I could do a "backup current restore old
database".
Well this did not (yet) work. So I manually deletet my database in SQL
SERVER 2005 and then deleted the database files, hoping to be able to now do
a restore of the old database. This did not work (Yet) and I received the
errormessages mentioned before.
> If the database exists and you'd like to restore the backup to this
> database, you'll have to use the REPLACE option of the restore command.
> I'll suggest that you read up on the RESTORE DATABASE command/syntax in
> Books On Line - that will hopefully help you.
Thanks for the hints. But as you can see by my original post, I am using the
REPLACE option on my restore efforts, but may be there is still a wrong
selection of options in my sql?
Regards
Rainer Queck|||Hi Tibor,
thanks for the additional hint.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23xCFMfFbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> In addition, study RESTORE FILELISTONLY and RESTORE HEADERONLY.
How would the complete SQL string look like for my case?
Since the project I am working on is a weekend project, I won't be able to
continue with my tries before saturday.
Also the current database is not yet important since the project is under
developement and I am currently working with test data and therefore a
dataloss is not yet a pain ;-)
Still it would be great, if I could get this problem solved soon.
Regards
Rainer|||Hello Rainer,
I understand that you cannot restore a database when the existing mdf/ldf
is remmoved and the database does not exist on the server. If I'm off-base,
please let me know.
You may first run fist command:
restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak'
Please see the Databasename in the result, and this shall be the database
name you use in restore command. Please double check the name is correct.
From the error message you encounter, it seems the database name is not
correct in your restore command
In my sample, it is dbacc. If run the command you mentioned, you may see
the following error message:
RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
failed with the operating system error 3(The system cannot find the path
specified.).
Msg 3156, Level 16, State 3, Line 1
File 'dbacc_log' cannot be restored to
'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If so, you need to use "with move" statmend in restore command. For example:
RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
move 'dbacc' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'
If the issue still occurs, it might be the backup file is corrupted. You
may want to try a new backup/restore to test. Also, you may want to test
directly in QA or mangement studio(2005) to see if there is any difference
More related informaiton:
Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
http://support.microsoft.com/kb/304692/
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please let's know if you have any further questions or concerns. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
thank you very much for your detailed answer.
I will follow your suggestions as soon as I continue work on my project
(saturday) and let you know if I was successful or need more help.
Considering the answers I have up to now, there is hope ;-)
Regards
Rainer
"Peter Yang [MSFT]" <petery@.online.microsoft.com> schrieb im Newsbeitrag
news:bYXod$GbHHA.552@.TK2MSFTNGHUB02.phx.gbl...
> Hello Rainer,
> I understand that you cannot restore a database when the existing mdf/ldf
> is remmoved and the database does not exist on the server. If I'm
> off-base,
> please let me know.
> You may first run fist command:
> restore headeronly FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak'
>
> Please see the Databasename in the result, and this shall be the database
> name you use in restore command. Please double check the name is correct.
> From the error message you encounter, it seems the database name is not
> correct in your restore command
>
> In my sample, it is dbacc. If run the command you mentioned, you may see
> the following error message:
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
>
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc.mdf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file
> "E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf"
> failed with the operating system error 3(The system cannot find the path
> specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'dbacc_log' cannot be restored to
> 'E:\sql2005\MSSQL.1\MSSQL\DATA\dbacc_log.ldf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous
> messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> If so, you need to use "with move" statmend in restore command. For
> example:
>
> RESTORE DATABASE [dbacc] FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.bak' WITH FILE = 1,
> NORECOVERY, REPLACE, NOUNLOAD, STATS = 10,
> move 'dbacc' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc.mdf',
> move 'dbacc_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL.3\MSSQL\Backup\dbacc_log.ldf'
> If the issue still occurs, it might be the backup file is corrupted. You
> may want to try a new backup/restore to test. Also, you may want to test
> directly in QA or mangement studio(2005) to see if there is any difference
> More related informaiton:
>
> Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE
> http://support.microsoft.com/kb/304692/
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
> Server
> http://support.microsoft.com/?id=314546
> Please let's know if you have any further questions or concerns. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Rainer,
Please feel free to post back if you have any update. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
thank you very much for your help!
Following your advise I can now (re)generate a database completely from just
a backupfile.
Doing these 4 step give me exactly what I need:
DROP DATABASE OekoTemp
GO
restore headeronly FROM DISK = N'<backupfile>'
GO
RESTORE DATABASE [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 1,
NORECOVERY, REPLACE, NOUNLOAD, STATS = 10
GO
RESTORE LOG [OekoTemp] FROM DISK = N'<backupfile>' WITH FILE = 2, NOUNLOAD,
STATS = 10
Regards
Rainer Queck

Reason for Limitation and Work around for referencing Table Group fields in Footer!

I'm gonna try this again, Microsoft, Please help!

There has to be a solution for this!!!! How can I get around the limitation in SSRS 2005 of being able to SUM on a Table's Group field from my group footer expression!

Keep in mind, I'm talking about a table here, not the global Report Footer, a Table object's footer!

It's driving me nuts

My table footer GrossGoal_gt 's field expression (non of these work and throw the error below):

=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")

neither does this:

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

neither does this:

=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:

Aggregate functions can be used only on report items contained in page headers and footers.

or this:

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

The field it's referencing's (textbox GrossGoal1) value is:

=((Fields!FeeGoal.Value) / Fields!FeeSchedule.Value) * 100

Error:

[rsFieldReference] The Value expression for the textbox ‘GrossGoal_gt’ refers to the field ‘GrossGoal1’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

and

[rsInvalidAggregateScope] The Value expression for the textbox ‘GrossGoal_gt’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

I did that! I set it to my Group name of my table!!!! Am I doing this wrong?

http://www.eggheadcafe.com/forums/ForumPost.asp?ID=59694&INTID=9

I am having to produce a stupid work around which is create a new dataset, tie it to a stored proc which then does some crazy calculations which is a headache in SQL which sums up my column's total using SQL in the stored proc instead of Reporting Services being able to reference my stupid Group field in the footer in order to sum for a grand total in the footer for that group column.

Related threads I have posted:

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/936792

Here's are other people with the same problem but no answers out there from my searches!

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882

http://www.sqlblogs.com/top/ng/group~24/~131100~__Scope-problem-with-SUM-on-group-footer/index.aspx

http://www.sqlblogs.com/top/ng/group~24/~130065~__MVPs-and-MS-support--Where-are-you~~~-on-dificults-questions/index.aspx

http://www.developmentnow.com/g/115_2005_2_0_0_455511/GroupingSum-Question.htm

http://www.sqlblogs.com/top/ng/group~24/~129523~__RunningValue-Error/index.aspx

Why doesn't this work MS ?

Scope

Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope.

To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes.

Why am I putting this in big letters? Because I'm about to pull my hair out!!!!

Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:

Report Functions

Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).

The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:

Copy Code

=Sum(Fields!LineTotal.Value, "Order")

Based on your previous posts, the problem seems to be that each detail row of the table has an amount (not sure which data set field: maybe Fields!FeeGoal.Value?) and a percent (maybe Fields!FeeSchedule.Value?). Then there is a derived amount expression: "=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100" in a text box (GrossGoal1). What if, instead, you add a Calculated Field like GrossGoal to your data set, defined by the same expression:"=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100"? In that case, the detailed row amount can simply be: "=Fields!GrossGoal.Value", and both the group and footer expressions can be: "=Sum(Fields!GrossGoal.Value)".|||

Deepak, I've definitely tried that work around with little success if any. Most of the time, Visual Studio crashes when trying to SUM on a calculated Dataset field that is also very complex in itself!

Also Proj Fee (what I am referring to above for an example of what I'm currently stuck on)is not just a single value, it is a complicated calculation that includes many in it's expression. That's why this report is so crazy and why I really need to be able to bypass all these workarounds to be able to direcly reference fields from the footer and use aggregate functions on those fields.

As my report goes from right to left, the fields are not jus straight values, they have a ton of combined fields and calculatiosn within each field so each field really builds on the other...keep in mind this used to be a complex Excel Spreadsheet that they wanted me to automate!

the fact is, myself and others see this as a huge problem and limitatio that is keeping us from delivering highly complicated reporting via Reporting Services 2005. We do not want to create wacky ways around this....if that's the case, we might as well go back to Crystal at this point ! Yes, SSRS is great but this limitation is HUGE!

Here's Project Fee...yes, it's a nightmare and you'll see now why you can't just assume this work around or that when you get this complicated in your reports; We need to be able to SUM Group Header fields (not detail) in our Footer. I am not using the detail section of my Group, I don't need it.

ProjFee Expression is:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) + (SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

You try putting this stripped down as a new calculated field in your dataset and then try doing a SUM on this in the footer of your table and watch Visual Studio Crash!!! It's loads of fun

|||

Here is my report file....open it, take a look at the last report at the bottom. The ProjFee in Footer..I want this to sum up my ProjFee Group Column:

http://photopizzaz.biz/myreport.zip

I've also tried these combinations using straight formulas (changing the SUM around in the past has worked so far up until now) with no luck...all the totals come out wrong:

http://www.photopizzaz.biz/calcs_tried_in_projfee_footer_expression.doc

What I'm trying to achieve like eveyrone else is to have success putting something like these into my ProjFee Footer textbox expression:

=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:

Error: Aggregate functions can be used only on report items contained in page headers and footers.

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

|||If anyone needs test data to test my report, I'd be happy to provide it so I can figure out this problem, just let me know. It will be non-confidential test data that will work.|||

You may want to try using a custom function and a global variable to store a running total (sort of a SUMIF(value, groupvalue)) that you would create in the code window.

See http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/

Actually all of your complex calculations might be simplified by encapsulating in vb functions.

Here's more from Teo's book.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true

|||

Yes, but Andrew, I need the SUM in my footer to be summing up the group totals in my group field column...an outside function isn't gonna help me here because Commission % has to be used in the group calculation, I can't use Commission % in my GT because it's not possible to sum a bunch of % then do a calcuation like this.

Also, I was told that the group footer takes in the whole dataaset? not just the group totals.

Anyway, I think this is crazy first of all that I haven't had an MS staff reply to this problem, and second the documentation says we should be able to reference a groupname as the scope in a field expression from a table footer yet their errors tell a different story. Furthermore, there are at least from my searches, no examples of referencing a groupname or other names other than a dataset in BOL for doing what I want but the error says you should be able to. Where's the documentation on this scope? I only saw a brief comment and the most simplest example in BOL which isn't really helping anyone.

Can someone from MS please comment who is in the SSRS 2005 team!! A lot of use are wondering what the deal is here and have for quite some time.

|||

Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:

Report Functions

Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).

The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:

Copy Code

=Sum(Fields!LineTotal.Value, "Order") 

|||

You should check that whether your sql or view have the column named "GrossGoal1".

|||

Sorry for being rude but I just said 1000 times, it's not a field from my dataset! It's a named textbox in my Report table's group. If you right-click a field in your group in your report, you have to name it something...i.e. textbox50 to something lik GROSSGOL

I want to do a SUM on that column textbox that resides in my table's group...not the Dataset!

Everyone keeps thinking I'm talking about a Dataset field, no, it's a Table Group Field!!

Create a table in SSRS 2005 OK. Then, create a group in that table. Ok, then add some fields to that group Header in your table. Rename one of them to GrossGoal by going to the properties of one of your table's textboxes (which contain expressions like referencing dataset fields!). Ok, then in your Table footer, try to do a SUM(name of the textbox in your group that you just renamed)

that's what I'm trying to do! Please Download and Look at my freakin .rdl that I provided, don't just guess what I'm trying to do here, I've provided probably 10x the required information! If you look at my .rdl, right click on the fields and see their properties, then look at the expression for the footer field! It's trying to reference the field above it which is a group field!

and one more time, because of the fact that I'm dealing with a commission %, simply pasting the same expression from my group to my footer will not work!! trust me...this is a very complex calculation that willl not come out right going that route like all the books tell you to do!

|||

Looks like there is a confusion on the scope argument in aggregate functions. What the scope argument tells RS is to summarize all detail data in the specified scope, not summarizing all the aggregated values on these scopes.

For example, if you have a table (with table footer) and a table group. (looks like the site that host your report is currently down, so I can't look at your particular case). The hiearchy of the report looks like:

Report/DataSet

Table (table header/footer rows)

TableGroup (tablegroup header/footer rows)

In the TableGroup (group header/footer), you can define an aggregate with scope 1) none (the default is the current scope - TableGroup) or 2) TableGroup or 3) Table or 4) Nothing (the outermost scope, meaning the entire dataset, essentially the same as the table scope, in this case). If you are in the table footer, you can define an aggregate with scope 1) none (equivalent to table) or 2) Table or 3) Nothing. As you can see here, you can reach out to outer/containing scopes from the current location, but not the opposite way. This error message "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." basically means this.

From what you described in the posts, looks like you are trying to sum a set of calculated values (involve aggregates) from the group footers in your table footer. "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release.

For now, there is a workaround for this that involves using custom code (similar to what Andrew has suggested) and can be quite tricky to get it to work. The idea is that you would pass a detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group), return the variable value which is the grand total you are looking for. Without looking at your particular scenario, I'm not entirely sure if this approach would work for you. But hopefully it can give you some pointer to start with if you want to try it.

Below is an old sample I provided to one of our customers to workaound a similar problem. Hope it's of some help to you.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report1</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
<CanGrow>true</CanGrow>
<Value>=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Freight">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Freight</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>OrderID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Top>0.33in</Top>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.MyFunc(Nothing, Fields!Freight.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.21in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5b047e43-435e-4e68-bf7b-70eaf0f45c53</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal freight As Object) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function
</Code>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Expr1">
<DataField>Expr1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.OrderID, Orders.Freight, [Order Details].OrderID AS Expr1, [Order Details].UnitPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderID IN (10248, 10250))</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1e9e64e8-3f34-4760-ae9a-0789ad4c44c7</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

|||

Fang Wang, this is a huge limitation, any idea when this will be supported? If it was, I would have been able to save literally a month worth of wasted time.

Basically I ended up having to do this sum in SQL which was painful because it included about 7 complicated SUMS before it which were wrapped up into this final Column SUm and I do not wish to have to code SQL like that again. On the other hand custom code as you stated is not easy and definitely something I would not wish on anyone just to sum up a Column in this situation.

thanks for th workaround and the FYI on the non-supported note though! Much appreciated. Lets hope for all of us (many who have posted this problem in other forums) that MS comes up wtih a SP which will fix this and provide this very powerful option in the future.

|||

and another complaint, it is endless, this time with a matrix!

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882

|||

First of all enough with the caps / bold and large text. We understand your problem, and quite frankly the world is not going to end.

Secondly, the solution (workaround) was provided and DOES work.

Please do some reading on how to use and control flow using custom code in rs.

Jon

|||

Not sure if you have gone through the entire solution above, however this is another possible solution if that didn't work (from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.
What if you don't want to or can't?
Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.
Steps to lookup values from a reference table in SQL:
Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)
Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")
In the Code Properties window, try the following:
public dict as new System.Collections.Generics.Dictionary(Of System, System)
function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function
function getValue(value as object) as object
return dict(value)
end function
Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.
If you have properly bound a table to the first list control, you should be able to lookup results in the second table.
This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.
I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

Oh, and Flavaflav, I really hope you don't wear a clock around your neck & have gold teeth.

cheers,

Andrew

Reason for Limitation and Work around for referencing Table Group fields in Footer!

I'm gonna try this again, Microsoft, Please help!

There has to be a solution for this!!!! How can I get around the limitation in SSRS 2005 of being able to SUM on a Table's Group field from my group footer expression!

Keep in mind, I'm talking about a table here, not the global Report Footer, a Table object's footer!

It's driving me nuts

My table footer GrossGoal_gt 's field expression (non of these work and throw the error below):

=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")

neither does this:

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

neither does this:

=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:

Aggregate functions can be used only on report items contained in page headers and footers.

or this:

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

The field it's referencing's (textbox GrossGoal1) value is:

=((Fields!FeeGoal.Value) / Fields!FeeSchedule.Value) * 100

Error:

[rsFieldReference] The Value expression for the textbox ‘GrossGoal_gt’ refers to the field ‘GrossGoal1’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

and

[rsInvalidAggregateScope] The Value expression for the textbox ‘GrossGoal_gt’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

I did that! I set it to my Group name of my table!!!! Am I doing this wrong?

http://www.eggheadcafe.com/forums/ForumPost.asp?ID=59694&INTID=9

I am having to produce a stupid work around which is create a new dataset, tie it to a stored proc which then does some crazy calculations which is a headache in SQL which sums up my column's total using SQL in the stored proc instead of Reporting Services being able to reference my stupid Group field in the footer in order to sum for a grand total in the footer for that group column.

Related threads I have posted:

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/936792

Here's are other people with the same problem but no answers out there from my searches!

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882

http://www.sqlblogs.com/top/ng/group~24/~131100~__Scope-problem-with-SUM-on-group-footer/index.aspx

http://www.sqlblogs.com/top/ng/group~24/~130065~__MVPs-and-MS-support--Where-are-you~~~-on-dificults-questions/index.aspx

http://www.developmentnow.com/g/115_2005_2_0_0_455511/GroupingSum-Question.htm

http://www.sqlblogs.com/top/ng/group~24/~129523~__RunningValue-Error/index.aspx

Why doesn't this work MS ?

Scope

Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope.

To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes.

Why am I putting this in big letters? Because I'm about to pull my hair out!!!!

Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:

Report Functions

Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).

The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:

Copy Code

=Sum(Fields!LineTotal.Value, "Order")

Based on your previous posts, the problem seems to be that each detail row of the table has an amount (not sure which data set field: maybe Fields!FeeGoal.Value?) and a percent (maybe Fields!FeeSchedule.Value?). Then there is a derived amount expression: "=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100" in a text box (GrossGoal1). What if, instead, you add a Calculated Field like GrossGoal to your data set, defined by the same expression:"=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100"? In that case, the detailed row amount can simply be: "=Fields!GrossGoal.Value", and both the group and footer expressions can be: "=Sum(Fields!GrossGoal.Value)".|||

Deepak, I've definitely tried that work around with little success if any. Most of the time, Visual Studio crashes when trying to SUM on a calculated Dataset field that is also very complex in itself!

Also Proj Fee (what I am referring to above for an example of what I'm currently stuck on)is not just a single value, it is a complicated calculation that includes many in it's expression. That's why this report is so crazy and why I really need to be able to bypass all these workarounds to be able to direcly reference fields from the footer and use aggregate functions on those fields.

As my report goes from right to left, the fields are not jus straight values, they have a ton of combined fields and calculatiosn within each field so each field really builds on the other...keep in mind this used to be a complex Excel Spreadsheet that they wanted me to automate!

the fact is, myself and others see this as a huge problem and limitatio that is keeping us from delivering highly complicated reporting via Reporting Services 2005. We do not want to create wacky ways around this....if that's the case, we might as well go back to Crystal at this point ! Yes, SSRS is great but this limitation is HUGE!

Here's Project Fee...yes, it's a nightmare and you'll see now why you can't just assume this work around or that when you get this complicated in your reports; We need to be able to SUM Group Header fields (not detail) in our Footer. I am not using the detail section of my Group, I don't need it.

ProjFee Expression is:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) + (SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

You try putting this stripped down as a new calculated field in your dataset and then try doing a SUM on this in the footer of your table and watch Visual Studio Crash!!! It's loads of fun

|||

Here is my report file....open it, take a look at the last report at the bottom. The ProjFee in Footer..I want this to sum up my ProjFee Group Column:

http://photopizzaz.biz/myreport.zip

I've also tried these combinations using straight formulas (changing the SUM around in the past has worked so far up until now) with no luck...all the totals come out wrong:

http://www.photopizzaz.biz/calcs_tried_in_projfee_footer_expression.doc

What I'm trying to achieve like eveyrone else is to have success putting something like these into my ProjFee Footer textbox expression:

=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:

Error: Aggregate functions can be used only on report items contained in page headers and footers.

=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")

Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

|||If anyone needs test data to test my report, I'd be happy to provide it so I can figure out this problem, just let me know. It will be non-confidential test data that will work.|||

You may want to try using a custom function and a global variable to store a running total (sort of a SUMIF(value, groupvalue)) that you would create in the code window.

See http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/

Actually all of your complex calculations might be simplified by encapsulating in vb functions.

Here's more from Teo's book.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true

|||

Yes, but Andrew, I need the SUM in my footer to be summing up the group totals in my group field column...an outside function isn't gonna help me here because Commission % has to be used in the group calculation, I can't use Commission % in my GT because it's not possible to sum a bunch of % then do a calcuation like this.

Also, I was told that the group footer takes in the whole dataaset? not just the group totals.

Anyway, I think this is crazy first of all that I haven't had an MS staff reply to this problem, and second the documentation says we should be able to reference a groupname as the scope in a field expression from a table footer yet their errors tell a different story. Furthermore, there are at least from my searches, no examples of referencing a groupname or other names other than a dataset in BOL for doing what I want but the error says you should be able to. Where's the documentation on this scope? I only saw a brief comment and the most simplest example in BOL which isn't really helping anyone.

Can someone from MS please comment who is in the SSRS 2005 team!! A lot of use are wondering what the deal is here and have for quite some time.

|||

Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:

Report Functions

Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).

The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:

Copy Code

=Sum(Fields!LineTotal.Value, "Order") 

|||

You should check that whether your sql or view have the column named "GrossGoal1".

|||

Sorry for being rude but I just said 1000 times, it's not a field from my dataset! It's a named textbox in my Report table's group. If you right-click a field in your group in your report, you have to name it something...i.e. textbox50 to something lik GROSSGOL

I want to do a SUM on that column textbox that resides in my table's group...not the Dataset!

Everyone keeps thinking I'm talking about a Dataset field, no, it's a Table Group Field!!

Create a table in SSRS 2005 OK. Then, create a group in that table. Ok, then add some fields to that group Header in your table. Rename one of them to GrossGoal by going to the properties of one of your table's textboxes (which contain expressions like referencing dataset fields!). Ok, then in your Table footer, try to do a SUM(name of the textbox in your group that you just renamed)

that's what I'm trying to do! Please Download and Look at my freakin .rdl that I provided, don't just guess what I'm trying to do here, I've provided probably 10x the required information! If you look at my .rdl, right click on the fields and see their properties, then look at the expression for the footer field! It's trying to reference the field above it which is a group field!

and one more time, because of the fact that I'm dealing with a commission %, simply pasting the same expression from my group to my footer will not work!! trust me...this is a very complex calculation that willl not come out right going that route like all the books tell you to do!

|||

Looks like there is a confusion on the scope argument in aggregate functions. What the scope argument tells RS is to summarize all detail data in the specified scope, not summarizing all the aggregated values on these scopes.

For example, if you have a table (with table footer) and a table group. (looks like the site that host your report is currently down, so I can't look at your particular case). The hiearchy of the report looks like:

Report/DataSet

Table (table header/footer rows)

TableGroup (tablegroup header/footer rows)

In the TableGroup (group header/footer), you can define an aggregate with scope 1) none (the default is the current scope - TableGroup) or 2) TableGroup or 3) Table or 4) Nothing (the outermost scope, meaning the entire dataset, essentially the same as the table scope, in this case). If you are in the table footer, you can define an aggregate with scope 1) none (equivalent to table) or 2) Table or 3) Nothing. As you can see here, you can reach out to outer/containing scopes from the current location, but not the opposite way. This error message "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." basically means this.

From what you described in the posts, looks like you are trying to sum a set of calculated values (involve aggregates) from the group footers in your table footer. "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release.

For now, there is a workaround for this that involves using custom code (similar to what Andrew has suggested) and can be quite tricky to get it to work. The idea is that you would pass a detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group), return the variable value which is the grand total you are looking for. Without looking at your particular scenario, I'm not entirely sure if this approach would work for you. But hopefully it can give you some pointer to start with if you want to try it.

Below is an old sample I provided to one of our customers to workaound a similar problem. Hope it's of some help to you.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report1</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
<CanGrow>true</CanGrow>
<Value>=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Freight">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Freight</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>OrderID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Top>0.33in</Top>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.MyFunc(Nothing, Fields!Freight.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.21in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5b047e43-435e-4e68-bf7b-70eaf0f45c53</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal freight As Object) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function
</Code>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Expr1">
<DataField>Expr1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.OrderID, Orders.Freight, [Order Details].OrderID AS Expr1, [Order Details].UnitPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderID IN (10248, 10250))</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1e9e64e8-3f34-4760-ae9a-0789ad4c44c7</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

|||

Fang Wang, this is a huge limitation, any idea when this will be supported? If it was, I would have been able to save literally a month worth of wasted time.

Basically I ended up having to do this sum in SQL which was painful because it included about 7 complicated SUMS before it which were wrapped up into this final Column SUm and I do not wish to have to code SQL like that again. On the other hand custom code as you stated is not easy and definitely something I would not wish on anyone just to sum up a Column in this situation.

thanks for th workaround and the FYI on the non-supported note though! Much appreciated. Lets hope for all of us (many who have posted this problem in other forums) that MS comes up wtih a SP which will fix this and provide this very powerful option in the future.

|||

and another complaint, it is endless, this time with a matrix!

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882

|||

First of all enough with the caps / bold and large text. We understand your problem, and quite frankly the world is not going to end.

Secondly, the solution (workaround) was provided and DOES work.

Please do some reading on how to use and control flow using custom code in rs.

Jon

|||

Not sure if you have gone through the entire solution above, however this is another possible solution if that didn't work (from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.
What if you don't want to or can't?
Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.
Steps to lookup values from a reference table in SQL:
Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)
Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")
In the Code Properties window, try the following:
public dict as new System.Collections.Generics.Dictionary(Of System, System)
function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function
function getValue(value as object) as object
return dict(value)
end function
Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.
If you have properly bound a table to the first list control, you should be able to lookup results in the second table.
This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.
I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

Oh, and Flavaflav, I really hope you don't wear a clock around your neck & have gold teeth.

cheers,

Andrew