SQL Reporting Services and combining 2 datasets into a table

Thanks for replying Rick. Here's the details you requested with a few more details.
I am using Visual Studio to create the report.

Here is the first query:
SELECT     [Job No_], [Global Dimension 2 Code], [Posting Date], [Document No_], Description, [Total Cost], [Total Price], LEFT([Global Dimension 2 Code], 1)
                      AS Expr1, [Source Name]
FROM         [Company$Job Ledger Entry]
WHERE     ([Job No_] = '0218') AND ([Posting Date] BETWEEN '20080601' AND '20080630')
ORDER BY [Global Dimension 2 Code]

(For testing and getting this up and running I removed the user inputs and used the examples shown in the WHERE statement, in this case Job 0218 for June 2008).

[Global Dimension 2 Code] is the cost code and is of the format 101, 102, 103, 201, 202, 301, 302 etc.

In the Layout section I added a table and inserted two Groups. The first was grouped by Expr1 to group 100, 200, 300 etc. The second was by [Global Dimension 2 Code]. This way I can total each cost code and each section. The columns are Posting Date, Source, Document No, Description and Cost (which is Total Cost + Total Price). All fine.

As mentioned before I also want to add next to each cost code total the sum of the costs BEFORE the entered dates. With our example, all costs before June. So you can see for any given code the costs before June, the June costs then a final column in which they are added together.

To get the data I created a second query called RunningCost:
SELECT     [Global Dimension 2 Code] AS RCGD2Code, [Total Cost] AS RCTotalCost, [Total Price] AS RCTotalPrice, LEFT([Global Dimension 2 Code], 1)
                      AS RCExpr1
FROM         [Company$Job Ledger Entry]
WHERE     ([Job No_] = '0218') AND ([Posting Date] BETWEEN '20010101' AND '20080531')
ORDER BY RCGD2Code

I now need to add the previous cost (RCTotalCost + RCTotalPrice) for each cost code into the table. My problem is I cannot get it to recognise the group structure in the table. With the output being the TOTAL total cost for everything not just the cost code total cost or section total.

Recap of the table structure:
First grouped into cost code sections, 100, 200, 300 etc
Then grouped in to each cost code 101, 102, 201, 202 etc
Columns for the cost code ectries are:
Posting Date, Source, Document No, Description, Cost (Total Cost + Total Price)
A cost code total at the end of each code and another at the end of each section
(All this works fine)
Now need to enter previous cost code totals next to the current totals
Further column to add these together.

Cheers for your time and help,
John

Oh, I have to say that you confused me completely with your detailed explanation. However, I can tell you the following on the subject:

1. You CANNOT use two datasets on the same dataobject (e.g. table, list, or matrix).

2. You can use one of the work arounds, such as:

a. Create a subreport and place it in the cell of your report - than you can use different datasets and you can pass a parameter from your main report to you subreport to synchronize them.

b. Create several tables and place 'em one right after another - it will create illusion of one table (e.g. table to list details from ds1, table to list totals from ds2, table to list totals from ds1)

c. Figure out how to do it with groups and one dataset.

It sounds like you can do it just using the latter way (c), but as I mentioned earlier, I'm a bit confused with what you exactly want.

You can try to abstract and simplify your query for the sake of experiment and then I can probably suggest something. Leave only necessary fields and give us example of your result set. Then show us a sample result set of what you're trying to achieve in your report.

Cheers,
Yurich

Many thanks for the reply Yurich. Sorry for the confusion. I was worried that no one would understand my rambling explainations. I'll try and show an abstract example.

Here's the data:
JobLedger
JobNo   CostCode   Date           Cost
218       101            20080514   200
218       101            20080605   100
218       103            20080524   300
218       102            20080413   200
218       101            20080621   300
218       103            20080616   200
218       103            20080401   100
218       102            20080625   400
218       101            20080322   200
218       102            20080607   100

I use the query:
SELECT     JobNo, CostCode, Date, Cost
FROM         [Company$JobLedger]
WHERE     (JobNo = '0218') AND (Date BETWEEN '20080601' AND '20080630')
ORDER BY CostCode

(With the final version JobNo and Date will be entered via parameters)

In the Layout section of Visual Studio I add a table and insert a group based on the CostCode. This shows the CostCode items grouped together and allows me to total each group.

Example report output:
Job               Code             Date              Cost
218               101               05/06/2008    100
218               101               21/06/2008    300
                                                    Total -  400                    

218               102               07/06/2008   100
218               102               25/06/2008   400
                                                   Total -  500

218               103               16/06/2008   200
                                                   Total -  200

This I can do. All looks fine.

What the FD also wants is the cost total of the data before the given dates next to each section total and then add to the current total to give a new total.

He wants it to look like this:
Job               Code             Date              Cost  Previous Total    New Total
218               101               05/06/2008    100
218               101               21/06/2008    300
                                                    Total -  400           400                   800              

218               102               07/06/2008   100
218               102               25/06/2008   400
                                                   Total -  500           200                   700

218               103               16/06/2008   200
                                                   Total -  200           400                  600

I can extract the data (sort of, not sure of how to do BEFORE a data yet, but not to worry, it can wait for now) using:
SELECT     CostCode AS RCCode, Cost AS RCCost,
FROM         [Company$JobLedger]
WHERE     (JobNo = '0218') AND (Date BETWEEN '20010101' AND '20080531')
ORDER BY RCCode

I add SUM of RCCost in to the group structure of the table, but it ignores the grouping for the CostCode and gives the complete total eg 1000 after each section.

I have also tried taking out all the data with one query for all dates and then using group FILTER to only show the required dates that way. But was running into a lot of problems, tho maybe that was to do with UK and US date formats and may be a better way to resolve this. Not sure. I am open to suggestions!

I hope that has made things a little clearer. Any help with this is greatly appreciated.

Thanks for your time looking at this.

Cheers,
John

Brilliant! Thanks Yurich. I have adapted it for my original code and it does exactly what I wanted.

Thanks again for all your time and patience,
John

I'm glad it worked for you :)
You're welcome

Hi
I am just another programmer looking for a solution to my exact same problem. I used Yurich's code and it worked like a charm.

Thanks Yurich!!!