Help with complex summary of data please

Hi, I'm fairly new to RS and am looking for pointers to help me with
the following situation.
I have a system that tracks the activity of our warehouse and drivers.
Basically, it logs when an order is entered and when it leaves the
warehouse. There are various types of orders and seven warehouses. I
have a view which gives me all the detail I need including the
TimeToSend of each order (the difference between order time and send
time) all in one record per order. I have to create a report that
summarises all of this data on a daily basis by providing the
following:
At the entire company level and then broken down by each warehouse for
each day:
- #orders of TypeA where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeA where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeA where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeA orders
- #orders of TypeB where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeB where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeB where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeB orders
I have achieved the above using two datasets and two tables in my
report aligned next to each other and sorted so the warehouses appear
next to each other.
I have been asked to add additional summary data for each warehouse.
Such as:
- Count of orders of TypeC
- Best AVG TimeToSend of all days (i.e. best day ever)
Now, I can get these new data items from more datasets and add more
tables to the report but am I going about this the right way. Ideally,
having more than one dataset per table would be great. Or if I could
link the tables somehow.
Should I look at trying to return all the data in one dataset? Is that
even possible? My sql is fairly good but I don't know how to combine
the disparate data together.
Any suggestions would be appreciated.Hi,
I am not sure whether I have understood you fully, however, I think you may
integrate the data in another new Table first or a temp table?
If you want to use multiple datasets, it is not a easy job as Data Regions
(Table, Matrix, Chart, and List) are bound to a single data set. You can
refer to other data sets but the reference must be wrapped in an aggregate
such as First(Fields!FieldName.Value, "DataSetX").
Your options are to write a single query, using SQL Server's OpenRowset
functionality, that joins the multiple data sources into a single data set.
The other choice is to use multiple side by side tables though this might
introduce formatting issues.
Sincerely yours,
Michael Cheng
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.

Help Required on Stored Procedure

Hello All,

I'm trying to write a stored procedure which tracks the insert event
in a Table A. When we insert something in Table A in Database 'FMDB',
it inserts the same information in Table B in Database 'Wartung'.

When I insert something with insert query visa SQL Query analyzer, it
works fine.

If i insert records via my C++ programme, it gives error that
ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.

Trigger code is as follow:

CREATE TRIGGER [TG_TASKCOPY] ON [FMDB].[METABO_TASK]
FOR INSERT

AS

BEGIN TRANSACTION

IF (SELECT COUNT(*) FROM inserted)=1
BEGIN

DECLARE @.OBJEKT VARCHAR(32)
DECLARE @.TASK VARCHAR(32)
DECLARE @.PLANNER VARCHAR(32)
DECLARE @.RESPONSIBLE VARCHAR(32)
DECLARE @.STATUS VARCHAR(32)
DECLARE @.PLANDATE DATETIME
DECLARE @.ANLAGE_NUM int

SELECT @.OBJEKT=OBJECT FROM inserted
SELECT @.TASK=TASK FROM inserted
SELECT @.PLANDATE=PLANDATE FROM inserted

IF (SELECT COUNT(*) FROM WARTUNG.WARTUNG.IPS_ASSETS WHERE
FM_NUM=@.OBJEKT)=1
BEGIN
BEGIN TRANSACTION

SELECT @.ANLAGE_NUM=ANLAGE_NUM FROM WARTUNG.WARTUNG.IPS_ASSETS
WHERE FM_NUM=@.OBJEKT
INSERT INTO WARTUNG.WARTUNG.IPS_WARTUNG (ANLAGE,ANLAGE_NUM,
WAEHRUNG,NAECHSTEDATUM, STATUS,INTERVALTYPE,ART) VALUES(@.OBJEKT,
@.ANLAGE_NUM,'EUR', @.PLANDATE,'geplant','D',@.TASK)

COMMIT TRANSACTION
END

ELSE IF(@.@.error<>0)
BEGIN
ROLLBACK TRANSACTION
END

END

With Regards,
Attiq ur Rehman[posted and mailed, please reply in news]

Attiq ur Rehman (attiq76@.yahoo.com) writes:
> I'm trying to write a stored procedure which tracks the insert event
> in a Table A. When we insert something in Table A in Database 'FMDB',
> it inserts the same information in Table B in Database 'Wartung'.
> When I insert something with insert query visa SQL Query analyzer, it
> works fine.
> If i insert records via my C++ programme, it gives error that
> ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.

It could have help if you had included the relevant parts of your
C++ code too.

Anyway, there are some problems with your trigger. You have an initial
BEGIN TRANSACTION which is not matched by a COMMIT or ROLLBACK. There
is not really any reason to issue any BEGIN TRANSACTION in a trigger,
because a trigger always runs in a transaction; defined by the statement
that fired the trigger. (Even if you don't have any user-defined
transaction, each INSERT, UPDATE or DELETE statement is its own transaction
in SQL Server).

There is also this funny line:

> ELSE IF(@.@.error<>0)
> BEGIN
> ROLLBACK TRANSACTION
> END

This is a meaningless piece of code. @.@.error is set after each
statement, and the previous statement is an IF statement, and yes,
an IF statement also sets @.@.error.

I suggest that you remove all BEGIN/COMMIT/ROLLBACK TRANSACTION
from the trigger.

Note also that when you rollback a transaction in a trigger this
cancels the execution of the entire batch. Likewise, if you get
an error in a trigger this also cancels the batch entire, and also
rolls back any transactions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp