Can we implement Pivot Tables using MSRS?

Can we implement Pivot Tables using Reporting Services 2005? I need to
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>

can view be dynamically filtered like an MSAccess query?

In MSAccess you can dynamically filter a query by adding square brackets [ ]
to the criteria section and a prompt inside the brackets [enter filter code]
.
Then you can open the query directly and be prompted for a filter. I tried
this in the view without any success.
I was approached today by an End user who uses an Access ADP to interface
with our sql server. The user needs to pull/look at data from a large
table, and currently there is no mechanism for filtering the respective tabl
e
in the Access ADP. Since several users are using the same ADP, I don't want
to modify it. I was thinking of creating a view that could be opened from
the ADP. But rather than having the user call me everytime he needs to
change the criteria, is there a way to make the view dynamic?
Thanks,
RichRich,
You can use an inline table-valued function. You can not pass parameters to
a view.
Inline User-Defined Functions
http://msdn.microsoft.com/library/d...>
_08_73lf.asp
AMB
"Rich" wrote:

> In MSAccess you can dynamically filter a query by adding square brackets [
]
> to the criteria section and a prompt inside the brackets [enter filter cod
e].
> Then you can open the query directly and be prompted for a filter. I tri
ed
> this in the view without any success.
> I was approached today by an End user who uses an Access ADP to interface
> with our sql server. The user needs to pull/look at data from a large
> table, and currently there is no mechanism for filtering the respective ta
ble
> in the Access ADP. Since several users are using the same ADP, I don't wa
nt
> to modify it. I was thinking of creating a view that could be opened from
> the ADP. But rather than having the user call me everytime he needs to
> change the criteria, is there a way to make the view dynamic?
> Thanks,
> Rich|||Thanks - very . I will give this a shot. The only thing is that - afte
r
reading the article - it looks like the user needs to write a sql statement.
If the user is using an Access ADP - where does he write the statement to us
e
the inline function? In the article it looked like the user had QA on his
desktop.
"Alejandro Mesa" wrote:
> Rich,
> You can use an inline table-valued function. You can not pass parameters t
o
> a view.
> Inline User-Defined Functions
> http://msdn.microsoft.com/library/d...
es_08_73lf.asp
>
> AMB
> "Rich" wrote:
>|||I figured it out. The function will show up in the user interface. The use
r
can invoke the function the same way as a query in MSAccess and will be
prompted for input. Very . Thanks again for the suggestion.
"Rich" wrote:
> Thanks - very . I will give this a shot. The only thing is that - af
ter
> reading the article - it looks like the user needs to write a sql statemen
t.
> If the user is using an Access ADP - where does he write the statement to
use
> the inline function? In the article it looked like the user had QA on his
> desktop.
> "Alejandro Mesa" wrote:
>

Can the usage of a Cursor dynamically generate a query statement?

Hi,
As far as my understanding goes, cursors can be used to fill in variables as
it iterates through a pre-defined hardcoded query.
Would like to know if I can make a dynamic query within a cursor and
subsequently
pump values obtained from this dynamic query into their respective variables
.
Appreciate any form of reply! Thanks!
An extract of the code (it doesn't run):
DECLARE Scursor CURSOR FOR
SELECT isnull(@.account_colname, '')
, isnull(@.trans_date_colname, '')
, isnull(@.trans_time_colname, '')
, isnull(@.trans_type_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.amount_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.seq_colname, '')
-- ,ROWIDTOCHAR(rowid) --ROWID
FROM isnull(@.source_tabname, '')
WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
AND isnull(@.contra_colname, '') IS NULL
OPEN ScursorHi Arthur,
The dynamic queries will be executed in a seperate memory space so u will
not be able to access any of the local variables. This holds good for your
cursors as well. u ll not be able to call them within your D-SQL.
rgds,
anu
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>|||Why cursors? Why dynamic SQL? Both are things that you should try to
avoid.
If you need more help, please specify your actual problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||The answer is yes, and the question is why do you want to do this?. Try
finding a set based solution to the problem and leave cursors as the last
resource in your pocket.
Example:
use northwind
go
declare @.my_cursor cursor
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
declare @.order_id int
declare @.order_date datetime
set @.sd = '19960101'
set @.ed = '19970101'
set @.sql = N'set @.c = cursor local fast_forward for select orderid,
orderdate from dbo.orders where orderdate >= '''
set @.sql = @.sql + convert(char(8), @.sd, 112) + N''' and orderdate < ''' +
convert(char(8), @.ed, 112) + N''''
set @.sql = @.sql + N'; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c = @.my_cursor output
if cursor_status('variable', '@.my_cursor') = 1
begin
while 1 = 1
begin
fetch next from @.my_cursor into @.order_id, @.order_date
if @.@.error <> 0 or @.@.fetch_status <> 0 break
select @.order_id, @.order_date
end
end
close @.my_cursor
deallocate @.my_cursor
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server wou
ld use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the
pointer in that index
to go fetch the data row, in descending order. The lower selectivity you hav
e, the less of a perf
gain you see by using the index, as a data page has to be visited *for each
row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) a
nd then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even th
e FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.c
om...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.
Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegr oups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>

Can Report Parameter Prompt be Dynamically changed?

I am attempting to write a multilingual report and would like to have
the Prompt property of a Report Parameter dynamically change. Is this
possible? Support for something like an expression would be ideal,
but I cannot seem to find where prompt supports such a concept.
Thanks,
DaveOn Dec 6, 3:59 pm, david.gab...@.swagelok.com wrote:
> I am attempting to write a multilingual report and would like to have
> the Prompt property of a Report Parameter dynamically change. Is this
> possible? Support for something like an expression would be ideal,
> but I cannot seem to find where prompt supports such a concept.
> Thanks,
> Dave
As far as I know, this functionality does not exist. One work around
would be to modify the .rdl file programmatically between the <prompt>
tags based on the language (via StreamReader, StreamWriter, etc).
Also, you might try creating a custom ASP.NET website and allow the
user to select a language from a drop-down list and then create 2
reports: each one based on a different language. Then you could show
either report based on the language selected. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Can report content be generated dynamically?

SSRS 2005
I have figured out how to use a custom code assembly to dynamically control
the content of the textboxes in the footer of my report.
For example, I have an expression like this in one of the textboxes:
=Code.OakRptLib.BuildPageFooterLeft()
This approach assumes that the textboxes already exist in the report footer.
I am trying to build custom code assemblies that allow my reports to be
dynamically configured in a consistent, standard manner at run time.
Ideally when the report starts up I would like to dynamically create these
text boxes in the report footer so that I can make sure they all use the same
font settings, positioning, content, etc.
Is there a way that I can dynamically create these text boxes in the report
footer when the report first starts processing? For example with code in the
OnInit() method?
--
Chris, SSSIHello Chris,
Based on my research, you could not dynamically create a report item in the
code.
The only thing you may do is using a program to dynamically create a rdl
file.
Since the RDL file is a XML format, you could use the .NET program to
generate a RDL file.
Hope this will be some help for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Wei!
Did Steven Cheng have any ideas about this?
-- Chris
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Based on my research, you could not dynamically create a report item in the
> code.
> The only thing you may do is using a program to dynamically create a rdl
> file.
> Since the RDL file is a XML format, you could use the .NET program to
> generate a RDL file.
> Hope this will be some help for you.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
I have discussed with Steven, and he also confirmed this.
I suggest you may try some suggestion from Chris Conner in other post: "Can
I obtain a reference to a report item?"
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.