SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table within a Table with different datasets


Table within a Table with different datasets

Author
Message
Ronald R Mayfield
Ronald R Mayfield
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 67
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88060 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ronald R Mayfield
Ronald R Mayfield
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 67
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.
Ronald R Mayfield
Ronald R Mayfield
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 67
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.
sidneyljohnson
sidneyljohnson
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 48
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.



aktikt
aktikt
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 413
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?
bridget.wagenbach
bridget.wagenbach
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 39
Do you have a short example of this "enumerated" shared dataset?
aktikt
aktikt
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 413
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.
aktikt
aktikt
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 413
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.
maddogs
maddogs
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search