Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
billydidit
Starting Member
3 Posts |
 Posted - 2013-02-01 : 20:54:45
|
| I am reporting on TFS data using SQL. I have two work item link types (parent/child and dependencies) where I need to return every work item ID associated with a given ID.That said, I have written two TVFs that return IDs. One returns every child ID in the hierarchy given an ID, and the other returns dependent IDs.I have several situations where I am asking in SQL the following: Give me all the <field values> where <ID> is IN (list of IDs)...so the list of IDs would be what I am after, my question is...WHERE DO I KEEP THE RESULTS FOR FUTURE USE?The results can be anywhere from no records to a few hundred, and there are no more than 50,000 IDs to check. How would you store the results for use in a SSRS report?-Bill |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-02 : 03:19:26
|
| you need to put the results in temp tables and retrieve them in final select inside a procedure. then call this procedure from ssrs dataset to use the data in report.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
billydidit
Starting Member
3 Posts |
Posted - 2013-02-02 : 11:04:13
|
Thank you,So can I dynamically create temp tables? and if so how long is the data available for SSRS? How do I know what table name to look for in SSRS? (my entire database re-creates itself every 5 or 15 minutes...cant decide yet on time)For example, ID=12345 has 4 children (12346, 12347, 12348, 12349). Let's say I have hundreds of IDs that I need to do this with...how do I create the temp tables and how do I access them?quote:
Originally posted by visakh16
you need to put the results in temp tables and retrieve them in final select inside a procedure. then call this procedure from ssrs dataset to use the data in report.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
-Bill |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-02 : 11:26:13
|
| use SELECT...INTO syntax for temporary tables creationInside SSRS everything comes as fields within dataset so you dont have to worry about which table they come from------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
billydidit
Starting Member
3 Posts |
Posted - 2013-02-04 : 09:17:39
|
So I create about 5,000 temp tables and I try to figure out a naming convention that works programmatically from SSRS report?I may have a better idea that came to me during the ideas presented on this thread. Thank you for helping to get the ball rolling.There are about 50 customers so I will generate 50 views that list each ID (for the given customer) descending as rows and again as columns. I think I can do this with a dynamic PIVOT.Combinations that have dependencies or child links will get a 1 where all others get a 0. On the SSRS report I can filter by customer and call the corresponding customer view and return all ID combinations that have a link.quote:
Originally posted by visakh16
use SELECT...INTO syntax for temporary tables creationInside SSRS everything comes as fields within dataset so you dont have to worry about which table they come from------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
-Bill |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 12:19:44
|
| hmm...sounds like an idea. But keep in mind that you cant have datasets with dynamic fields in SSRS. the metadata has to be fixed for you to use columns in the report------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Storing fields with a variable # of values