SQL Server Performance

how to link many datasets

Discussion in 'SQL Server 2005 Reporting Services' started by fenny, Aug 29, 2006.

  1. fenny New Member

    hello, i'm still new in sql server 2005 reporting services.i face a problem when doing my project.

    in my report, i have several datasets in one report.how can i link these datasets?the parameters between these datasets are the same,the different between these datasets are the where condition. i need to merge all the datasets into one report table. is there any suggestion?thanks.
  2. smy New Member

    i doubt it can be done. one report table can only link with one dataset. If you are to input the other dataset field to this report table, the only data the report can display is the first record from the query statement or sum of the data in the records retrieved by the query statement.
  3. jhermiz New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by fenny</i><br /><br />hello, i'm still new in sql server 2005 reporting services.i face a problem when doing my project.<br /><br /> in my report, i have several datasets in one report.how can i link these datasets?the parameters between these datasets are the same,the different between these datasets are the where condition. i need to merge all the datasets into one report table. is there any suggestion?thanks.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />100% possible, but you are looking at it from a difficult angle.<br /><br />A dataset is simply some SQL code right? In fact, its a stored procedure (or at least make it a stored procedure). If you have multiple datasets it means you have multiple sprocs right? So make one sproc that includes all the SQL you need in it and use that in a dataset.<br /><br />BTW in general you should have n parameter datasets and 1 main dataset.<br />Let us look at a simple example. Let us suppose you work for a dealership who sells cars.<br /><br />You want to display cars based on Factory (GM, Ford, Chrysler, etc) and Model (GM-&gt;Hummer, Ford-&gt;Mustang, that is you have a relationship where a factory has many models (1 to many)). So what y ou would have is 3 datasets:<br /><br />1 dataset to pull Factories:<br /><br />SELECT FactoryID FROM Factory<br /><br />another dataset to pull models BASED on the Factory:<br /><br />Select ModelID FROM FactoryModel WHERE FactoryID = @FactoryID<br /><br />that is you have a query to pull the models based on a parameter.<br /><br />Finally you should have ONE last dataset, this dataset should be used in the actual report:<br /><br />SELECT Description, <br />FactoryID, ModelID, etc.. FROM DealerCar<br />WHERE<br />(FactoryID = @FactoryID)<br />AND (ModelID = @ModelID)<br />AND (...whatever else here...)<br /><br />That's all there is to it. If there is anything easy in life its reporting services, it has been designed to be very intuitive.<br /><br />Post back should you have questions or review my blog for more tips and tricks.<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />My Site: <a href='http://jhermiz.googlepages.com' target='_blank' title='http://jhermiz.googlepages.com'<a target="_blank" href=http://jhermiz.googlepages.com>http://jhermiz.googlepages.com</a></a><br />Great SQL Books:<br /><a href='http://jhermiz.googlepages.com/sqlbooks' target='_blank' title='http://jhermiz.googlepages.com/sqlbooks'<a target="_blank" href=http://jhermiz.googlepages.com/sqlbooks>http://jhermiz.googlepages.com/sqlbooks</a></a><br />Great Dot Net Books:<br /><a href='http://jhermiz.googlepages.com/dotnetbooks' target='_blank' title='http://jhermiz.googlepages.com/dotnetbooks'<a target="_blank" href=http://jhermiz.googlepages.com/dotnetbooks>http://jhermiz.googlepages.com/dotnetbooks</a></a>

Share This Page