Dynamically changing reports at runtime

Hi - I'm trying to decide among reporting options without spending too much
time going down the wrong path, so I hope someone can answer my questions
about Reporting Services capabilities...
With Reporting Services, can I dynamically (at runtime via code):
1) add or remove subreports from a report?
2) make a subreport visible or hidden?
3) add or delete columns?
4) make columns visible or hidden?
5) add or delete tables?
6) make a table visible or hidden?
Is doing any of these things fairly straightforward?
Thanks so much in advance,
SherylYes, you can use an expression for the Hidden property of respective report
elements to dynamically show/hide them at runtime.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sheryl Landon" <shland@.comcast.net> wrote in message
news:OoDKy5$pEHA.3244@.tk2msftngp13.phx.gbl...
> Hi - I'm trying to decide among reporting options without spending too
much
> time going down the wrong path, so I hope someone can answer my questions
> about Reporting Services capabilities...
> With Reporting Services, can I dynamically (at runtime via code):
> 1) add or remove subreports from a report?
> 2) make a subreport visible or hidden?
> 3) add or delete columns?
> 4) make columns visible or hidden?
> 5) add or delete tables?
> 6) make a table visible or hidden?
> Is doing any of these things fairly straightforward?
> Thanks so much in advance,
> Sheryl
>

Dynamic SQL to populate a variable

Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.

declare @.TableName sysname

set @.TableName = 'Customers'

delcare @.Output bigint

declare @.SQL varchar(max)

set @.SQL = 'select top 1 RowID from ' + @.TableName

select @.Output =

EXEC (@.SQL)

create function udf_GetDatabaseFingerPrint(

@.DBID bigint

)

begin

returns bigint

as

declare @.dbname sysname

, @.iBig bigint

, @.tSQL varchar(2000)

select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID

set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'

set @.iBig = exec(@.tSQL)

return @.iBig

end

Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had

declare @.objectId int,

exec sp_executeSQL

N'select @.objectId = max(object_id) from sys.objects',

N'@.objectId int output', @.objectId=@.objectId output

select @.objectId

Dynamic SQL Query Question

I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.

I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @.tblNode AND tblCorp = @.tblCorp AND tblService = @.tblService

The @.tblNode, @.tblCorp, @.tblService are all pulling from 3 separate drop down controls.

Ok easy enough.

However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.

Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?

I guess I am looking to see if a wild card can be used so tblNode = % or something.

The server is MSSQL

Thank you

You can use either of the 2 approaches mentioned in this article and its comments:http://www.emxsoftware.com/Optional+Parameters+in+SQL+Server+Search+Queries

|||

Thank you for your help, while this is what I need... would you happen to know how to set the value of the drop downs I am using = NULL?

I can pull now based on the criteria of the drop downs, but the default values should pull everything. That is really where I am stuck right now.

|||

You leave your Select All listItem in your DropDownList to empty like:

<asp:ListItemValue="">ALL</asp:ListItem>

Your SQl where clause:

Where tblNode =ISNULL(@.tblNode,tblNode) AND tblCorp=ISNULL(@.tblCorp, tblCorp) AND tblService= ISNULL(@.tblService, tblService)

and:

If you use a SqlDataSource, you need to set this property too:CancelSelectOnNullParameter="false"

|||

Thank you both, that actually did it and I am up and working.

I appreciate you both taking the time to explain it for me.

Dynamic Sql Intermittenetly Slow - Losing Hair Fast

Hi guys,
I've been staring at this stored proc for hours and I just can't see
what wrong with it. It will run fine 40+ times in a row and then one
time it it will take up-to 15 seconds to complete. Am baffled.
Anyway here's the query. It basically says, select all the hotel rates
where the option is in the OptionList parameter and the agentid = x and
the AgentPassword is equal to x. There is an added complication in that
I have to filter out any HotelRate that is zero for the selected room
types (single, double etc)
Any help appreciated!
Cheers, Pete (lad4bear)
CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
@.AgentId nvarchar(50),
@.AgentPassword nvarchar(50),
@.OptionList nvarchar(3900),
@.Single int,
@.Double int,
@.Twin int,
@.Triple int
AS
BEGIN
DECLARE @.dynamicSql nvarchar(4000)
SET @.dynamicSql =
'SELECT *
FROM
HotelsOptionRates
WHERE
[HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
IF (@.Single > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
<> 0'
IF (@.Double > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
<> 0'
IF (@.Twin > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
<> 0'
IF (@.Triple > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
<> 0'
EXECUTE(@.dynamicSql)
END
GO<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
>
What is the execution plan when it takes 15 seconds to complete?
David|||I'm pretty sure that you can write the query without using dynamic SQL.
A lot of information at this site:
http://www.sommarskog.se/
Read "Dynamic Seach Conditions" first.
"Arrays and Lists in SQL server" will help you with this part:
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
> Anyway here's the query. It basically says, select all the hotel rates
> where the option is in the OptionList parameter and the agentid = x and
> the AgentPassword is equal to x. There is an added complication in that
> I have to filter out any HotelRate that is zero for the selected room
> types (single, double etc)
> Any help appreciated!
> Cheers, Pete (lad4bear)
>
> CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
> @.AgentId nvarchar(50),
> @.AgentPassword nvarchar(50),
> @.OptionList nvarchar(3900),
> @.Single int,
> @.Double int,
> @.Twin int,
> @.Triple int
> AS
> BEGIN
> DECLARE @.dynamicSql nvarchar(4000)
> SET @.dynamicSql =
> 'SELECT *
> FROM
> HotelsOptionRates
> WHERE
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
> AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
> AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
> IF (@.Single > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
> <> 0'
> IF (@.Double > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
> <> 0'
> IF (@.Twin > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
> <> 0'
> IF (@.Triple > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
> <> 0'
> EXECUTE(@.dynamicSql)
> END
> GO
>|||To test a hypothesis I replaced the dynamic sql with similar (although not
the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds to
complete.
Now currently I don't know if the stored proc itself is taking 15 seconds or
if the line of code I use to call the stored proc is taking 15 seconds. It's
one line of thoroughly tested code so I see no reason to that it should be
causing this problem.
As the database is remote I cannot use sql profiler to see how long the
stored proc is actually taking. Is there another way to get timing
information?
Thanks for your help
Pete (aka lad4bear)
"David Browne" wrote:

> <lad4bear@.gmail.com> wrote in message
> news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> What is the execution plan when it takes 15 seconds to complete?
> David
>
>|||Figured it out. I forgot I had scheduled batch update set to run every 3
minutes which was updating the table. Looks like I was running into some
locking issues. Thanks for the advice and the links
Cheers Pete (aka lad4bear)
"lad4bear" wrote:
> To test a hypothesis I replaced the dynamic sql with similar (although not
> the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds
to
> complete.
> Now currently I don't know if the stored proc itself is taking 15 seconds
or
> if the line of code I use to call the stored proc is taking 15 seconds. It
's
> one line of thoroughly tested code so I see no reason to that it should be
> causing this problem.
> As the database is remote I cannot use sql profiler to see how long the
> stored proc is actually taking. Is there another way to get timing
> information?
> Thanks for your help
> Pete (aka lad4bear)
>
> "David Browne" wrote:
>