Parameters && Report Performance ..
Jan 28, 2008
Hi all,
I have a report which uses two different datasets. The first one is used to get the required data and the second one is to get the customer name to be used in a parameter.
I had a major performance issue with the report as the first dataset is very complex. And after I did a lot of SQL tuning to the first dataset, it is now working fine with an acceptable performance.
The problem now is when I tried to add a condition in this dataset to be used by the parameter like
WHERE CUSTOMER_NAME IN (@CustName )
The performance of the report is getting very bad again and the report can take more than 15 min. to get the result.
The second dataset is a simple query that is used to get the customer name from the customer table so I don€™t think it is the problem.
Any ideas? Do you think it has anything to do with the SSRS parameters.
Thanks all.
View 1 Replies
ADVERTISEMENT
Jan 7, 2008
I'm using sql2000. VB2003.
My report uses the following parameters
WHERE (@Unit='' or a.unit = @Unit) and (@WO='' or a.wo = @WO)
It takes about 23 seconds to run if I type in 71173 as Unit leaving WO blank.
If I edit the SQL to be Where a.unit=71173 it execustes in 1 second.
Where am I going wrong?
I read some of the threads but I couldn't get a clear idea if this is normal or not.
Card Gunner
View 3 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
May 3, 2007
How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies
View Related
Apr 9, 2008
Hi,
I have a user defined function which has 5 parametes to pass, now while running the Table Valued Function using hardcoded parametes value its taking only 10 seconds , but while running the UDF by declaring the parametes value and
assigned them value its taking more than 3 minutes.
here is the example
First case
select * from UDF_Test('A',76,'C',987,''X')
This one taking only 10 minutes to run.
Second case
Declare @P1 varchar,
@P2 Int,
@P3 varchar,
@P4 int,
@P5 varchar
SET @P1 = 'A'
@P2= 76
@P3='C'
@P4 = 987
@P5='X'
Select * from TVF_Test(@P1,@P2,@P3,@P4,@P5) as test
where test.column1=@P1
Any help will be appreciated.
Thanks
View 6 Replies
View Related
Mar 22, 2007
Dear all
Can any one provide me details of the parameters to be monitored in sql server 2005
Currently i am monitoring
SQLServer : Buffer Manager : Buffer Cache Hit Ratio
SQLServer : Databases : Log Flushes / Sec
SQLServer : Databases: Transactions / Sec
SQLServer : Access Methods : Page Splits / Sec.....
1) Please provide if any other relevant parameters to be monitored by perfmon.
2) Which are the most essential parameters to be monitored.
3) Is there any query or trace that will provide me performance details in SQL.
4) Can i have a query that get me the most resource consuming process or query in sql server.
5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.
It would be very helpful if you guys could provide me help on these.
Thanks in advance
View 3 Replies
View Related
Apr 30, 2007
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
View 1 Replies
View Related
Nov 18, 2007
Here is a situation in my company:
We have an Office SharePoint 2007 site, we developed a couple of web parts and added them to our site. We used SQL Server Reporting services 2005 as our reporting solution. The reports are hosted on the report center and when we need a report, we open it by sending a URL requesting the report, passing the report parameters in the URL query string. So the report is filtered based on the parameters passed from the web parts.
But since best practices say that you should host reports directly under SharePoint, by configuring the reporting services to run in the SharePoint integrated mode. We followed the steps and installed SharePointRS and we finally succeeded to publish the reports to a SharePoint folder, but we had a limitation: we are unable to pass the report parameters internally to the report hosted in SharePoint. If we passed them in the query string as the report center case, SharePoint neglects them totally.
So the question is: how can I pass parameters internally between a SharePoint web part and a SQL server 2005 reporting services report hosted in SharePoint?
View 1 Replies
View Related
Sep 16, 2015
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
columns are **Level**  and        **Salesamount**. Â
Values are like Â
**[-]category** Â Â Â Â Â Â Â **100**
  **[-]subcat**       **50**
     **product**      **30**
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
Here i used the expressions in parameter values
**pCategory**----iif(inscope(category),!fields.category.value,split(join(Parameters!category.value,","),","))
**psubcat**----iif(inscope(subcat),!fields.subcat.value,split(join(Parameters!subcat.value,","),","))
**pproduct**----iif(inscope(product),!fields.product.value,split(join(Parameters!product.value,","),","))
View 3 Replies
View Related
Feb 8, 2015
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
View 3 Replies
View Related
Jul 17, 2006
I have run into a very odd issue with the performance of one of our stored procedures. The SP seems to perform very poorly if I embed the parameters directly into the query. If I declare local variables to hold the parameter values, the SP runs a lot faster.
This stored procedure takes about 26 seconds:
CREATE PROCEDURE dbo.jk_ReportData_Get
@startDate datetime, @endDate datetime, @entityID int, @reportID int
AS
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
When I change it to the following, it takes less than one second:
CREATE PROCEDURE dbo.jk_ReportData_Get
@temp1 smalldatetime, @temp2 smalldatetime, @temp3 int, @temp4 intAS
declare @startDate datetimeset @startDate = @temp1declare @endDate datetimeset @endDate = @temp2declare @entityID intset @entityID = @temp3 declare @reportID intset @reportID = @temp4
select sum(amount_mtd) Amount, account_id, entity_id from data where account_id in (select paramvalue from cellparameter where cell_id in (select cell_id from report_reportsection rrs inner join reportsection rs on rrs.reportsection_id = rs.id inner join reportsection_cell rsc on rs.id = rsc.reportsection_id where report_id = @reportID)) and thedate between @startDate and @endDate and data.entity_id = @entityID group by account_id, entity_id GO
Can anyone explain this please??? :) Notice it is the same query in both cases. The only difference is in the parameters...
View 21 Replies
View Related
Nov 22, 2007
Hi all.
Context: SQL 2000, JDBC 1.2
I have a query with 9 parameters that run as follows (approximate timing):
1. Using prepared statement: 1,7 sec
2. Re-using the same statement: 1,7 sec
3. Using prepared statement, no parameters (the params are hardcoded): 20 ms
4. Using statement+resultset+rs.getFirst: 20 ms
5. from inside SQL analyzer: instantly
So, I observe a problem when using parameters. Do I miss something, do I have to do something?
If the actual query may help, I will post it promptly.
Thx in advance
Denis
View 5 Replies
View Related
Mar 28, 2008
Hi,
I am having hard time in sending parameters to the report server through reportviewer control from my application. Could anybody help me on this issue?. Is there any way to send the parameters and also what kind of configuration do i need on my report project so that it can accept parameters from my application.
Thanks,
View 9 Replies
View Related
Nov 28, 2007
Good morning, all
I have a really cool Budget report which has two matrices on it. One is used/visible if the user selects four levels of grouping, and one is used if the user selects only three levels of grouping. The four choices for grouping are four fields, Par, FERC, Point, and Resource.
Since grouping is optional, I need a way to pass the parameter for a field that is not grouped on from the main report to the drillthrough report. For example if the user chooses to group on all fields but Point, I need to pass the report parameter that was used by the main report for Point to the drillthrough report as a report parameter.
So, I tried testing the three grouping levels values and if none were equal to "Point" then pass in the parameter by putting the following expression in the Parameter Value box for the Point parameter on the Navigation tab of the datacell of the main report that is the drillthrough link:
=iif(
Parameters!GroupLevel1.Value <> "Point"
and
Parameters!GroupLevel2.Value<>"Point"
and
Parameters!GroupLevel3.Value<>"Point"
,
Join(Parameters!PNTPoint.Value,",")
,
Fields!Point.UniqueName
)
But this is not working; the drillthrough report shows a blank dropdown for the Point parameter and I have to manually set it. Does anyone see an error in the syntax of the iif statement?
Any references, links, resources or good clean jokes would be greatly appreciated.
Thanks,
Kathryn
View 1 Replies
View Related
Oct 22, 2007
Dear All,
Is it possible to poplulate some control like Dropdownlist (for example, with Product names Or Product Ids) from database table in SSRS project type and then filter report by choosing various choice pre-populated in dropdownlist control.
Project Type is : SQL Server Reportings 2005.
Desired Action: Report will run in browser, User will choose specific product and Run the report(filter) to show that product relate results.
any help,
Thanks,
View 4 Replies
View Related
Feb 7, 2007
Is it possible to add custom controls in for parameters in report manager??
I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services??
I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.
I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.
Does anyone know if customising the report manager will become available in a later version??
View 1 Replies
View Related
Jan 15, 2007
Greetings,
I have two reports, Report A and Report B.
Report A has two parameters defined as "Prompted", meaning the report input area is available for the user.
Report A parameters ---> StartDate, EndDate
When Report B invokes Report A using the Jump To Report Functionality and passing Report B's StartDate and EndDate parameters, Report A seems to think the parameters are implied and not needed and fails to drop down the parameter input area.
This is not desired because the user should be able to change report A's parameters.
Anyone know a workaround?
View 1 Replies
View Related
Oct 15, 2007
hi there
I am using Reporting Services 2005.
I have 3 report parameters: startdate, enddate, callstatus
My problem is that once I added callstatus to the parameter list my dates were ignored?
What do I need to do to correct it?
My select code is as below:
SELECT Ticket.TicketNumber, Application.ApplicationName, Status.StatusName, Ticket.LastModifiedBy, CONVERT(datetime, Ticket.CreatedDate),
Company.CompanyName
FROM Ticket INNER JOIN
Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Status ON Ticket.StatusID = Status.StatusID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID
WHERE (CONVERT(datetime, Ticket.CreatedDate) >= @StartDate) AND (CONVERT(datetime, Ticket.CreatedDate) <= @EndDate) AND
(Status.StatusName IN (@CallStatus)) OR
('SELECT ALL' IN (@CallStatus))
My report Parameters have the values listed in the Non-queried part on report parameters
thanks
Dianne
View 3 Replies
View Related
Sep 19, 2007
Why is it that EVERY TIME you make any change at all to a data set or parameter, RS automatically changes your parameters from single value to Multi-value????
This is VERY annoying.
View 3 Replies
View Related
Jun 7, 2007
Is there a way to force report designer overlook parameter dependencies, so that a report could be displayed by either of the two parameters although one parameter accepts but not requires the other?
For Example:
In my report I have two parameters, DeptID and ProjectID. The two drop downs display Departments and Projects respectively but only if the department is selected, the Projects list becomes active as its expecting a DepartmentID in the stored proc but accepts a null value as well. So, what I would like to achieve is display ALL projects in the Projects list, even if no department is selected. Any ideas how to do that?
Thanks.
View 2 Replies
View Related
Jan 15, 2008
Hello,
I have an mdx report that uses 7 parameters.
How do I stop the parameters from being dependent on one another and cascading?
When a user changes a parameter value, the flashing and repopulating of the other parameter boxes takes longer than the actual report to run.
I've tried removing the dependencies from the parameter dataset mdx code as well as edititng the parameter datasets and removing the dependencies.
Is cascading parameter relationships a default by design?
Any insight would be appreciated.
Thank you.
View 5 Replies
View Related
Aug 8, 2007
Hi,
I have a report and the parameters Passed to the report are Cusip and PeriodId through a stored Procedure.
i want to populate the cusip Parameters using a dataset thats got by a sproc called usp_getCusips.
and the PerioId using a dataset thats been got by another sproc called usp_Getperiods...
So in my report i have added 3 datasets one for the main report, one to get the values of the PeriodId and other to get the value of the cusip.
So in my Report parameters for the Parameter Cusip and i have checked the Available value option and selected its corresponding Dataset and value .
and i have also done the same thing for the PeriodId.. But when i run my sproc i am getting this Error.
[rsInvalidReportParameterDependency] The report parameter €˜Cusip€™ has a DefaultValue or a ValidValue that depends on the report parameter €œCusip€?. Forward dependencies are not valid.
[rsInvalidReportParameterDependency] The report parameter €˜PeriodId€™ has a DefaultValue or a ValidValue that depends on the report parameter €œPeriodId€?. Forward dependencies are not valid.
Any Help will be appreciated.
Regards,
Karen
View 7 Replies
View Related
Feb 21, 2008
I've had a report working that uses a parameter that allows null values. So, of course, I have the "Allow Null Values" flag checked on the parameters dialog for the respective parameter. But recently I changed the "Available values" option for the parameter to "From Query" but then I lose the ability to allow a null value! Why is this happening? Why are they mutually exclusive?
View 1 Replies
View Related
May 12, 2008
I am doing report development against cube. I have a main report and a sub report in the main report. I am collecting the parameters in the main report and passing into sub report which has the same exact parameters. (I am assigning parameters to the sub report from the Fields collection. If I assign from the Parameter collection, it works fine, but I need to do based on Fields collection because I have my grouping like that)
I am getting the error above, what could I be doing wrong?
View 1 Replies
View Related
Nov 16, 2007
Hi all,
I'm writing a web interface and calling reports from report server. I first used the ReportViewer control. But it seemed to be impressively faster to directly call the report URL and link that directly in a frame.
This all works fine. But I want to dynamically add the parameter values that are used by the report in my URL string. To find out those, I have written a stored procedure:
CREATE PROCEDURE dbo.spIIMGetReportParameters
@sReportname varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xmlParameter xml
SELECT @xmlParameter =
(SELECT Parameter
FROM [catalog]
WHERE [Name] = @sReportname)
SELECT parameter.Name.value('.','nvarchar(50)') AS ParameterName
FROM @xmlParameter.nodes('/Parameters/Parameter/Name') as parameter(Name)
END
Then I loop through the result and add parameters to the Url string.
But sometimes I get parameters that are not needed.
Is there another way to find at runtime which parameters a report expects?
Thanks,
Jan
View 1 Replies
View Related
Aug 8, 2007
Hi,
I have kept two Report Parameters "StartDate" and "EndDate" and i have selected From Query and viewed the preview. I got the reults but when i am selecting the two dates it is not validating the condition.
I need the results between StartDate and EndDate. For this i have to set any where in the parameters or is there any other way
can you please help me in this
Thanks
Dinesh
View 15 Replies
View Related
May 27, 2008
Hi,
How to link two report parameters of 2 different dimension in ssrs report,means how to write dataset mdx for that?
View 2 Replies
View Related
Feb 21, 2008
Hello Everyone,
I would like to create a report parameter that will allow the user to select a week from a list of all weeks so far this year.
For example:
First Parameter is Week: (Week 1, Week 2, ... , Week 7)
Second Parameter is Year: 2008
How can I accomplish this? Can I create a function and use it as a parameter query?
Thank You in advanced
View 5 Replies
View Related
Jun 30, 2006
Hi!
I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:
An error has occurred during report processing.
Query execution failed for data set 'DataSet3_generar'.
Must declare the scalar variable "@Param1".
I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.
But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.
I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)
So, is there a way of solve this using SQL server express? Or I need SQL server 2005?
If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?
Thanks! Any suggestion will help me a lot!
View 5 Replies
View Related
Dec 7, 2007
Hello,
I have a report (Report1) with a subreport (SubReport2). The subreport is a barchart. On the data values for the chart, I have a "Jump to Report" action defined. This jumps to another report(Report3) that has some parameters on it. Neither Report 1 nor SubReport2 have any parameters, only Report3.
When I jump to Report3, I would like to have it open up blank with the parameters open to be selected. I don't want to pass in any parameters as I jump to it. This works perfectly when I test it in my Preview pane in BIDS. However, when I upload the report to the ReportServer, when I click on SubReport2 to jump to Report3, I get an error saying
The 'Months' parameter is missing a value
There is no parameter pane for me to choose the parameters (or any other report viewer pane). However, if I open Report3 directly without trying to use Report1 and SubReport2 to jump to it, the parameter pane is there, everything works right.
Does anyone know how I can get the parameters pane to show up when I jump to Report3?
Thanks,
Andy
View 1 Replies
View Related
Nov 8, 2007
Hi! I hope this is not a stupid question ...
I'd like to "simulate" multivalue report parameters in t-sql. Background is, that I'm developing statements for report datasets with management studio for convinience. The statements need to apply a filter based on a report parameter like this:
select a, b, c
from datasource
where a in (@listofitems)
Which works great in reporting services, since @listofitems is converted to 'item1', 'item2', ... by reporting services. I tried to simulate the parameter by using:
declare @listofitems as varchar(100);
set @listofitems = '''Item1'', ''Item2''';
Unfortunatley it doesn't work as I expected. Has anybody had the same issue?
Cheers,
Martin.
View 8 Replies
View Related
May 12, 2008
Can someone give me some code showing how to work with local reports. I'm trying to pass some parameters to my query/ data source and then bind it to the report viewer control on my aspx page.
Also is it possible to bind a sqldatasource to a report viewer? (if yes, could you show me some code).
thanks.
View 2 Replies
View Related
May 29, 2007
I'm trying to generate a report off my DMX query and want to pass report parameters in it. I can't seem to figure out how to do this, b/c the '+' operator isn't valid in DMX and OPENQUERY encapsulates everything in quotes...
Basically it'd be OPENQUERY([ds],'SELECT ''@myparam'' as [Field]') But it never returns any values! Thanks for any help.
View 4 Replies
View Related
