Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Table within a Table with different datasets Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2007 5:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 5:06 PM
Points: 8, Visits: 61
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.
Post #429975
Posted Wednesday, December 5, 2007 10:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 36,711, Visits: 31,161
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430014
Posted Thursday, December 6, 2007 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 5:06 PM
Points: 8, Visits: 61
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.
Post #430232
Posted Monday, December 10, 2007 11:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 5:06 PM
Points: 8, Visits: 61
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.
Post #431464
Posted Thursday, January 31, 2008 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 14, 2012 3:46 PM
Points: 4, Visits: 41
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.



Post #450165
Posted Tuesday, July 1, 2008 9:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:54 AM
Points: 194, Visits: 412
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?
Post #526997
Posted Monday, October 6, 2008 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 15, 2009 10:39 AM
Points: 2, Visits: 39
Do you have a short example of this "enumerated" shared dataset?
Post #581392
Posted Tuesday, February 10, 2009 3:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:54 AM
Points: 194, Visits: 412
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.

Post #654308
Posted Monday, February 16, 2009 3:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:54 AM
Points: 194, Visits: 412
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.
Post #658123
Posted Tuesday, August 10, 2010 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 15, 2013 6:15 PM
Points: 110, 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
Post #966914
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse