SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Storing fields with a variable # of values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

billydidit
Starting Member

USA
3 Posts

Posted - 02/01/2013 :  20:54:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/02/2013 :  03:19:26  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

billydidit
Starting Member

USA
3 Posts

Posted - 02/02/2013 :  11:04:13  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/





-Bill
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/02/2013 :  11:26:13  Show Profile  Reply with Quote
use SELECT...INTO syntax for temporary tables creation

Inside SSRS everything comes as fields within dataset so you dont have to worry about which table they come from




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

billydidit
Starting Member

USA
3 Posts

Posted - 02/04/2013 :  09:17:39  Show Profile  Reply with Quote
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 creation

Inside SSRS everything comes as fields within dataset so you dont have to worry about which table they come from




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





-Bill
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/04/2013 :  12:19:44  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000