Passing data from 1 datasource to datasource2 or filter of a table

[danparker276]

From what I read I don't think it could be possible to pass a field value from datasource1 to be a parameter of datasource2 in a subreport, but could I filter datasource2 based on a field of datasource1.

I don't want to use your product groups example because datasource1 has a lot of fields.
Say I have stores and products and I want to list store information and the products each store carries.
Now I could write 1 query to join products and store, but then I'd be repeating 10 columns of my store data for every product and it would be a very large result set.

say I'd want the report to look like:

StoreID | Store Name| Store Hours| Store Owner| Store Street| Store City| Store State ... ( few more)
  1                MyStore1    8-10               Me     ...
                                                                                                Table Product List MyStore1
                                                                                                       Cookies
                                                                                                        Milk
                                                                                                        Eggs
    2             MyStore2    9-5                Joe     ...
                                                                                                Table ProductList MyStore2
                                                                                                         Steak
                                                                                                          Eggs
                                                                                                          Orange Juice

What would be the best way to accomplish this.  I have a large amount of data, so join the data in 1 query probably isn't ideal.
                                                                                               

[danparker276]

Ok, I watched one of your videos filtering on:
= ReportItem.Parent.DataObject.storeId  will work.  I take it this is the best way to do it.  But you can only do that in the filter, you can't add that as a parameter to the datasource unless you use needDataSource right?

[Peter]

Hello Danparker276,

The ReportItem.DataObject expression references the parent's data item (in your case master report) data. In this line of thoughts the ReportItem.DataObject.storeId  retrieves the storeId from the master report's datasource when used as subreport's SqlDataSource parameter value. Thus our suggestion is to use datasource components relations capability. For example when SqlDataSource1 is set to the master report and SqlDataSource2 is set to the SubReports. You can set SqlDataSource2 datasource parameter to a SqlDataSource1 field as shown in the following example:

 

Name	Type	Value
@storeId	String	= ReportItem.DataObject.storeId

Check out the attached sample reports that illustrate the suggested approach. All the best,
Peter
the Telerik team

[danparker276]

I wondering about performance and memory though.  Say I have 10,000 items for datasource1.  Then I'd be making 10,000 queries for datasource2 in the subreport.  If I put datasource1 and datasource2 in the same report and created a table and a filter instead of a subreport, I think it would only do 1 query then right?  If I'm going to display all the data anyway, a filter would work better than a datasource parameter.

And my clients like to print out a lot of pages, thats the reason for a lot of items.

[Peter]

Hi Danparker276,

You are correct the datasource parameters approach will result in database query for every subreport instance. Thus if this will cause performance troubles our suggestion is to avoid the datasource parameters approach. 

We are not sure about the table filter approach you have in mind. However every table (data item) instance have separate datasource instance. They will not share the same data and separate query will be executed. Thus our suggestion is to create a method with some custom logic that will fill the dataset or you own caching custom type, just once and use the table's NeedDataSource event to call your method and set the datasource as shown in the following code snippet:

private void table_NeedDataSource(object sender, EventArgs e)
{
    ((Telerik.Reporting.Processing.Table)sender).DataSource = this.getData();
}

private object getData()
{
    if (this.productCatalogDataSet.ProductCatalogDataSetTable.Count == 0)
    {
        this.productCatalogDataSetTableAdapter1.Fill(this.productCatalogDataSet.ProductCatalogDataSetTable);
    }

    return this.productCatalogDataSet.ProductCatalogDataSetTable;
}

Kind regards,
Peter
the Telerik team

[danparker276]

Ok thanks that will work.

[Fridli Jacober]

[quote]Ok, I watched one of your videos filtering on: = ReportItem.Parent.DataObject.storeId  will work.

...[/quote]

Thank you!

But is something similar possible to use in the report document name? Say I have a report filter list (public report parameter) which display text items but uses the values/ids to filter the data, is it then possible to use the filter texts in the report document name?
E. g. I've got work reports for persons with specific numbers. Is it then possible to filter with the reports ids but use the reports numbers as document name (file name)?

[quote]Peter said:Hi Danparker276,

You are correct the datasource parameters approach will result in database query for every subreport instance. Thus if this will cause performance troubles our suggestion is to avoid the datasource parameters approach.[/quote]

 I don't get that...

Regards

[Stef]

Hello Fridli,

If I understand correctly you have a report parameter, which has AvailableValues -  DataSource, ValueMember and DisplayMember settings. In the report you use the value coming from the ValueMember, and on export you want to name the document based on the DisplayMember value.

The report's DocumentName property is used to name the file saved on exporting a report, and you can set it to the report parameter's Label e.g., '= Parameters.Parameter1.Label", which will return the DisplayMember value.


If I have misunderstood you, please post a demo report and step by step instructions how the user interacts with it, and when and how the name of the report must be changed.

Regards,
Stef
Telerik

[Fridli Jacober]

Hi Stef

The document naming based on the filter value's label is working. Thank you!

Another problem which applies to the topic:
I have a list with an image where I use ReportItem.DataObject.FieldA for a list data source parameter which works. Now for the image I want to access ReportItem.Parent.DataObject.FieldB which is not defined in the current context.
How to access report data fields from the image (or another item) within the list?

Thank you!

[Fridli Jacober]

P.S. The same applies to the document name. I tried to name the file export based on the report content's title (ReportItem.DataObject.Object_Title) but I guess this is not possible this way...?

[Stef]

Hello Fridli,

On your questions:

1. An expression like =ReportItem.Parent.DataObject.FieldX will get the value of FieldX which must exist in the schema of the data visualized by the Table item's container (parent).
   In order to provide you more accurate suggestions, please elaborate on the report layout, where the Table item is place, what data is visualized by the report, and what data is visualized by the Table item.
2. The Report.DocumentName property cannot be set to a data field from your data source as the name is evaluated before the report's data is available. Thus we can suggest you to use a user function getting the desired name.

Regards,
Stef
Telerik

[Fridli Jacober]

Hi Stef

1. Why would you use "=ReportItem.Parent.DataObject.FieldX" when you can just use "=Fields.FieldX" or did I misunderstand you?
I'd like to access report data fields (from the report source) from the image (or another item) within the list which is placed in the report.​

2. Yes it thought to remember it's like this

Regards

[Fridli Jacober]

Got it to work with "ReportItem.Parent.Parent.DataObject.Organisation_ID". Thought I already tried this but I also mean to remember that there's a way to directly access the report's data source / fields?

Regards

[Stef]

Hello Fridli,

When you are in the scope of a nested data item, to get a parent's data scope you need to go through the ReportItem and its Parent processing elements.

Other approach is to have the data at the level of the nested data items (in its data source) and thus use it as a field directly.

Regards,
Stef
Telerik