Data Extract Question

Hi, I'm hoping someone has an idea or two on this topic. Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
----
ContactID
Email
Name
tQuestion
----
QuestionID
Question
tAnswer
----
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to seethe data with one line per contact, but showing every answer to everyquestion... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc......
Obviously to get the data I cansimply do an outerjoin to get allcontact data then all questions, and answers that exist... but thatwill obviously return tabular data with one row per eachanswer... Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively... any help would be more thanappreciated. Thanks in advance.
-e

emaxwell wrote:

Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively

Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).

Data Extension for SQL Server Reporting Services

Is there any other then "Data Extension process" way to manipulate a Dataset ?
Is it a good idea to edit dataset from SQL server using "Data Extension
process" ?
Thanks, JoshWhat is it you are trying to do?
I would stay away from a data extension unless you absolutely have no other
way of solving the problem. It is non-trivial plus it will be unneccesary
when version 2 comes out (probably late summer). Version 2 will have both a
web form and winform control that you can pass a dataset to.
I have found that usually people can get what they need done by using a
stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Josh T" <Josh T@.discussions.microsoft.com> wrote in message
news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> Is there any other then "Data Extension process" way to manipulate a
> Dataset ?
> Is it a good idea to edit dataset from SQL server using "Data Extension
> process" ?
> Thanks, Josh|||Thanks Bruce!
We are trying to solve a conceptual problem: is possible to add a business
layer to a dataset, although it was received from SQL server, before it'll be
processed by Report Server e.g. how we can manipulate dataset before it gets
into report.
Best regards, Ilya
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||I've been struggling with this same question. The data extension IS daunting
(maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
(passing lots of parms from form data). With the resulting ADO.Net dataset, I
want to display a report in .PDF format. I've been struggling with all the
mechanics of capturing a model representation of the data as an XML file,
creating an XSD file from that, using the XSD file in the Report Designer.
Preview does not work for me unless I strip a bit of the XML file into the
Designer (I actually have to type it in; PASTE after COPY only puts in one
element - weird?). Then I deploy the RDL. When I run the app it works until I
get to the .RENDER method when it fails saying "item not found" regarding my
reportPath parameter.
In short, I agree this is fraught with problems. How can I accomplish the
display of a PDF report with a stored procedure?
--
John
"Josh T" wrote:
> Thanks Bruce!
> We are trying to solve a conceptual problem: is possible to add a business
> layer to a dataset, although it was received from SQL server, before it'll be
> processed by Report Server e.g. how we can manipulate dataset before it gets
> into report.
> Best regards, Ilya
> "Bruce L-C [MVP]" wrote:
> > What is it you are trying to do?
> >
> > I would stay away from a data extension unless you absolutely have no other
> > way of solving the problem. It is non-trivial plus it will be unneccesary
> > when version 2 comes out (probably late summer). Version 2 will have both a
> > web form and winform control that you can pass a dataset to.
> >
> > I have found that usually people can get what they need done by using a
> > stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > > Is there any other then "Data Extension process" way to manipulate a
> > > Dataset ?
> > > Is it a good idea to edit dataset from SQL server using "Data Extension
> > > process" ?
> > >
> > > Thanks, Josh
> >
> >
> >|||Could someone notice my post dated 5-12-2005 and help me out with an answer
or comment? Thanks.
--
John
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||You need to rethink this. You are making it wayyy more complicated than it
needs to be. From your web app you use either URL integration or Web
services. URL integration is easier. You call the report. The report uses
the stored procedure as its data source. When you call the report you
specify that it render it as PDF. The report has report parameters that
your web apps specifies when it calls the report.
Note, first get the report working prior to integrating with your app. I
usually first hard code the parameters to the stored procedure then I take
out the hard coded values and make the query parameters. When you create a
query parameter RS automatically (usually) creates the report parameter for
you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> I've been struggling with this same question. The data extension IS
> daunting
> (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> (passing lots of parms from form data). With the resulting ADO.Net
> dataset, I
> want to display a report in .PDF format. I've been struggling with all the
> mechanics of capturing a model representation of the data as an XML file,
> creating an XSD file from that, using the XSD file in the Report Designer.
> Preview does not work for me unless I strip a bit of the XML file into the
> Designer (I actually have to type it in; PASTE after COPY only puts in one
> element - weird?). Then I deploy the RDL. When I run the app it works
> until I
> get to the .RENDER method when it fails saying "item not found" regarding
> my
> reportPath parameter.
> In short, I agree this is fraught with problems. How can I accomplish the
> display of a PDF report with a stored procedure?
> --
> John
>
> "Josh T" wrote:
>> Thanks Bruce!
>> We are trying to solve a conceptual problem: is possible to add a
>> business
>> layer to a dataset, although it was received from SQL server, before
>> it'll be
>> processed by Report Server e.g. how we can manipulate dataset before it
>> gets
>> into report.
>> Best regards, Ilya
>> "Bruce L-C [MVP]" wrote:
>> > What is it you are trying to do?
>> >
>> > I would stay away from a data extension unless you absolutely have no
>> > other
>> > way of solving the problem. It is non-trivial plus it will be
>> > unneccesary
>> > when version 2 comes out (probably late summer). Version 2 will have
>> > both a
>> > web form and winform control that you can pass a dataset to.
>> >
>> > I have found that usually people can get what they need done by using a
>> > stored procedure.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
>> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
>> > > Is there any other then "Data Extension process" way to manipulate a
>> > > Dataset ?
>> > > Is it a good idea to edit dataset from SQL server using "Data
>> > > Extension
>> > > process" ?
>> > >
>> > > Thanks, Josh
>> >
>> >
>> >|||--
John
"Bruce L-C [MVP]" wrote:
> You need to rethink this. You are making it wayyy more complicated than it
> needs to be. From your web app you use either URL integration or Web
> services. URL integration is easier. You call the report. The report uses
> the stored procedure as its data source. When you call the report you
> specify that it render it as PDF. The report has report parameters that
> your web apps specifies when it calls the report.
> Note, first get the report working prior to integrating with your app. I
> usually first hard code the parameters to the stored procedure then I take
> out the hard coded values and make the query parameters. When you create a
> query parameter RS automatically (usually) creates the report parameter for
> you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
> news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> > I've been struggling with this same question. The data extension IS
> > daunting
> > (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> > (passing lots of parms from form data). With the resulting ADO.Net
> > dataset, I
> > want to display a report in .PDF format. I've been struggling with all the
> > mechanics of capturing a model representation of the data as an XML file,
> > creating an XSD file from that, using the XSD file in the Report Designer.
> > Preview does not work for me unless I strip a bit of the XML file into the
> > Designer (I actually have to type it in; PASTE after COPY only puts in one
> > element - weird?). Then I deploy the RDL. When I run the app it works
> > until I
> > get to the .RENDER method when it fails saying "item not found" regarding
> > my
> > reportPath parameter.
> >
> > In short, I agree this is fraught with problems. How can I accomplish the
> > display of a PDF report with a stored procedure?
> > --
> > John
> >
> >
> > "Josh T" wrote:
> >
> >> Thanks Bruce!
> >> We are trying to solve a conceptual problem: is possible to add a
> >> business
> >> layer to a dataset, although it was received from SQL server, before
> >> it'll be
> >> processed by Report Server e.g. how we can manipulate dataset before it
> >> gets
> >> into report.
> >>
> >> Best regards, Ilya
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> > What is it you are trying to do?
> >> >
> >> > I would stay away from a data extension unless you absolutely have no
> >> > other
> >> > way of solving the problem. It is non-trivial plus it will be
> >> > unneccesary
> >> > when version 2 comes out (probably late summer). Version 2 will have
> >> > both a
> >> > web form and winform control that you can pass a dataset to.
> >> >
> >> > I have found that usually people can get what they need done by using a
> >> > stored procedure.
> >> >
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> >> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> >> > > Is there any other then "Data Extension process" way to manipulate a
> >> > > Dataset ?
> >> > > Is it a good idea to edit dataset from SQL server using "Data
> >> > > Extension
> >> > > process" ?
> >> > >
> >> > > Thanks, Josh
> >> >
> >> >
> >> >
>
>