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

14 posts, 1 answers
  1. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 21 Aug 2011 Link to this post

    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.
                                                                                                   
  2. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 21 Aug 2011 Link to this post

    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?
  3. DevCraft banner
  4. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 22 Aug 2011 Link to this post

    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

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's DevProConnections Awards. We are competing in mind-blowing 20 categories and every vote counts! VOTE for Telerik NOW >>

  5. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 22 Aug 2011 Link to this post

    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.
  6. Answer
    Peter
    Admin
    Peter avatar
    1611 posts

    Posted 23 Aug 2011 Link to this post

    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

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's DevProConnections Awards. We are competing in mind-blowing 20 categories and every vote counts! VOTE for Telerik NOW >>

  7. danparker276
    danparker276 avatar
    389 posts
    Member since:
    Aug 2010

    Posted 23 Aug 2011 Link to this post

    Ok thanks that will work.
  8. Fridli Jacober
    Fridli Jacober avatar
    89 posts
    Member since:
    Dec 2009

    Posted 04 Sep 2015 in reply to Peter Link to this post

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

    ...

    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)?

    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.

     I don't get that...

    Regards

  9. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 08 Sep 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  10. Fridli Jacober
    Fridli Jacober avatar
    89 posts
    Member since:
    Dec 2009

    Posted 08 Oct 2015 in reply to Stef Link to this post

    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!

  11. Fridli Jacober
    Fridli Jacober avatar
    89 posts
    Member since:
    Dec 2009

    Posted 09 Oct 2015 in reply to Stef Link to this post

    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...?
  12. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 13 Oct 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  13. Fridli Jacober
    Fridli Jacober avatar
    89 posts
    Member since:
    Dec 2009

    Posted 13 Oct 2015 in reply to Stef Link to this post

    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

  14. Fridli Jacober
    Fridli Jacober avatar
    89 posts
    Member since:
    Dec 2009

    Posted 14 Oct 2015 in reply to Stef Link to this post

    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

  15. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 15 Oct 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
DevCraft banner