multiple datasets

I have a report that I have created with multiple subreports and datasets. There should be a better solution so I am asking the question here.

The report should display like this:

Object Object Title Budget Curr-Spent Ytd-Spent Ytd-Encum Post-Ytd Balance % Remaining 4100 EMPLOYEES $380.00 $250.93 $343.67 $0.00 $0.00 $61.33 15.94% 4102 EMPLOYEES - TEMPORARY $149.00 $110.75 $139.70 $0.00 $0.00 $47.30 30.16% 4201 EMPLOYEE BENEFITS $138.00 $73.16 $12.60 $0.00 $0.00 $32.40 25.28%

Each column represents another dataset.

Each column right now is a subreport so I can use the object as a parameter to the subreports dataset. Each column needs to have the object as a parameter.

Is there a way I can pass a field from the first dataset as a parameter for the next dataset without using subreports?

Why can't you create a SQL query which puts every thing together in one dataset and use that dataset in a table. Looks like you are using related information anyways so why not do everything in SQL and then use table for it.

Thanks,

-Rohit

|||

I will need to run each query with the object as a parameter. I guess I can do this with a table variable or a cursor in the sql but there has got to be a better way.

Here is the main dataset

SELECT DISTINCT sum_object AS object, object_desc

FROMfin_detail WITH (NOLOCK)

JOIN bud_objectWITH (NOLOCK)

ON FIN_DETAIL.SUM_OBJECT = bud_object.OBJECT_CODE

WHERE pca = @.pca

AND budget_year = @.budgetyear

AND sum_object <> '4100'

UNION

SELECT DISTINCT sub_obj AS object, object_desc

FROM fin_detailWITH (NOLOCK)

JOIN bud_objectWITH (NOLOCK)

ON FIN_DETAIL.SUB_OBJ = bud_object.OBJECT_CODE

WHERE pca = @.pca

AND budget_year = @.budgetyear

AND exp_sum_object= ‘4100’

Here is the dataset I need to call for each main dataset

IF substring(@.object,1,2)='41'

BEGIN

--starts with 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = '4100'

And sub_obj = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

ELSE

BEGIN

--not 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

Basically, I need to get all the sum_object in the first query if the sum_object <> 4100 and the sub_object if the sum_object = 4100.

Then I need to call the second dataset to sum the amounts with the @.object from the first dataset as the parameter.

The only way I figured out how to do this was so use subreports instead of a loop in sql.

BTW, this second dataset is one of 5 that I need to call for each object in the first query.

|||

I figured out how to do what I needed from reading this article http://www.code-magazine.com/articleprint.aspx?quickid=0701061&page=2

I created this function

Function GetBudget(ByVal myobject As String, ByVal pca As String, ByVal budgetyear As String) As Integer
Dim oConn As New System.Data.SqlClient.SqlConnection
oConn.ConnectionString =

oConn.Open()
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn

oCmd.CommandText =

IF substring(@.object,1,2)='41'

BEGIN

--starts with 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = '4100'

And sub_obj = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

ELSE

BEGIN

--not 41

Select isnull(sum(amount),0) * -1 as budget

From fin_detail WITH (NOLOCK)

Where pca = @.pca

And budget_year = @.budgetyear

And sum_object = @.object

And gl_Account = '5071'

And budget_year=trans_year

END

oCmd.Parameters.AddWithValue("@.sumobject", myobject)
oCmd.Parameters.AddWithValue("@.pca", pca)
oCmd.Parameters.AddWithValue("@.budgetyear", budgetyear)

Dim Budget As Integer = oCmd.ExecuteScalar()
oConn.Close()
Return Budget
End Function

I then called called the code in my textbox like this

=Code.GetBudget(Fields!object.Value,Parameters!pca.Value,Parameters!BudgetYear.Value).

For every row this function is called and returns my budget value because the Fields collection contains the object record from my main report dataset.

I did have problems placing the query on multiple lines even with the normal VB string concatination techniques so I put it all on one line in the XML.

This is the first time that I saw an example of querying a database from custom code in SSRS.