Post reply

Table within a Table with different datasets

  • December 5, 2007 at 5:40 pm

    #184587

    I have a report that has a table with another table within one of the textboxes. The table within contains a set of data items relating to the groups in the outside table. This was working fine until I split the dataset into two different datasets. When I change the contained tables dataset to a new one, I get the error that the report items expression can only refer to fields with the datascope. I tried to reselect the fields within the contained tables new dataset, but it only shows the available fields from the outside table dataset.

  • December 5, 2007 at 10:27 pm

    #757513

    Ummmm.... you didn't save a copy of the original report that worked?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 6, 2007 at 8:37 am

    #757698

    I do have the original, but I wanted to add some functionality to the report that requires me to split up the datasets. The new stuff works now, but the old enclosed table does not.

  • December 10, 2007 at 11:45 am

    #758707

    I was able to convert the enclosed table into a subreport, so now the subreport is enclosed in one of the cells of the first table and I can use a different dataset for that subreport. That seemed to work fine, except when it has to be exported into an Excel format file for one of the users. Then the subreport is ignored. I am able to export it into a web format file and then import it into Excel and reformat that portion to make it workable.

  • January 31, 2008 at 1:50 pm

    #773932

    you will need to merge your datasets into one dataset.

    RS doesn't support multiple datasets with a connection btw the datasets. However if you merge the data into one dataset and add one column with an enumerator(1 = first "dataset", 2= 2nd "dataset) you add additional tables with a filter set to the shared dataset pointing to that enumerated column be sure that you use the = as operator and =1 in the field for the filter. int data types require the =X instead of just X if it was a string enumeration.

    bottom line is you can "sub report" without sub-reports using the shared dataset with a filter column and using the filter properties of a list or table. Be sure to populate the shared dataset with any data used to join the "multiple datasets" in appropriate columns that can be grouped on... such that each enumerated dataset will have its own data columns plus the enumerated column plus any columns needed for the grouping connections between the "multiple datasets"

    I took a report with 26 subreports(based on 1 to N cardinality requirements) into a single dataset with a filter on each nested table. Prior to the change imagine 5000 records calling the databases one entity at time populating each sub report. After the change, not only does excel work with the nested filtered tables(ie virtual sub-reports) but sql only gets hit once instead of 5000x26 times. Let sql use the power of set logic and reporting services filter out the appropriate rows it needs for each nested table/list. Note that as you add additional enumerated sections the data spreads out diagonally down your table when observing the output in sql. While there maybe a lot of white space this is currently the only option short of using report builder off of a data model that which lets you connect multiple data sets together. It is very important that you try to grasp the single DS/filtered concept for 1 to N cardinality/multiple data set situations. This is a make or break concept for RS and it pains me and Teo Lachev to see that MSFT hasn't allowed us a more efficient way to resolve multiple data set requirements.

  • July 1, 2008 at 9:35 pm

    #836980

    The table within a table approach works really well as described above. I am using that approach with a little variation. Anyway, I'm running into a problem with formatting. It seens the data is being offset for some reason. That is, the text from one row in the table is not aligned with the text in the above row. I made sure the alignment was the same and that the padding was the same. Yet, the difference remains. Anyone have any ideas?

  • October 6, 2008 at 2:57 pm

    #881348

    Do you have a short example of this "enumerated" shared dataset?

  • February 10, 2009 at 3:40 pm

    #941719

    The technique described in this thread works well, but if you are bringing in datasets that have no relation this technique will use a cartesian product, in effect a cross join, this approach doesn't work as you will have too many records and the report will slow down. For example, one organization in a database could have 10 phone numbers in one table, 10 emails in another table, 10 addresses in another table, 10 aliases in another table, 10 roles in another ... etc. etc. If the only commonality between the tables is the organization Key then you will get 10 X 10 X 10 X 10 X 10 records resulting. This is not the proper way to code a report. You need to bring the datasets in separately and display them within their own tables It's only when you have parent - child relationships when it makes sense to do technique described.

    I just figured I'd add this point to this thread since a co-worker at my company did the above and I spent a couple of weeks fixing it.

  • February 16, 2009 at 3:40 pm

    #944884

    It has dawned on me recently that the method described above is only good for small data sets. If your code exceeds 30000 records or so it really starts to slow down. Otherwise, sub-reports should be used instead.

  • August 10, 2010 at 12:19 pm

    #1204960

    Hi Sidney - I know this is an old post but I hope you might still be listening in as I am struggling with the merged dataset approach (not with the concept but the execution) and need what I hope to be just one or two simple 'aha' tips to get me over the top.

    I have the merged dataset with record types set up just fine coming out of a stored procedure. I have created a child table I'm trying to nest within another parent report group, with both the child and parent tables pointing to the merged dataset.

    Where I am getting slapped down with different approaches is:

    1) When I try to embed the child table in a detail row within the parent group (detail row with merged all cells into one cell first), on Preview I get an RS error message saying I cannot put a table in a detail row. I tried this both within a data region and on the single row cell with same result.

    2) When I create a new group row on the parent table I can insert the table into that just fine (after first merging the group row into one cell and creating a data region rectangle), the preview looks fine but when exporting to Excel it now says that data regions withn table cells are ignored. This defeats the entire purpose as I had this working as subreports but I could not get the subreports to export to Excel so I tried this suggested approach.

    What bits am I missing with this? Thanks for any assistance.

    Cheers,

    Maddog


    maddog

  • October 31, 2010 at 12:15 pm

    #1244648

    I think its such a shame you have to resort to this technique, or subreports to accomodate something so simple. There is certainly a business case for table-in-table using different datasets but linking using common fields: just think of Microsoft Access and subreports, much much easier than on ssrs.

    www.host-junction.net

  • May 4, 2015 at 5:53 am

    #1795323

    One Solution Can be use of Lookupset function:

    use this expression in one cell

    =String.Join(VbCrLF,

    Lookupset(Fields!joinFieldName.Value,

    Fields!joinFieldName.Value,

    Fields!FieldYouWant.Value ,"secondDataset")

    )

    It joins firstDataSet (current) to second dataset and feed result in every new line.

    Excel output is also well!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply