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.
> > >
> >
> >

Dynamic calculation

Hi,
I get different values and a calculation from a query and need to bring them
all together to make another calculation.
@.PRICE,@.TIME,@.CALC,@.TOTAL
@.PRICE = 3
@.TIME = 5
@.CALC = '* .5'
@.TOTAL = (@.PRICE @.CALC) * @.TIME
I tried to use the exec command, but couldn't get it to work
How can I do this using Transact SQL?
Thank you,
MosheProbably you can make use of CASE expressions to do this, assuming you
know something about the types of optional calculations. Example:
SET @.total =
CASE @.calc_type
WHEN 1 THEN @.price*@.time*0.5
WHEN 2 THEN @.foo*@.bar*0.25
WHEN ... etc
END
If you think you will be forced to use dynamic code then there should
be no reason why you can't do it with EXEC. That's not necessarily the
approach I would recommend but maybe if you post some actual code
rather than pseudo code we could help you fix it.
David Portas
SQL Server MVP
--|||Seems to me you're trying to use dynamic SQL - don't quite know why, since
what you need can be done much more efficiently without dynamic SQL, but
still...
Read more here:
http://www.sommarskog.se/dynamic_sql.html
For a more efficient solution, please provide more information.
ML|||SET @.TOTAL = (@.PRICE @.CALC) * @.TIME
HTH, jens Suessmeyer.|||Hi Moshe,
I've done something very similar with a financial research app i wrote for a
client, they specify a couple of hundred dynamic formula that i then need to
calculate on the fly.
Basically you can use sp_executesql and get the output...
declare @.nsql nvarchar(4000)
set and declare... @.PRICE,@.TIME,@.CALC,@.TOTAL
SET @.PRICE = 3
SET @.TIME = 5
SET @.CALC = '* .5'
SET @.nsql = '@.TOTAL = (@.PRICE ' + @.CALC + ') * @.TIME'
EXEC sp_executeSQL @.nsql,
N'@.PRICE int, @.TIME int, @.TOTAL
decimal( 10, 2 ) OUTPUT',
@.PRICE, @.TIME, @.TOTAL OUTPUT
PRINT @.TOTAL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Moshe Allen" <mosheallen@.hotmail.com> wrote in message
news:dmerbr$hba$1@.news2.netvision.net.il...
> Hi,
> I get different values and a calculation from a query and need to bring
> them all together to make another calculation.
> @.PRICE,@.TIME,@.CALC,@.TOTAL
> @.PRICE = 3
> @.TIME = 5
> @.CALC = '* .5'
> @.TOTAL = (@.PRICE @.CALC) * @.TIME
> I tried to use the exec command, but couldn't get it to work
> How can I do this using Transact SQL?
> Thank you,
> Moshe
>

DW partitioning advice needed by lost soul

We are putting our first DW together. One of the consultants, not familiar with the MS SQL database, suggested we consider "parititioning" our data for ease or reloading, distaster recovery etc. His idea was that we could parition the data based on, say, year, and that way, in case of a disaster, we would only need to reload that year's worth of data from our backups. (So I guess he is talking about software partitioning, not hardware partitioning?)

I can't imagine how this approach wll work with our nightly backup strategy or the changes the need to be made to the DW import code to to handle these partitons. Furthermore, in case of a disaster how is one to even know what parititon(s) to reload. (I'd be incliined to reload the entire warehouse to make sure I got everything!). I'm lost. Any thoughts you can share on this headache will be much appreciated!

barkingdog

Not sure if you are asking question on the right place. This forum is specifict to issues related to Analysis Services only.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My mistake -- I will contiue to look for the the DataWarehousing newsgroup!

Barkingdog