locked
data source for List control from report prameters RRS feed

  • Question

  • I need to allow report user to type a comma separated text of Employee’s in a text box, and then report will split this text and generate a sub report for each ID

    To do this I have to place the sub report into a list, my problem is

    1.       List control should work by a data source, while I need the list to work by report parameter split values.

     


    khaled Mohamed
    Wednesday, November 2, 2011 12:41 PM

Answers

  • Thanks you very much for you effort When I try your report by parameter “1,2,3 “ , The sub report display ONE table with Three rows , While My Issue is to Display Three Tables each one with ONE row. ( Repeat whole Sub report for each value in the comma separated text By the way I have found a work around this Using List control as I guest from the beginning. Let the user Enter IDs as Comma separated text and make the list control Query the data to get the ID from table where column_ID in ( Comma separated parameter ) . This way allow me to convert from text parameter to data set, Placing the sub report in the list control will make it repeated for each value in the data set
    khaled Mohamed
    • Marked as answer by Khaled_tec Friday, November 4, 2011 7:34 PM
    Friday, November 4, 2011 7:33 PM

All replies

  • Use the multiple value parameter feature.  This allows selction of multiple items. Say the parameter is @Employees

    In Query designer for the dataset that controls the listbox and/or sub reports, put something like WHERE EmployeeId in (@Employees).

     


    PG A bit of experimentation by trial and error often helps get round problems.
    Wednesday, November 2, 2011 11:14 PM
  • I need to allow user to types the IDs , Not to select them from list

    Why ?

    The real case is I already have about milion of IDs ( Customer ID for Bank system)

    And It is not usefull to list them in DDL


    khaled Mohamed
    Thursday, November 3, 2011 6:51 AM
  • Hi Khaled_tec,

    If I have not misunderstood, your requirement is to pass some splitted values to drive subreport. To do this, there is no need to use List control.

    1. Create an input parameter in main report(you might have it already), which can input a comma separater text.
    2. Right-click the Subreport, click Subreport Properties.
    3. In the Subreport Properties pane, click Parameters tab. And then Add a parameter with the same name as it in Subreport, structure the Value expression as:"=Split(Parameters!<ParameterName>.Value,",")"

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.
    Thursday, November 3, 2011 7:28 AM
  • Thanks you Lola Wang

    I have tried this scenario But the sub report is render only for the first value in the Splitted array

    Sub report is not repeated for each value


    khaled Mohamed
    Thursday, November 3, 2011 7:45 AM
  • Hi Khaled_tec,

    Please do check the filter in your Subreport. The parameter value is "=Parameters!<ParaName>.Value", not "=Parameters!<ParaName>.Value(0)".

    Lola


    Please remember to mark the replies as answers if they help.
    Thursday, November 3, 2011 7:48 AM
  • Hi Lala

    Yes it is

    =Split( Parameters!ManyIDs.Value,

    ",")

    And Sub reprot is render for first prameter only , this is why I think List control should be used


    khaled Mohamed
    Thursday, November 3, 2011 7:51 AM
  • Hi Khaled_tec,

    If you have sub report returning the first parameter only, I am afaid that, the List component will do no help either. You have to reset the parameter in subreport with "=Parameters!<ParaName>.Value" or create a new multi-value parameter.

     

    Lola


    Please remember to mark the replies as answers if they help.
    Thursday, November 3, 2011 8:00 AM
  • I have tried Passing from Parent to sub report

    1.       =Parameters!ManyIDs.Value   , It give Error subreport can not shown

    2.       =Parameters!ManyIDs.Value.ToString().Split(",") , It give Error subreport can not shown

    The constrain I have is not to change the sub report, I Just want to repeat it vertically

     


    khaled Mohamed
    Thursday, November 3, 2011 8:39 AM
  • Hi Khaled Mohamed,

    could you please post your .rdl files to our data collection E-mail address?

    E-mail: sqltnsp AT microsoft.com (Please replace the AT with @, and remove additional

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.
    Thursday, November 3, 2011 8:40 AM
  • Hi Khaled Mohamed,

    As I have checked your rdl files, I noticed the Contact ID field is Integer data type. Since a comma separated text will be a string type, we cannot convert an array(result of splitting) to Integer. We might need a little changer about parameter configuration on “SubReport”.

    1.    Open “SubReport”, reconfigure the @PersonID as a multi-value parameter.

    2.    Change the main dataset query as
    SELECT [ContactID]

          ,[Title]

          ,[FirstName]

          ,[MiddleName]

          ,[LastName]

    FROM [AdventureWorks].[Person].[Contact]

    where convert(nvarchar,[ContactID]) in (@PersonID)

    3.    Open “ParentReport”, reconfigure the @ManyIDs by clearing the check box Allow multiple values and without any Avalilable Values and Default Values. Remember to choose Text for Data type.

    4.    Right click the sub report in ParentReport to open Subreport Properties pane. Add PersonID parameter with value “=Split(Parameters!ManyIDs.Value,",")”

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.
    Thursday, November 3, 2011 9:57 AM
  • I follow those steps , But It give me error , could you Reply to my Email with updated files

    What I understood from your steps is

    1. Sub report will dispaly One Table containg many rows (ex: '1,2,3' will display ONE tables with THREE rows)

    What I want is

    1. Repeat Whole sub-Report for each splited value ( ex: '1,2,3' should dispaly 3 tables each one contain One row only )

    In Real case

    1. the sub report contain many charts which I need to dispaly above each one of the 3 tables

    2. The sub report may get data from Tera data , Oracle.....

    **************************************************************************

    In other words If user have 3 Ids he have to follow 3 steps

    * Run subreport with value 1 and Print

    * Run subreport with value 2 and Print

    * Run subreport with value 3 and Print

    I want to provide the user the ability to type the three values in one prameter then the report split it , and Repeat the sub report 3 times So the Print copy of the parent is the equivalent to the 3 Printed copy ( Keep in mind that sub report may contain photos , Chart ,...........)

     

     

     


    khaled Mohamed
    Thursday, November 3, 2011 10:55 AM
  • Hi khaled,

    I have sent you the modified rdl files with email.

    Lola


    Please remember to mark the replies as answers if they help.
    Friday, November 4, 2011 1:19 AM
  • Thanks you very much for you effort When I try your report by parameter “1,2,3 “ , The sub report display ONE table with Three rows , While My Issue is to Display Three Tables each one with ONE row. ( Repeat whole Sub report for each value in the comma separated text By the way I have found a work around this Using List control as I guest from the beginning. Let the user Enter IDs as Comma separated text and make the list control Query the data to get the ID from table where column_ID in ( Comma separated parameter ) . This way allow me to convert from text parameter to data set, Placing the sub report in the list control will make it repeated for each value in the data set
    khaled Mohamed
    • Marked as answer by Khaled_tec Friday, November 4, 2011 7:34 PM
    Friday, November 4, 2011 7:33 PM