Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts

Friday, March 30, 2012

Layout of report parameters

Hi all

Is there any way to change the way the report parameters are displayed?

Presently they are just in a tabular format. I am after displaying them as follows:

- - -

__

- -

The horizontal line is there to group the parameters. Is that possible?

Thanks heaps
This is not natively supported. You would need to construct your own prompting area.

Layout for Parameters in Menu Bar

I want to change the layout for the parameters in my menu bar.
I understand that with SP1, I can use style sheets.
1.
I want to group three on top and then but a horizontal bar <hr>
and then I want to put four in the middle - and agin a <hr>
and then three at the bottom.
2.
Also - can I convert the drop down menu to a 'scrolling text box' solme how?
I attempted to edit the MyStyle... but with limited luck.
Any suggestion is appreciated.The customizations that SP1 adds will not let you make these changes. You
can, however, implement your own parameters area and hide the default one.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407211153.48c4e7e0@.posting.google.com...
> I want to change the layout for the parameters in my menu bar.
> I understand that with SP1, I can use style sheets.
> 1.
> I want to group three on top and then but a horizontal bar <hr>
> and then I want to put four in the middle - and agin a <hr>
> and then three at the bottom.
> 2.
> Also - can I convert the drop down menu to a 'scrolling text box' solme
how?
> I attempted to edit the MyStyle... but with limited luck.
> Any suggestion is appreciated.|||Implementing my own parameter area -
- Would SOAP be the preferred way to do that? (Besides URL calls)
- Can I use "Infopath" for making such SOAP calls and redisigning the
parameter forms?
Thxsql

Friday, February 24, 2012

Large String Parameters Causing Performance Issues

Hi All,
Bit of strange one this. I have a key field in one of my databases that is a
Char(18). I use this as a parameter field in one of my reports.
When I run the report and specify the key field parameter manually the
report runs instantly. If I attempt to set this as a parameter variable and
enter the criteria in the parameter box, the report takes over a minute to
run. The 2 statements are as follows.
1) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = '085505010500074995'
When run as above, the report runs instantly.
2) select sum(sales_value) as sales from production.dbo.epos_transactions
where transaction_ref = @.TREF
When I enter the parameter and run the report it takes over a minute to run.
Running the query through query analyser causes no issues on any of the
servers involved and the response time on all servers is instant.
I'd be grateful if anyone can shed any light on this.
Many thanks for any help.
--
Nick Colebourn
DBA
United Coop LtdHi Nick,
Could you supply a generic repro for this issue. An RDL for Northwind or a
simple DB. I would love to take a look at it.
| Thread-Topic: Large String Parameters Causing Performance Issues
| thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==| X-WBNR-Posting-Host: 213.146.135.115
| From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
<NickColebourn@.discussions.microsoft.com>
| Subject: Large String Parameters Causing Performance Issues
| Date: Tue, 25 Jan 2005 03:53:03 -0800
| Lines: 29
| Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi All,
| Bit of strange one this. I have a key field in one of my databases that
is a
| Char(18). I use this as a parameter field in one of my reports.
| When I run the report and specify the key field parameter manually the
| report runs instantly. If I attempt to set this as a parameter variable
and
| enter the criteria in the parameter box, the report takes over a minute
to
| run. The 2 statements are as follows.
|
| 1) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = '085505010500074995'
|
| When run as above, the report runs instantly.
|
| 2) select sum(sales_value) as sales from production.dbo.epos_transactions
| where transaction_ref = @.TREF
|
| When I enter the parameter and run the report it takes over a minute to
run.
|
| Running the query through query analyser causes no issues on any of the
| servers involved and the response time on all servers is instant.
|
| I'd be grateful if anyone can shed any light on this.
|
| Many thanks for any help.
|
| --
| Nick Colebourn
| DBA
| United Coop Ltd
||||Hi Brad,
Many thanks for your reply. I'll see if I can get it to do the same
with a northwind query and post back asap. For your info, I've created an
in-line table valued function containing the sql with the parameter and
called this as the dataset instead of the sql text itself. This works ok
either with static criteria or using a parameter passed across to the
function. The performance issue only seems to happen when you pass the
parameter value directly across to the sql text.
Many thanks
Nick
""Brad Syputa - MS"" wrote:
> Hi Nick,
> Could you supply a generic repro for this issue. An RDL for Northwind or a
> simple DB. I would love to take a look at it.
>
> --
> | Thread-Topic: Large String Parameters Causing Performance Issues
> | thread-index: AcUC1GzdZAw/tJ+RRciPnzP3TypLNA==> | X-WBNR-Posting-Host: 213.146.135.115
> | From: "=?Utf-8?B?TmljayBDb2xlYm91cm4=?="
> <NickColebourn@.discussions.microsoft.com>
> | Subject: Large String Parameters Causing Performance Issues
> | Date: Tue, 25 Jan 2005 03:53:03 -0800
> | Lines: 29
> | Message-ID: <EFE8F146-E9CC-49C8-A5B8-4E29E3F57EC2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40737
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi All,
> | Bit of strange one this. I have a key field in one of my databases that
> is a
> | Char(18). I use this as a parameter field in one of my reports.
> | When I run the report and specify the key field parameter manually the
> | report runs instantly. If I attempt to set this as a parameter variable
> and
> | enter the criteria in the parameter box, the report takes over a minute
> to
> | run. The 2 statements are as follows.
> |
> | 1) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = '085505010500074995'
> |
> | When run as above, the report runs instantly.
> |
> | 2) select sum(sales_value) as sales from production.dbo.epos_transactions
> | where transaction_ref = @.TREF
> |
> | When I enter the parameter and run the report it takes over a minute to
> run.
> |
> | Running the query through query analyser causes no issues on any of the
> | servers involved and the response time on all servers is instant.
> |
> | I'd be grateful if anyone can shed any light on this.
> |
> | Many thanks for any help.
> |
> | --
> | Nick Colebourn
> | DBA
> | United Coop Ltd
> |
>

Large selection in multiple parameters

I have a problem and was wondering if someone might have some good
suggestions. I've created a report that shows the number of loans that
have been given to students at various colleges by various vendors.
The report has parameters for vendor and for college, and I created the
parameters as multi-value drop down boxes. The source for the schools
box is a query against my data table for the distinct Schools that
appear.
The problem I'm having is this: There are approximately 4000 schools
in the table, and when the user does a Select All on the web site, the
report chugs away for a while and then returns nothing. It works fine
in Visual Studio (slowly, but it returns everything) but not on the
website. I don't get any error messages.
The WHERE clause in my main script is simply:
Where SchoolName in (@.schoolName)
My guess is that there are simply too much being shoved into the
parameter when I try to select all. Just wondering if anyone has a
reasonable way to get around this. I've managed a rather kludgy method
where I have an additional boolean parameter and then logic to bypass
the WHERE if it's true, but I'd rather just have the thing work
normally (via the drop-down) if that's possible.
The drop down for the Vendors works just fine, whether I select one,
many, or all of the vendors. But there are only 30 of those, so the
cause seems to be the number of choices. Is there a set limit to the
number of choices in a drop down, or a set size that can be passed
perhaps?
Using Reporting Services 2005.How about doing a bit of processing before the query gets run. Something
along the lines of
IIF parameter!School.Item(0) = true, select data regardless of school (no
whereclause on Schoolname) else select data with whereclause.
I'm not quite sure how to check if the Select All parameter was selected or
not, but it might be food for thought anyway.
Kaisa M. Lindahl Lervik
<cphite@.gmail.com> wrote in message
news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
>I have a problem and was wondering if someone might have some good
> suggestions. I've created a report that shows the number of loans that
> have been given to students at various colleges by various vendors.
> The report has parameters for vendor and for college, and I created the
> parameters as multi-value drop down boxes. The source for the schools
> box is a query against my data table for the distinct Schools that
> appear.
> The problem I'm having is this: There are approximately 4000 schools
> in the table, and when the user does a Select All on the web site, the
> report chugs away for a while and then returns nothing. It works fine
> in Visual Studio (slowly, but it returns everything) but not on the
> website. I don't get any error messages.
> The WHERE clause in my main script is simply:
> Where SchoolName in (@.schoolName)
> My guess is that there are simply too much being shoved into the
> parameter when I try to select all. Just wondering if anyone has a
> reasonable way to get around this. I've managed a rather kludgy method
> where I have an additional boolean parameter and then logic to bypass
> the WHERE if it's true, but I'd rather just have the thing work
> normally (via the drop-down) if that's possible.
> The drop down for the Vendors works just fine, whether I select one,
> many, or all of the vendors. But there are only 30 of those, so the
> cause seems to be the number of choices. Is there a set limit to the
> number of choices in a drop down, or a set size that can be passed
> perhaps?
> Using Reporting Services 2005.
>|||What I have done in the case is as follows. I found this function
CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=',')
RETURNS @.VALUES TABLE (Param nvarchar(max))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(max)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
This will break the comma separated string passed into your stored proc into
a table. Thow the results of this function into a temp table, and filter you
results with a join, instead of an in.
Ken
"Kaisa M. Lindahl Lervik" wrote:
> How about doing a bit of processing before the query gets run. Something
> along the lines of
> IIF parameter!School.Item(0) = true, select data regardless of school (no
> whereclause on Schoolname) else select data with whereclause.
> I'm not quite sure how to check if the Select All parameter was selected or
> not, but it might be food for thought anyway.
> Kaisa M. Lindahl Lervik
> <cphite@.gmail.com> wrote in message
> news:1162419349.183710.148290@.h48g2000cwc.googlegroups.com...
> >I have a problem and was wondering if someone might have some good
> > suggestions. I've created a report that shows the number of loans that
> > have been given to students at various colleges by various vendors.
> > The report has parameters for vendor and for college, and I created the
> > parameters as multi-value drop down boxes. The source for the schools
> > box is a query against my data table for the distinct Schools that
> > appear.
> >
> > The problem I'm having is this: There are approximately 4000 schools
> > in the table, and when the user does a Select All on the web site, the
> > report chugs away for a while and then returns nothing. It works fine
> > in Visual Studio (slowly, but it returns everything) but not on the
> > website. I don't get any error messages.
> >
> > The WHERE clause in my main script is simply:
> > Where SchoolName in (@.schoolName)
> >
> > My guess is that there are simply too much being shoved into the
> > parameter when I try to select all. Just wondering if anyone has a
> > reasonable way to get around this. I've managed a rather kludgy method
> > where I have an additional boolean parameter and then logic to bypass
> > the WHERE if it's true, but I'd rather just have the thing work
> > normally (via the drop-down) if that's possible.
> >
> > The drop down for the Vendors works just fine, whether I select one,
> > many, or all of the vendors. But there are only 30 of those, so the
> > cause seems to be the number of choices. Is there a set limit to the
> > number of choices in a drop down, or a set size that can be passed
> > perhaps?
> >
> > Using Reporting Services 2005.
> >
>
>|||Ken Reitmeyer wrote:
> What I have done in the case is as follows. I found this function
> CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> ',')
> RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> BEGIN
> DECLARE @.chrind INT
> DECLARE @.Piece nvarchar(max)
> SELECT @.chrind = 1
> WHILE @.chrind > 0
> BEGIN
> SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> IF @.chrind > 0
> SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> ELSE
> SELECT @.Piece = @.RepParam
> INSERT @.VALUES(Param) VALUES(@.Piece)
> SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> IF LEN(@.RepParam) = 0 BREAK
> END
> RETURN
> END
> This will break the comma separated string passed into your stored proc into
> a table. Thow the results of this function into a temp table, and filter you
> results with a join, instead of an in.
Ken,
When I use this function it works for one selection, but when I select
more than one school it tells me I have too many parameters.|||This is how I am using the function to pass a long parameter list
--temp table to hold parameters
declare @.tbl_WorkCenters table
(
work_center int
)
declare @.sql varchar(max)
--@.work_centers is the comma separated parameter list passed in from the
reprot
set @.sql = 'select ltrim(param) from fn_MVParam(''' + @.work_centers + ''',
'','')'
insert into @.tbl_WorkCenters
exec (@.sql)
Not sure if that answers your question or not.
"cphite@.gmail.com" wrote:
> Ken Reitmeyer wrote:
> > What I have done in the case is as follows. I found this function
> >
> > CREATE FUNCTION [dbo].[fn_MVParam](@.RepParam nvarchar(max), @.Delim char(1)=> > ',')
> > RETURNS @.VALUES TABLE (Param nvarchar(max))AS
> > BEGIN
> > DECLARE @.chrind INT
> > DECLARE @.Piece nvarchar(max)
> > SELECT @.chrind = 1
> > WHILE @.chrind > 0
> > BEGIN
> > SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
> > IF @.chrind > 0
> > SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
> > ELSE
> > SELECT @.Piece = @.RepParam
> > INSERT @.VALUES(Param) VALUES(@.Piece)
> > SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
> > IF LEN(@.RepParam) = 0 BREAK
> > END
> > RETURN
> > END
> >
> > This will break the comma separated string passed into your stored proc into
> > a table. Thow the results of this function into a temp table, and filter you
> > results with a join, instead of an in.
> Ken,
> When I use this function it works for one selection, but when I select
> more than one school it tells me I have too many parameters.
>

Large Parameter Sring

I am using a string for the querystring of a report, and I am passing in parameters to substitute values. However, one of the parameters is over 1200 characters long and when I run the report from the designer it executes, but when I run it from report manager it doesn't run the report, and I don't receive any errors, and nothing shows up in the error logs. Please let me know if there are some kind of limitations on the amount of data a parameter can pass in a string data typeYou are probably hitting this URL limitation:
http://support.microsoft.com/support/kb/articles/Q208/4/27.ASP
Currently, there is no workarond.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"pietrzbm" <pietrzbm@.discussions.microsoft.com> wrote in message
news:62AB9382-CCD8-466C-A36A-71462D174638@.microsoft.com...
> I am using a string for the querystring of a report, and I am passing in
parameters to substitute values. However, one of the parameters is over
1200 characters long and when I run the report from the designer it
executes, but when I run it from report manager it doesn't run the report,
and I don't receive any errors, and nothing shows up in the error logs.
Please let me know if there are some kind of limitations on the amount of
data a parameter can pass in a string data type