Dynamic Column Ordering

We're trying to put together a pretty advanced implementation of SQL
Reporting. We are trying to devise a way to allow the end user to
dynamically order the columns.
For a variety of reasons we cannot use/deploy the client tool set. Our data
won't fit nicely into models.
So, what we were thinking of is have the calling app send in some string
looking like "1,4,2,3" as a param and have the report know to put col1 first,
col4 second and so on.
Does anyone know, or can anyone think creatively, of a way to do this
without getting into the RDL and effectively re-writing it?
Thanks for any/all feedback and ideas.You could probably do that with dynamic SQL within a stored proc. Then use
the the stored proc as your datasource.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"BrendanMC" <BrendanMC@.discussions.microsoft.com> wrote in message
news:3BFA8CB6-A3F4-4988-A2F3-FDE3D5DED831@.microsoft.com...
> We're trying to put together a pretty advanced implementation of SQL
> Reporting. We are trying to devise a way to allow the end user to
> dynamically order the columns.
> For a variety of reasons we cannot use/deploy the client tool set. Our
> data
> won't fit nicely into models.
> So, what we were thinking of is have the calling app send in some string
> looking like "1,4,2,3" as a param and have the report know to put col1
> first,
> col4 second and so on.
> Does anyone know, or can anyone think creatively, of a way to do this
> without getting into the RDL and effectively re-writing it?
> Thanks for any/all feedback and ideas.
>|||Brendan,
As Alain stated, a stored procedure will work. However if you have an
aversion you can also write a series of CASE statements in to your SQL
Dataset.
Example:
1. Create a parameter for each column, and name it Col1, Col2, Col3,... (or
any naming convention you like).
2. Populate the list from a non-queries list, which has a "label"
representing the display name, and the "value" displaying the actual database
field name you want to call. This will allow the users to see the 'English'
name of the field, why the 'value' will represent the database name of the
field.
3. Write your SQL statement like this:
SELECT CASE Col1
WHEN Col1 = Field1 THEN dbo.mytable.field1
WHEN Col2 = Field1 THEN dbo.mytable.field2
WHEN Col3 = Field1 THEN dbo.mytable.field3
WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 1,
CASE Col2
WHEN Col1 = Field1 THEN dbo.mytable.field1
WHEN Col2 = Field1 THEN dbo.mytable.field2
WHEN Col3 = Field1 THEN dbo.mytable.field3
WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 2,
CASE Col3
WHEN Col1 = Field1 THEN dbo.mytable.field1
WHEN Col2 = Field1 THEN dbo.mytable.field2
WHEN Col3 = Field1 THEN dbo.mytable.field3
WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 3,
CASE Col4
WHEN Col1 = Field1 THEN dbo.mytable.field1
WHEN Col2 = Field1 THEN dbo.mytable.field2
WHEN Col3 = Field1 THEN dbo.mytable.field3
WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 4
FROM dbo.mytable
This applies the same field selection to each dataset field, but looks at a
different parameter to actually determine WHAT field should be displayed.
Then in your report you set the table up and column 1 is always
=Fields!Column1.Value. In summary, your rdl (table) remains static, and
your SQL is dynamic.
Hope this helps.
Michael C.
"Alain Quesnel" wrote:
> You could probably do that with dynamic SQL within a stored proc. Then use
> the the stored proc as your datasource.
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "BrendanMC" <BrendanMC@.discussions.microsoft.com> wrote in message
> news:3BFA8CB6-A3F4-4988-A2F3-FDE3D5DED831@.microsoft.com...
> > We're trying to put together a pretty advanced implementation of SQL
> > Reporting. We are trying to devise a way to allow the end user to
> > dynamically order the columns.
> >
> > For a variety of reasons we cannot use/deploy the client tool set. Our
> > data
> > won't fit nicely into models.
> >
> > So, what we were thinking of is have the calling app send in some string
> > looking like "1,4,2,3" as a param and have the report know to put col1
> > first,
> > col4 second and so on.
> >
> > Does anyone know, or can anyone think creatively, of a way to do this
> > without getting into the RDL and effectively re-writing it?
> >
> > Thanks for any/all feedback and ideas.
> >
>|||In my haste I wrote some bad code (too much Starbucks). Here is an amendment.
SELECT Column1 = CASE @.Col1
WHEN 'Field1' THEN dbo.mytable.field1
WHEN 'Field2' THEN dbo.mytable.field2
WHEN 'Field3' THEN dbo.mytable.field3
WHEN 'Field4' THEN dbo.mytable.field4 END,
Column2 = CASE @.Col2
WHEN 'Field1' THEN dbo.mytable.field1
WHEN 'Field2' THEN dbo.mytable.field2
WHEN 'Field3' THEN dbo.mytable.field3
WHEN 'Field4' THEN dbo.mytable.field4 END,
Column3 = CASE @.Col3
WHEN 'Field1' THEN dbo.mytable.field1
WHEN 'Field2' THEN dbo.mytable.field2
WHEN 'Field3' THEN dbo.mytable.field3
WHEN 'Field4' THEN dbo.mytable.field4 END,
Column4 = CASE @.Col4
WHEN 'Field1' THEN dbo.mytable.field1
WHEN 'Field2' THEN dbo.mytable.field2
WHEN 'Field3' THEN dbo.mytable.field3
WHEN 'Field4' THEN dbo.mytable.field4 END
FROM dbo.mytable
The above code assumes that 'Fieldx' is the value from the parameter.
You may also want to consider that a way which will limit your users from
picking the same column in 2 seperate parameter boxes. If you need a sample
of this let me know and I can provide you something there too.
Michael C.
"Michael C" wrote:
> Brendan,
> As Alain stated, a stored procedure will work. However if you have an
> aversion you can also write a series of CASE statements in to your SQL
> Dataset.
> Example:
> 1. Create a parameter for each column, and name it Col1, Col2, Col3,... (or
> any naming convention you like).
> 2. Populate the list from a non-queries list, which has a "label"
> representing the display name, and the "value" displaying the actual database
> field name you want to call. This will allow the users to see the 'English'
> name of the field, why the 'value' will represent the database name of the
> field.
> 3. Write your SQL statement like this:
> SELECT CASE Col1
> WHEN Col1 = Field1 THEN dbo.mytable.field1
> WHEN Col2 = Field1 THEN dbo.mytable.field2
> WHEN Col3 = Field1 THEN dbo.mytable.field3
> WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 1,
> CASE Col2
> WHEN Col1 = Field1 THEN dbo.mytable.field1
> WHEN Col2 = Field1 THEN dbo.mytable.field2
> WHEN Col3 = Field1 THEN dbo.mytable.field3
> WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 2,
> CASE Col3
> WHEN Col1 = Field1 THEN dbo.mytable.field1
> WHEN Col2 = Field1 THEN dbo.mytable.field2
> WHEN Col3 = Field1 THEN dbo.mytable.field3
> WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 3,
> CASE Col4
> WHEN Col1 = Field1 THEN dbo.mytable.field1
> WHEN Col2 = Field1 THEN dbo.mytable.field2
> WHEN Col3 = Field1 THEN dbo.mytable.field3
> WHEN Col4 = Field1 THEN dbo.mytable.field4 END as Column 4
> FROM dbo.mytable
>
> This applies the same field selection to each dataset field, but looks at a
> different parameter to actually determine WHAT field should be displayed.
> Then in your report you set the table up and column 1 is always
> =Fields!Column1.Value. In summary, your rdl (table) remains static, and
> your SQL is dynamic.
> Hope this helps.
> Michael C.
>
> "Alain Quesnel" wrote:
> > You could probably do that with dynamic SQL within a stored proc. Then use
> > the the stored proc as your datasource.
> >
> > --
> >
> > Alain Quesnel
> > alainsansspam@.logiquel.com
> >
> > www.logiquel.com
> >
> >
> > "BrendanMC" <BrendanMC@.discussions.microsoft.com> wrote in message
> > news:3BFA8CB6-A3F4-4988-A2F3-FDE3D5DED831@.microsoft.com...
> > > We're trying to put together a pretty advanced implementation of SQL
> > > Reporting. We are trying to devise a way to allow the end user to
> > > dynamically order the columns.
> > >
> > > For a variety of reasons we cannot use/deploy the client tool set. Our
> > > data
> > > won't fit nicely into models.
> > >
> > > So, what we were thinking of is have the calling app send in some string
> > > looking like "1,4,2,3" as a param and have the report know to put col1
> > > first,
> > > col4 second and so on.
> > >
> > > Does anyone know, or can anyone think creatively, of a way to do this
> > > without getting into the RDL and effectively re-writing it?
> > >
> > > Thanks for any/all feedback and ideas.
> > >
> >
> >