Dynamically adding fields to a report.

Not sure how to tackle this one. I need to create a report that dynamically
adds fields based on information from a data store.
[Abstracting the problem for clarity]
Let's say I have a table with contact data (FirstName, LastName, Street,
City, State, Zip) and a customer table with a definition of what contact
fields they use and how to render them.
I need a report that I pass a customerId and it looks up what fields to
pull, say customer1's report would look like:
FirstName LastName
Zip
And customer2's report would look like
FullName (Combining First and LastName)
Street
City, Zip
Each report needs to only show the relavant data, and additionlly position
it according the cutomer's defined format. Creating a report for each
customer is out of the question, way too many customers, and that's just
retarded. I thought about adding every field to the report and hiding the
ones that weren't used but positioning would be a headache.
Is there a way that I can dynamically create and add fields to the report at
runtime?
Any ideas would be greatly appriciated.Sorry, dupe of a previous submit. See Below.|||The short answer is no, since dynamically adding report elements is not
supported in version 1.0 of Reporting Services. That said, it looks like the
best workaround in your case is to pre-process the report by loading the
report definition in XML DOM and add/remove the fields you don't need. This
will require an application front-end to generate the report definition,
upload and generate the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> Not sure how to tackle this one. I need to create a report that
dynamically
> adds fields based on information from a data store.
> [Abstracting the problem for clarity]
> Let's say I have a table with contact data (FirstName, LastName, Street,
> City, State, Zip) and a customer table with a definition of what contact
> fields they use and how to render them.
> I need a report that I pass a customerId and it looks up what fields to
> pull, say customer1's report would look like:
> FirstName LastName
> Zip
> And customer2's report would look like
> FullName (Combining First and LastName)
> Street
> City, Zip
> Each report needs to only show the relavant data, and additionlly position
> it according the cutomer's defined format. Creating a report for each
> customer is out of the question, way too many customers, and that's just
> retarded. I thought about adding every field to the report and hiding the
> ones that weren't used but positioning would be a headache.
> Is there a way that I can dynamically create and add fields to the report
at
> runtime?
> Any ideas would be greatly appriciated.|||You need something like ad-hoc right?
It will be available with SQL 2005.
But right now you can dynamicly build query in SQL. I did it once. it
is not perfect report but idea is :
1. create parameter string where user will put field they want to see
using coma delimeter
2. pass this string on SP and parse it into the table.
generate query what return you what ever you need just remember each
field should be name generic (col1, col2, col3) and sequence should be
how they want to see it on the screen
3. create report base on col1, col2 col3 data returned.
If you need more info please send me email(natta@.netzero.net). I will
try to send you example.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message news:<#KjqSlqzEHA.1564@.TK2MSFTNGP09.phx.gbl>...
> The short answer is no, since dynamically adding report elements is not
> supported in version 1.0 of Reporting Services. That said, it looks like the
> best workaround in your case is to pre-process the report by loading the
> report definition in XML DOM and add/remove the fields you don't need. This
> will require an application front-end to generate the report definition,
> upload and generate the report.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Joshua Belden" <JoshuaBelden@.discussions.microsoft.com> wrote in message
> news:35B3E7B6-3BFB-4985-A1D9-CDCF261FED82@.microsoft.com...
> > Not sure how to tackle this one. I need to create a report that
> dynamically
> > adds fields based on information from a data store.
> >
> > [Abstracting the problem for clarity]
> > Let's say I have a table with contact data (FirstName, LastName, Street,
> > City, State, Zip) and a customer table with a definition of what contact
> > fields they use and how to render them.
> >
> > I need a report that I pass a customerId and it looks up what fields to
> > pull, say customer1's report would look like:
> > FirstName LastName
> > Zip
> >
> > And customer2's report would look like
> > FullName (Combining First and LastName)
> > Street
> > City, Zip
> >
> > Each report needs to only show the relavant data, and additionlly position
> > it according the cutomer's defined format. Creating a report for each
> > customer is out of the question, way too many customers, and that's just
> > retarded. I thought about adding every field to the report and hiding the
> > ones that weren't used but positioning would be a headache.
> >
> > Is there a way that I can dynamically create and add fields to the report
> at
> > runtime?
> >
> > Any ideas would be greatly appriciated.