I have a process that stores a machine status every 2 minutes in a bit field of an SQL database. Running = True, Stopped = False. I am trying to build a report that shows the percent run time every hour. This information would be the count of Trues divided by the total count for each hour. I can select and group the data on the hour but I can either count() the total number of readings or count() the number of readings where the status is true. How do I collect both pieces of information in the same data collection?
SELECT TOP (100)
Max(Time) As Time_M,
Count(Running) As Count_T
FROM Compact10
Group by DatePart(mm,Time), DatePart(dd,Time), DatePart(hh,time)
Order by DatePart(mm,Time) desc, DatePart(dd,Time) desc, DatePart(hh,time) desc
Hi Gurus Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene). Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports?
Early Thanks, Salman Shehbaz. Database Administrator Xavor Corp. 92-321-440-0797
Hi ALL Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene). Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports? Early Thanks, Salman Shehbaz. Database Administrator Xavor Corp. 92-321-440-0797
Leon writes "I am trying to connect to a W2K3 Server's 2005 Reporting Server through my laptop's (WXP Pro SP2) SQL Server Management Studio.
I turned Port 1433 on in the server's Windows Firewall and was able to connect to the server's database engine from my laptop, but I have to turn Windows Firewall on the server off completely to be able to connect to the Reporting Server.
I have tried all the ports listed for SQL Server in the Microsoft documentation (TCP and UDP), but they seem to have no effect.
Can anyone tell me which port or ports I need to turn on in the server's firewall so that I can access Reporting Services through my laptop's SSMS?
I have created a linked report in which I have two screen: First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)
Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar). <!--[if !vml]--><!--[endif]--> Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.
In VS2008 SSRS, when creating a dataset, only the name of the stored procedure was required. When populating the dataset, VS prompted for the parameters, whether or not there were any. In VS2010, the name of the stored procedure is not enough if there are parameters.When creating a dataset in Visual Studio 2010 for databases and stored procedures in SQL 2012, I must provide (what should be) run-time parameters to obtain a field list and then to preview the report.
So the embedded dataset looks like this (wherein the only parameter is a password): exec dbo.spu_procedure1 @PW='999'.This works well and as expected during report design and development. But 999 must be provided at run-time by the requester, and not in the dataset definition. If I remove the parameter in the dataset's exec string before deploying the report, the field list is cleared and the report is unusable. So I cannot overcome what seems a contradiction: necessary to obtain a field list, but "yet-to-be-provided" when the user selects the report.
By keeping @PW='999' in the dataset specification, the user cannot override the parameter and automatically has a password to view the report.
Although not totally related to this RS cat but since I'm using the query in Reporting Services perhaps I can get a work around from someone. I've created a Rolling 2 year 12 month comparison report that compare sales figures from current and previous years. The query works fine however the query fails to return 0's for no sales for a particular month with no records. Is there anyway I can return a 0 instead. As you can see I tried "isNull to no avail.
Query==========>
SELECT Customer.Country, MetaDates.Month, MetaDates.Year, isnull(SUM(Order_Line_Invoice.Sales),0) AS Sales, SUM(Order_Line_Invoice.Cost) AS Cost, SUM(Order_Line_Invoice.Sales) - SUM(Order_Line_Invoice.Cost) AS GM, 'Current 12 Months' AS yearNum
FROM Order_Line_Invoice INNER JOIN Customer ON Order_Line_Invoice.CustId = Customer.CustId INNER JOIN MetaDates ON Order_Line_Invoice.InvoiceDate = MetaDates.Date LEFT OUTER JOIN Product ON Order_Line_Invoice.ProdId = Product.ProdId WHERE (Customer.country = @GroupByFieldFilter) and (Order_Line_Invoice.InvoiceDate BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)
GROUP BY MetaDates.Year, MetaDates.Month, Customer.country
UNION
SELECT Customer.Country , MetaDates.Month, MetaDates.Year, isnull(SUM(Order_Line_Invoice.Sales),0) AS Sales, SUM(Order_Line_Invoice.Cost) AS Cost, SUM(Order_Line_Invoice.Sales) - SUM(Order_Line_Invoice.Cost) AS GM, 'Previous 12 Months' AS yearNum
FROM Order_Line_Invoice INNER JOIN Customer ON Order_Line_Invoice.CustId = Customer.CustId INNER JOIN MetaDates ON Order_Line_Invoice.InvoiceDate = MetaDates.Date LEFT OUTER JOIN Product ON Order_Line_Invoice.ProdId = Product.ProdId WHERE (Customer.country = @GroupByFieldFilter) and (Order_Line_Invoice.InvoiceDate BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))
GROUP BY MetaDates.Year, MetaDates.Month, Customer.country order by MetaDates.Year, MetaDates.Month asc
I need to change the default member of my Time dimension according to a parameter received by a report in Reporting Services. When I code the MDX query I can't seem to be able to begin my statement with an ALTER CUBE to set the default dimension. It seems I can only do a SELECT..FROM..WHERE query.
Does anyone know how to set the default member of a dimension in a report ??
The Gobal value for the reporting timeout is 10 minutes (600).
I Have a report which takes around (1hr to run).. The report correctly timesout (page cannot be display) but the query kept on running..until it completed.
Is there any way to stop this from happening (Report and Query timeout after 10 minutes)
I have look on the internet and in my rsreportserver.config .. <Add Key="SQLCommandTimeoutSeconds" Value="60"/> <Add Key="MaxActiveReqForOneUser" Value="20"/> <Add Key="DatabaseQueryTimeout" Value="120"/>
I have SQL 2008 R2 instance and would like to install SSRS 2012. I have below query related to database configuration...Is there any performance impact while using SQL 2008R2 instance?Once configured SSRS database on 2008 R2, Will there any issue if we move our SQL instance from 2008R2 to SQL 2012 in future?
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit?
I have the following store procedure which execute perfectly fine Under SSMS as it is :
-- Insert statements for procedure here SELECT APHIST.ReturnDate AS ATDATE ,API_HIST.[ActionPlanItemID] ,API_HIST.[ActionPlanID] ,PIT.[ProductItemID] ,PIT.ProductItemCode
[Code] ....
But then when I try to create a dataset using ReportBuilder and pointing to that StoreProcedure, I get the following error message box :
"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. An item with same key already been added" ...
When I use an SSAS cube in Excel the command text is just the name of the cube itself and does not require an MDX query. Is it possible in SSRS to avoid writing an MDX query?
I recently changed my datasource in our Reporting Service Model. We changed to a quicker SQL server with disk capacity. I am able to run new reports but when I try to run old ones that were on the old datasource, I get an error. The datasource has been changed to point to the new server (datasource).
The error is: An error has occurred during report processing. (rsProcessingAborted) Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery ''). (rsSemanticQueryEngineError)
I have one view, i written below query to get employee hierarchy based on orgid and employee name..
If i select employeename it shoukd show employee reporties(under employees and below employee reporties like tree structure)
It is running fast in SSMS Level, but it is taking more time in SSRS(Sql Sever Report Services), If i run this query in SSRS,Some times System not responding.
Declare @OrgId int Declare @EName varchar(30) Set @OrgId=56793 Set @EName='ABCD' Select EmpId
SELECT dbo.incident.incident_ref, Customer.cust_n,incident.date_logged FROM incident INNER JOIN Customer ON incident.incident_id = Customer.cust_id WHERE incident.date_logged BETWEEN @date_from AND DATEADD(day, 1, @date_to)
I need to use a condition here to display customer based on my selection in dropdown list (Include ABC )
I'm using Report builder 3.0 to create a report. In the design, datatset query i have a sql to calculate difference between two dates (date1-date2) as processing time. I have this (processing time ) ONLY in my ORDER by clause and when i execute the statement it does exactly what i was looking for , however when i run the report the sort order is not delivered. I checked each column tablix properties and the sorting order is set for Processing Time.
I have a query where I am trying to display the most recent information about travelling method of customers using "t.collection_date". I am struggling to get MAX function working in the query.
SELECT P.id, P.forename, P.surname, P.dob, c.postcode, l.code_des, MAX(t.collection_date) FROM People p, MOT_HIST t, lk_mode_of_travel l, corresp_address c
WHERE p.ID = t.id (+) AND t.mode_of_travel = l.int_code (+) AND p.id = c.entity_id (+)
In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'
This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000
So I amended the SELECT query to:
MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'
This brings the date through as 11/06/2015, which is preferable.
But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.
Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?
Hello--I do not know if this group can help me with SQL Server Reporting Services ... but here it goes. I have a report built on a query and I want to be able to send a parameter (@theWhere) that is a string which is a where clause "(MEGLOMART_TYP_CODE <> 'XYZ' AND MEGLOMART_STAFF_SHORTAGE > 25)" these strings can vary depending on what columns the user selects and what operators they want to use. Generation of proper SQL for the where clause has been verified, I just need to be able to pass these, is there any way to do this...see example query below and how I was planning on using the @theWhere variable...
I was wondering on a question that : exporting a report to xlsx is feature of SSRS server or of Report viewer control itself.I have multiple report servers (eg 2008 R2, 2012 and 2014) . I am using a windows application and reportviewer version 9.0.So when I use report server 2008 R2 its not showing the xlsx export option but working fine with other report servers.Again I changed the control to 11.0 (also 12.0) and checked the same scenario and same results with Reporting server 2008 R2 but working fine with 2012 and 2014. if its worth to change the Report Viewer control or just changing the server is okay to achieve the functionality of exporting to xlsx.
I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:
declare @starttime as datetime declare @endtime as datetime declare @timezone as integer declare @date as datetime
set @timezone = 1 set @date = '5/1/2007'
set @starttime = dateadd(hh, @timezone, @date) set @endtime = dateadd(d, 1, @starttime)
select @Starttime, @endtime from site
Error Message:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).
I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is
select Hue from AvailableColors whereColor = @ColorParam.
For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....
Is it possible to use a parameter in a ldap query using the ADsDSOObject provider? I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider". Command parameters are unsupported with the current provider.
I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design. Here is my query for my dataset. If I hard code an example it works. Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of. Is this possible? or do I just need to push for a linked server?
I am a beginner with Report Designer (and with Reporting Services). Following the tutorials 'Adding parameters to filter Reports ...' I tried using the @symbol to add a parameter to my query: where code = @code_value...I get the error: ORA -000936 missing expression (msorlib) Is there another syntax that I should use to define a parameter/variable in query for my oracle dataset?
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00 0.50 0.00 0.00
To be displayed in the below format
1.25 0.75 0 0 1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query or SSRS expression.
So I have a report that uses an MDX query to fetch the main data for the tablix. Then I have a custom row added which pulls data in via a Lookup function and references a different dataset. The report has 3 parameters. Each dataset uses those 3 parameters in its underlying query. However, the dataset referenced in the lookup function doesnt seem to be updating when change the parameters and re-run the report.
I have a very frustrating problem. But given that this is a development forum, I'll keep that for my 'Envelope Lickers Rehab' and instead talk about an issue I'm having.
I have a very large query (line-wise) that executes in less than a minute when run from the SQL Management Studio as well as via Excel Services.
It is a stored procedure with one parameter. When I try to prime the designer with this query it gives me the following message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated."
I've considered cracking open the xml source and manually creating the fields, but I'd rather not go there.
When I click on the DataSet Query Desginer there is an Error Source:.NetSqlClient Data Provider. why this error comes and I have executed the SQL query in SSMS succesfully and the result comes. However this is causing the report from the report manager to fail.
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
