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.
javawaba wrote:
