| Groups | Blog | Home |
sql server reporting services : Dynamic Column Ordering
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 [quoted text, click to view] "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. [quoted text, click to view] "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. [quoted text, click to view] "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. > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |