Need Help On SQL Query On Reporting Services
Dec 10, 2007
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
End Query=========>
Any help would be much appreciated.
View 8 Replies
ADVERTISEMENT
May 13, 2008
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
View 3 Replies
View Related
Apr 2, 2007
Hi all,
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 ??
Many thanks,
John
View 2 Replies
View Related
Apr 2, 2008
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"/>
.. This information seems to be ingnored
View 2 Replies
View Related
Sep 2, 2015
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?
View 3 Replies
View Related
Mar 22, 2008
Hello,
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?
Any help is appreciated
View 1 Replies
View Related
Oct 7, 2015
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" ...
View 3 Replies
View Related
Sep 21, 2015
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?
View 3 Replies
View Related
Sep 20, 2007
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 will appreciate any help.
Nick
View 11 Replies
View Related
Sep 8, 2015
I have a query where:
WHERE a.jobdate >=GETDATE()-1
This brings though data from today and yesterday; I understand this is because GETDATE = now.
However, all I want is to see is data from today only.
Is there any way the above query can be amended - probably not using GETDATE - so I can achieve this?
View 3 Replies
View Related
Sep 18, 2015
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
[Code] .....
View 9 Replies
View Related
Oct 12, 2015
I want to write a conditional where clause in SSRS query, such as
WHERE CASE WHEN @para = 1 Then Column1 Like '%%'
WHEN @para = 2 Then Column1 IN (@NameList) END;
What's the correct syntax to write the conditional where clause?
View 12 Replies
View Related
Sep 7, 2015
How to call Boolean/Text parameter in SSRS Query.
Parameter (Cust )Properties:
My Query:
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 )
View 5 Replies
View Related
Nov 4, 2015
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.
View 2 Replies
View Related
May 27, 2015
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 (+)
View 6 Replies
View Related
Sep 2, 2015
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?
View 3 Replies
View Related
Aug 22, 2007
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...
Thanks!
SELECT DISTINCT SALES_REGION,
SALES_REGION_CODE,
PRODUCT_CLASS_DESC,
MEGLOMART_STORE_ID,
STATE_NAME,
MEGLOMART_TYP_CODE,
MEGLOMART_TYP_DESC,
MEGLOMART_COMMUNITY_POPULATION,
MEGLOMART_PENETRATION_PERCENT,
MEGLOMART_STAFFING_TARGET,
MEGLOMART_STORE_FT_STAFF,
MEGLOMART_STORE_PT_STAFF,
MEGLOMART_STAFF_SHORTAGE,
MEGLOMART_STAFF_TRAINEE_COUNT,
(CASE MEGLOMART_TYP_CODE
WHEN 'MEGA' THEN 'Full Service'
WHEN 'MID' THEN 'Traditional'
WHEN 'MIN' THEN 'Convenience'
ELSE 'Other' END) AS MEGLOMART_TYP_GRP,
(CASE WHEN MEGLOMART_TYP_CODE = 'MEGA' THEN 1
ELSE 0 END) AS MEGLOMART_MEGA_TYP_COUNT,
(CASE WHEN MEGLOMART_TYP_CODE = 'MID' THEN 1
ELSE 0 END) AS MEGLOMART_MID_TYP_COUNT,
(CASE WHEN MEGLOMART_TYP_CODE = 'MIN' THEN 1
ELSE 0 END) AS MEGLOMART_MIN_TYP_COUNT,
(CASE MEGLOMART_TYP_CODE
WHEN 'MEGA' THEN 0
WHEN 'MIN' THEN 0
WHEN 'MID' THEN 0
ELSE 1 END) AS MEGLOMART_OTH_TYP_COUNT
FROM
dbo.MEGLOMART_SNAPSHOT AS theView
WHERE
(PRODUCT_CLASS_DESC = 'Home Furnishings') AND
(NOT (SALES_REGION IS NULL))
AND @theWhere
ORDER BY
STATE_NAME,
PRODUCT_CLASS_DESC
View 26 Replies
View Related
Jun 25, 2015
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.
View 6 Replies
View Related
May 30, 2007
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.
------------------------------
ADDITIONAL INFORMATION:
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).
Any help would be greatly appreciated!
View 4 Replies
View Related
Nov 20, 2015
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.....
View 6 Replies
View Related
Aug 20, 2015
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?
="SELECT sAMAccountName, displayName, distinguishedName " +
"FROM 'LDAP://DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' " +
"WHERE objectCategory = 'Person' " +
"AND objectClass = 'user' " +
"AND memberOf = '" + @GlobalGroup + "'"
View 3 Replies
View Related
Sep 16, 2011
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?
View 4 Replies
View Related
Jul 21, 2015
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.
View 5 Replies
View Related
Sep 29, 2015
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.
View 2 Replies
View Related
Jun 25, 2015
I have to create a filter on a dataset that accepts a parameter value which in turn,
1. If i check 'NULL' for that parameter, it has to accept all values,
2. If i give value to that parameter, data depends on that value.
My previous developer has written OR(FIND(Parameter: Param1,Param1)<>0, Parameter: Param1 = EMPTY).how to write this formula in my formula builder..
View 2 Replies
View Related
May 18, 2012
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.
View 8 Replies
View Related
Nov 3, 2015
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.
View 8 Replies
View Related
Nov 29, 2010
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:
<Query>
<Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
<Parameters>
<Parameter Name="Criteria" Type="XML">
<DefaultValue>
[code]....
View 17 Replies
View Related
May 29, 2015
Currently, I want to get images of an item in my report for illustration purpose.
Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items.
SQL Query:
Select top 1 item_picture_mst.picture from item_picture_mst
where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000')
ORDER BY item_picture_mst.picture DESC;
View 3 Replies
View Related
Oct 14, 2009
I am creating a SSRS report using a SSAS cube as it data source. The user would like to select multiple values from a reporting parameter that is then used as a filter on the MDX statement. I am bale to have the report work successfully when only one value is selected but not when multiple values are selected; the report uses only the first value from the reporting parameter when it contains multiple values. How do I filter an MDX query using a SSRS report parameter with multiple values?
View 3 Replies
View Related
Oct 30, 2015
So, this is my Query for dataset "SalesOrder":
SELECT
SO_Header.Customer
,SO_Header.Status
,SO_Header.Customer_PO
,Customer.Name
,SO_Header.Order_Taken_By
[Code] ...
I would like to have a parameter on my report to Select by Order_Taken_By. My attempt at that is here:
Dataset called OrderTaken:
select distinct SO_Header.Order_Taken_By
from SO_Header
Under the report Parameter Properties named @Order, under Available Values
Selected "Get Values from a query"
I have DataSet: OrderTaken
Value Field: Order_Taken_By
Label Field: Order_Taken_By
The values of the field Order_Taken_By is all text characters, no integer values.
Running the inital Query by itself yields results. When I add the parameter, I can make a selection, but now I get no results, even though there should be values for the choice I've chosen.
View 11 Replies
View Related
Dec 6, 2006
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end.
One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows:
Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets?
Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run?
Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success.
Thanks to anyone who can help.
-- Mark
View 1 Replies
View Related
May 19, 2015
We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?
I checked executionlog2 but can not find the parameter property.
View 7 Replies
View Related