Table populated from stored procedure

[Paul]

I'm trying to generate a report which uses data in a parent-child relationship.  For any Item X, it may have any number of Sub-Items.   I can set this up to work with a subreport, but then the Sub-items will only list vertically, as a subreport cannot be multi-columned. 

Also, the Sub-Items are being generated from a Stored Procedure which takes as a parameter the key of the Item.

I was hoping to overcome this by including a table in the main report which used the Stored Procedure data to list the Sub-Items, but I'm not sure how to execute the stored procedure for each item in the main report.  Can this be done?

[Elian]

Hello,

You can do the following:

1. Bind the report to a data-source that will give you the Items (Item 1, Item 2....) (Let's name it ReportDataSource)
2. Inside the detail section you can put a List control (that will give you the sub-items)
3. Right-click on the List control and select Rotate Layout - this will make the items repeat horizontally and not vertically.
4. Create a new data-source that will take ItemKey as a parameter (Let's name it ListDataSource).
5. The parameter of the ListDataSource can be assigned to "=Fields.ItemKey" - this is from the context of the ReportDataSource
6. This way, the proper parameter value will be passed to the ListDataSource for each row in the ReportDataSource.

Greetings,
Elian
the Telerik team

[Paul]

Thanks for the reply!  I think this is exactly what I want to do, but I'm a little unclear on point 5.

I want to set the parameters for ListDataSource to be the "=Fields.ItemKey" from the ReportDataSource, but I'm not sure where to do this.  In the designer, the two datasources are not linked, so how would I get access to "=Fields.ItemKey" to set the parameter in ListDataSource?

If this is done through code-behind, would I use the NeedDataSource event on the list and not associate the list with the ListDataSource in the designer?

[Elian]

Hi Paul,

Here are some more details on the 5th step:

1. You have sqlDataSource1 that is the data-source for the report and it will give you all the Items. Let's say that the Key value is named "ItemKey" - this will be the field that is the key to getting the sub-items.
2. You create sqlDataSource2  that will be the data-source that gets the sub-items. When you build the query (using the sqlDataSource Wizard) you add a parameter:
   

   SELECT .....
   FROM ......
   WHERE ItemKey = @ItemKey
   --you define @ItemKey as parameter

3. On the next step you will be asked to set where this @ItemKey parameter will get it's value from.
4. Then you bind it to the report's context current item (see Bindings).
   

   =ReportItem.DataObject.ItemKey

   This line will give you the data object in the current context (this would be the current row from sqlDataSource1 )
5. Finish the wizard.
6. Add List component and assign sqlDataSource2 as data-source for it.

All the best,
Elian
the Telerik team

[Paul]

That is exactly what I wanted.  Thanks for explaining!