Advertisement

03.28.2008 at 05:32AM PDT, ID: 23276928
[x]
Attachment Details

SSRS Report with 2 Datasets

Asked by nutnut in SQL Server 2005, MS SQL Server, SQL Server 2008

Tags:

Hi,
I need to create a SSRS report that shows data from 2 different Stored Procedures that expect the same two parameters.  How do I do this please?  One Stored Procedure will return 3 or 4 lines (Summary Data) whilst the second will return 100's of lines ( the detail)

2 Tables are as below.  Report will look like this below:
Unfortunarely my data has to come from 2 stored procedures and 2 datasets I cannot merge them for lots of other reasons.

Report Start ---------------------------------
(From Stored Procedure 1)
Type   Amount   Notes
Cash   £100       Blah Blah Blah
Card    £50         La La La La

(From Stored Procedure 2)
Type Amount ItemNo
Cash 10         1
Cash 50         2
Cash 40         3
Card 25         1
Card 25         2
Report End------------------------------------


DROP TABLE [dbo].[TableA]
CREATE TABLE [dbo].[TableA](
      [Type] [varchar](50) NOT NULL,
      [TotalAmount] [decimal](18, 2) NOT NULL
) ON [PRIMARY]

GO
INSERT INTO [dbo].[TableA]
           ([Type] ,[TotalAmount], [Notes])
     SELECT 'Cash' ,100, 'Blah Blah Blah'
       UNION SELECT  'Card', 50, 'la la la'
 
DROP TABLE [dbo].[TableB]
CREATE TABLE [dbo].[TableB](
      [Type] [varchar](50) NOT NULL,
      [Amount] [decimal](18, 2) NOT NULL,
      [ItemNo] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[TableB]
           ([Type]
           ,[Amount]
           ,[ItemNo])
       SELECT 'Cash', 10, 1
 UNION SELECT 'Cash', 50, 2
 UNION SELECT 'Cash', 40, 3
 UNION SELECT 'Card', 25, 1
 UNION SELECT 'Card', 25, 2
GO


Thanks
nutnutStart Free Trial
[+][-]03.28.2008 at 07:00AM PDT, ID: 21230426

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]03.28.2008 at 01:58PM PDT, ID: 21234106

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]03.31.2008 at 06:22AM PDT, ID: 21245037

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Server 2005, MS SQL Server, SQL Server 2008
Tags: SSRS
Sign Up Now!
Solution Provided By: AntonyDN
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628