Hello to All,

I've created a Sub Report to call it from a main Report in SSRS/BIDS:
Here the steps I've did:

1. I've created a data source LN_SQL as embedded Connection of type: Microsoft SQL Server with the Connection string: Data Source=Servername

2. Then I've inserted a tablix and during creation I was asked for the data source and query. As data source I've inserted the upper mention data source. As Query of type text I've entered the following code:

Select t_qono, t_pono, t_srnb, t_ofbp, t_item, t_nids
from dbo.ttdsls101400
where (t_qono = '120000006')
order by t_pono.


But in the code I like to replace the following Points:
1.) dbo.ttdsls101400: here I will replace the final '400' through the @Company Parameter from the main Report.

2.) '120000006' is the Quotation ID from the main Report where it is defined as: First(Fields!tdsls100_qono.Value, "DataSet1"). How I can replace this explicit Number through a placeholder?

Best Regards
Bernd

Hello to All,

I found the solution by my own. Here the steps I've did:

1.) In the query-Textbox of the dataset properties I've entered my selection as mentioned before:
Select t_qono, t_pono, t_srnb, t_ofbp, t_item, t_nids
from dbo.ttdsls101400
where (t_qono = '120000006')
order by t_pono.
After entering the code I've pressed the "Refresh Fields" button.
This step is necessary to get the Fields (= columns in the select Statement) for the DataSet in the Report Data).

2.) I've changed the select Statement into a text string where I can attach typical Parameters and functions from BIDS as strings with the catenate sign (&):

="Select t_qono, t_pono, t_srnb, t_ofbp, t_item, t_nids from dbo.ttdsls101" &
Parameters!p_company.Value &
" where (t_qono = " &
Parameters!p_qono.Value &
") order by t_pono"


Remark: When the query is written as string the columns in the select-statement will only fill the before created fields in the DataSet. But adding new columns in the String-Statement has no effect on the dataset. Therefore you have to write the select-statement again in the plain form as described in Point 1.) with the new columns you want to add.

Hope the Explanation was OK and understandable.

Best Regards
Bernd

Great post, Bernd!

One small addition... If your parameter is alfanumeric (for example quotation ABC000006), it would not work. So better is to add the quotes:

="Select t_qono, t_pono, t_srnb, t_ofbp, t_item, t_nids from dbo.ttdsls101" &
Parameters!p_company.Value &
" where (t_qono = '" & Parameters!p_qono.Value & "') order by t_pono"

Hi Jaap,

thanks a lot for the hint. I'll integrate it. One never know if the user would change from numeric to alphanumeric somewhen / sometime.

Best Regards
Bernd