I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @dateparam AND [datefield] <= GETDATE().
The @dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:
Date Query dataset
SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder
UNION
SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder
UNION
SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder
UNION
SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder
UNION
SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder
UNION
SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder
UNION
SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder
UNION
SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder
ORDER BY 3
When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.
It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:
reportMonth - Just a non-queried list of months for the user to select reportYear - Generated using the following query from dataset "Years":
declare @curYear int set @curYear = 2000 declare @yearTable table (repYear int not null) while @curYear <= year(getdate()) begin insert into @yearTable(repYear) values (@curYear) set @curYear = @curYear + 1 end select * from @yearTable
This generated a list like:
2000 2001 2002 etc.
I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.
I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).
One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.
What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?
I don't have much knowledge on MDX queries. Here, requirement is to get the data less than the date passed from the parameter(Date - IV Financial].[calendar YQMD].[Date]).
I am able to create the below query(through Query designer) which is giving the data EQUALS to operation. But, I need the same with LESS THAN.
SELECT NON EMPTY { [Measures].[InventTrans Quantity] } ON COLUMNS, NON EMPTY { ([Items].[Item Id].[Item Id].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateIVFinancialCalendarYQMD, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Inventory].[Is posted].&[Posted] } ) ON COLUMNS
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is
WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD') When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results. But when I run the report from Preview, I get no results at all no matter what format I use in the function. Any chance any of you have seen this and know how to work with it?
in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
Hi Guys, Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Is there anyway to get the date an index was created on a table? I can see that there is a created date column on various system tables (sys.objects etc) but I can't find one related to an index.
After connected to Analysis Services and following the path below:
Analysis Service - Databases - Cubes
I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.
I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?
Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.
I have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
I create a Dimension Date using SSAS 2008 but when i execute the dimension and i go to see the result i have this result:the result is not sorted..what i need is having the result order by year i mean i have Calendrier 2020,Calendrier 2019 ...
I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?
Any one please tell me is there any possible way to identify the table modified date.
I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates. Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.
I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date can any one help
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
Hi, I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.
how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?
Thanks.
select a.vchcompanyname, count(*) as total from company a inner join incident b on b.iownerid = a.icompanyid where b.iincidentcategory = 1 and a.icompanytypecode = 102165 --and total > 2 (Wont recognise Total) group by b.iownerid, a.icompanyid, a.vchcompanyname order by 2 desc
I have a view V1 created as a plain UNION from 2 tables:
SELECT * FROM T1 UNION SELECT * FROM T2
If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned. However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.
Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?
SELECT Column_1, Column_2, Column_3, 10*Column_1 AS Column_4, 10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT Column_1, Column_2, Column_3, 10*Column_1 AS Column_4, 10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1 First 3 Columns are available within the Original Table_1 The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
