Dynamic Columns in Report

I have two tables with structure something like
User Attributes
AttributeID
AttributeName
Description
IsRequired
ChoiceArray
OptionType
DefaultValue
MaxVal
MinVal
Mask
DecimalPlaces
isMultiLine
DateFormat
CompanyID
UDFTypeID
EntityID
isVisible
Values of the tables can be
User notes
Published comments
Reject reasons
Whereas transaction Entity can be some other entity like purchase order, etc
and this information will saved for particular record as per customersâ'
needs. There can be thousand of customer with their own fields ranging from
0-20. The values of these user defined fields are stored in another tables
with relationship to user defined field and business transactionID. For
example for purchase orders values tables would be
UDFValueID
POID
Value
AttributeID
Values of the tales can be
1 1 Some Values 1
2 1 Some Values 2
3 1 Some Values 3
I need to develop a report that could be able to display the information in
format like
User notes Published comments Reject reasons etc. etc.
Some Values some value some reasons
I know that using pivot I can show the rows in columns, but I really donâ't
know how I can do that same with reporting services.
My question is how I can create/Add columns in reports on runtime or
dynamically. As per above requirement.A matrix table will allow dynamic columns.
"nettellect" wrote:
> I have two tables with structure something like
>
> User Attributes
> AttributeID
> AttributeName
> Description
> IsRequired
> ChoiceArray
> OptionType
> DefaultValue
> MaxVal
> MinVal
> Mask
> DecimalPlaces
> isMultiLine
> DateFormat
> CompanyID
> UDFTypeID
> EntityID
> isVisible
> Values of the tables can be
> User notes
> Published comments
> Reject reasons
> Whereas transaction Entity can be some other entity like purchase order, etc
> and this information will saved for particular record as per customersâ'
> needs. There can be thousand of customer with their own fields ranging from
> 0-20. The values of these user defined fields are stored in another tables
> with relationship to user defined field and business transactionID. For
> example for purchase orders values tables would be
> UDFValueID
> POID
> Value
> AttributeID
> Values of the tales can be
> 1 1 Some Values 1
> 2 1 Some Values 2
> 3 1 Some Values 3
> I need to develop a report that could be able to display the information in
> format like
> User notes Published comments Reject reasons etc. etc.
> Some Values some value some reasons
> I know that using pivot I can show the rows in columns, but I really donâ't
> know how I can do that same with reporting services.
> My question is how I can create/Add columns in reports on runtime or
> dynamically. As per above requirement.
>|||is there any sample or wlkthrough that could help me in developing this
"Jimbo" wrote:
> A matrix table will allow dynamic columns.
>
>
> "nettellect" wrote:
> > I have two tables with structure something like
> >
> >
> > User Attributes
> >
> > AttributeID
> > AttributeName
> > Description
> > IsRequired
> > ChoiceArray
> > OptionType
> > DefaultValue
> > MaxVal
> > MinVal
> > Mask
> > DecimalPlaces
> > isMultiLine
> > DateFormat
> > CompanyID
> > UDFTypeID
> > EntityID
> > isVisible
> >
> > Values of the tables can be
> >
> > User notes
> > Published comments
> > Reject reasons
> >
> > Whereas transaction Entity can be some other entity like purchase order, etc
> > and this information will saved for particular record as per customersâ'
> > needs. There can be thousand of customer with their own fields ranging from
> > 0-20. The values of these user defined fields are stored in another tables
> > with relationship to user defined field and business transactionID. For
> > example for purchase orders values tables would be
> >
> > UDFValueID
> > POID
> > Value
> > AttributeID
> >
> > Values of the tales can be
> > 1 1 Some Values 1
> > 2 1 Some Values 2
> > 3 1 Some Values 3
> >
> > I need to develop a report that could be able to display the information in
> > format like
> >
> > User notes Published comments Reject reasons etc. etc.
> > Some Values some value some reasons
> >
> > I know that using pivot I can show the rows in columns, but I really donâ't
> > know how I can do that same with reporting services.
> >
> > My question is how I can create/Add columns in reports on runtime or
> > dynamically. As per above requirement.
> >|||Design a dataset with a structure like:
RowValue
ColumnValue
CellValue
Create the matrix table and place those fields in the appropraite sections.
The matrix table will create columns to the number of unique column values
The matrix table will create rows to the number of unique row values
The intersection of row/column will be populated with your cell value
(usually a sum(cellvalue) )
If this doesn't help ask the google god.
"nettellect" wrote:
> is there any sample or wlkthrough that could help me in developing this
> "Jimbo" wrote:
> > A matrix table will allow dynamic columns.
> >
> >
> >
> >
> >
> > "nettellect" wrote:
> >
> > > I have two tables with structure something like
> > >
> > >
> > > User Attributes
> > >
> > > AttributeID
> > > AttributeName
> > > Description
> > > IsRequired
> > > ChoiceArray
> > > OptionType
> > > DefaultValue
> > > MaxVal
> > > MinVal
> > > Mask
> > > DecimalPlaces
> > > isMultiLine
> > > DateFormat
> > > CompanyID
> > > UDFTypeID
> > > EntityID
> > > isVisible
> > >
> > > Values of the tables can be
> > >
> > > User notes
> > > Published comments
> > > Reject reasons
> > >
> > > Whereas transaction Entity can be some other entity like purchase order, etc
> > > and this information will saved for particular record as per customersâ'
> > > needs. There can be thousand of customer with their own fields ranging from
> > > 0-20. The values of these user defined fields are stored in another tables
> > > with relationship to user defined field and business transactionID. For
> > > example for purchase orders values tables would be
> > >
> > > UDFValueID
> > > POID
> > > Value
> > > AttributeID
> > >
> > > Values of the tales can be
> > > 1 1 Some Values 1
> > > 2 1 Some Values 2
> > > 3 1 Some Values 3
> > >
> > > I need to develop a report that could be able to display the information in
> > > format like
> > >
> > > User notes Published comments Reject reasons etc. etc.
> > > Some Values some value some reasons
> > >
> > > I know that using pivot I can show the rows in columns, but I really donâ't
> > > know how I can do that same with reporting services.
> > >
> > > My question is how I can create/Add columns in reports on runtime or
> > > dynamically. As per above requirement.
> > >