Creating Date From Parameters
Mar 21, 2007
Hello all,
Hope that someone can help with this. I am trying to write an expression that will return a date using the two of the parameters on the report. I tried using CAST but VS will not allow that.
The parameters are..
Parameters!Month.Value
Parameters!Year.Value
There are going to be twelve columns on the report, each one representing a month out of the time frame selected. If the user were to select January 2007 the columns would then display --Jan 07, Dec 06, Nov 06 and so on.
Thanks so much for any help in this.
View 1 Replies
ADVERTISEMENT
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Oct 24, 2007
Any way to disable the calendar datepicker show it does not even show up on the report?
View 2 Replies
View Related
Mar 31, 2008
Hi,
I'm using the following code (created by Report Designer) to populate the From and To data controls in my report;
WITH MEMBER [Measures].[ParameterCaption] AS '[Check Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Check Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Check Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Check Date].[Date].ALLMEMBERS ON ROWS FROM [MyCube]
The report parameters for From and To contain a first item of 'All' which I don't want. How can I remove this please?
Thanks.
View 3 Replies
View Related
May 31, 2000
when i was trying to create a database like
CREATE DATABASE @dbname
i am getting the message
Incorrect syntax near '@dbname'.
is it possible to create a database like this
if yes how
Thanks alot on advance.
View 3 Replies
View Related
Mar 5, 2008
I'm using a Database called COMPANY and witihn this I have a table called Works_on.
I'm looking to create a trigger for update on Works_on that if the Hours coloum changes it MUST NOT be reduced and also that it can't be increase by more than 5%
Any idea how I can code this??
View 3 Replies
View Related
Dec 30, 2007
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
View 5 Replies
View Related
Jan 21, 2008
I am using a 2005 SSAS cube as a datasource for 2005 SSRS report with multivalue parameters. I use the design mode query builder to generate my MDX. I then add my parameter to my parameter field in the filters section. The name and value of the parameter in the dataset editor is as follows:
Name: FacilityFacilityNumber
Parameter: Parameters!FacilityFacilityNumber.Value
I then proceed to layout mode and then open up report parameters. I then check the multivalue box and select non-queried available values. I choose non-queried radio button because I want my asp page to be passing in the parameters, not a dropdown list.
The problem is that when I try and preview the report and I type in just one parameter I get the following:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"
After looking through a couple of forums I found that adding the following Parameter value in the data tab and dataset editor would help me out a bit:
"[Facility].[Facility Number].&["+Parameters!FacilityFacilityNumber.Value+"]"
Ok so now (in layout mode) I type in one parameter and I get a report back, BUT when I try to type in a second or more parameter I get the same error as before:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"
Here is the MDX query that I am using for this report:
SELECT NON EMPTY { [Measures].[Claim Count], [Measures].[Item Reasons Count], [Measures].[Charged Amount] } ON COLUMNS, NON EMPTY { ([Facility].[Facility Number].[Facility Number].ALLMEMBERS * [Claim Status Codes].[CLaim Status Code].[CLaim Status Code].ALLMEMBERS * [Claim Status Codes].[Claim Status Description].[Claim Status Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@FacilityFacilityNumber, CONSTRAINED) ) ON COLUMNS FROM [Claims Remittance]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Please, if anybody know's what I am doing wrong I would greatly appreciate it.......I have to have my report accept non-queried multivalued parameters. Thanks
View 1 Replies
View Related
Jan 23, 2008
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters.
the variables will be: @lastmonth, @curryear and @id
@lastmonth should inherit Session variable intlastmonth
@curryear should inherit Session variable intCurrYear
@id should inherit Session id
One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id")
The rest of the queries will be similar and use all 3 variables as well.
How can I go about this and how will queries be seperated.
View 2 Replies
View Related
Sep 9, 2014
I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"
View 3 Replies
View Related
Feb 17, 2008
Hi all,
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.
Please help and advise.
Thanks,
Scott Chang
View 18 Replies
View Related
Feb 13, 2008
I have three databases which have the identical data structure. When the user runs a report, he has to select a database.
For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list?
In my datasource, I have only specified the server name and no value for initial catalog.
View 1 Replies
View Related
Sep 1, 2015
I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.
SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information],
user8 AS [Transaction Type], user2 AS[Invoice Number],
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]
[Code] ....
View 2 Replies
View Related
Mar 20, 2008
See Code below -->> I need help with date parameters. I was told to pass in parameters for the meters date >>see underlined<<. I originally had specific years there but they do not want the years to be hard coded into the script. So I tried to replace with a parameter but need help. I have placed the years next to the parameters that were originally in the 'where' clause where the underline is.
SET NOCOUNT ON
DECLARE
@mtr_ageAdatetime, --<=1/1/94
@mtr_ageBdatetime, --<=1/1/07
@mtr_ageCdatetime; --<=1/1/07
set @mtr_ageA = (getdate()); (this should be a year not current date)
set @mtr_ageB =(getdate());
set @mtr_ageC =(getdate());
create table #aged_a
(
Entnovarchar (4),
Customer_Name varchar(100),
Loc_Nbrvarchar (10),
--Contract_Nbrvarchar (10),
Ref_Novarchar(20),
Meter_Novarchar(20),
Service_Areavarchar(10),
Pol_Subvarchar(10),
Kind_Codevarchar(10),
Mtr_Type varchar(10), --group a,b,c
Set_Datedatetime,
Mtr_Statusvarchar (1),
Meter_Locvarchar(20),
Addressvarchar(100),
Cityvarchar(30),
Statevarchar(2),
Zipvarchar(10)
)
Insert Into #aged_a
(Entno, Loc_Nbr, Meter_No, Service_Area, Pol_Sub, Kind_Code, Mtr_Type, Set_Date, Mtr_Status,
Meter_Loc, Address, City, State, Zip)
select
locn_entno,locn_locno, mplm_metno, locn_serv_area, locn_polsub_id, winv_subclass, winv_inv_type, mplm_set_date, mplm_meter_status,
ISNULL(mprl_rloc_desc,''), ISNULL(locn_addr, '') + ' ' + ISNULL(locn_addr2, ''), locn_city, locn_state,
locn_postal
FROM rs3_prod.dbo.MP_Location_Meter (NOLOCK)
INNER JOIN rs3_prod.dbo.WO_Inventory (NOLOCK)
ON mplm_entno = winv_entno
and mplm_metno = winv_invno
INNER JOIN rs3_prod.dbo.RS_Location (NOLOCK)
ON winv_entno = locn_entno
and winv_locno = locn_locno
Left outer join rs3_prod.dbo.MP_Read_Location (NOLOCK)
on mprl_rloc_code = mplm_rloc_code
WHERE (winv_inv_type = 'GROUP A' and mplm_set_date <=@mtr_ageA )
or (winv_inv_type = 'Group B'and mplm_set_date <=@mtr_ageB )
or (winv_inv_type ='Group C' and mplm_set_date <=@mtr_ageC )
View 1 Replies
View Related
Nov 5, 2007
I'm using MDX to query a cube. I have two identical data sets:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date Shipped].[Year-Month].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date Shipped].[Year-Month].ALLMEMBERS ON ROWS FROM [Heidtman DW]
How do I get one to be the "From" date and the other to be the "Thru" date?
I'm passing the correct parameters ([Date Shipped].[Month].&[2006]&[4]&[12]) & ([Date Shipped].[Month].&[2007]&[4]&[11]) but I don't know how to tell the query to use them correctly.
View 5 Replies
View Related
Nov 26, 2007
hi all
I am having problems with parameters
WHERE (CONVERT(datetime, Ticket.CreatedDate) >= @StartDate) AND (CONVERT(datetime, Ticket.CreatedDate) <= @EndDate)
My Report Parameters are date/time
I am in Data view of Reporting Services.
If I run this in Preview it works ok but sometimes I need to run in data view - what is incorrect with this?
thanks
Dianne
View 10 Replies
View Related
Jun 12, 2007
Hi,
I follow MAK's instruction here: http://www.databasejournal.com/features/mssql/article.php/10894_3515886_1
The problem is that it works only on the local machine server 2k3 with sql 2k5 installed. I have 2 other boxes( 2k3 and 2k server) that need to extract the security log.
I manually ran the logparser.exe with the same sql parameter but it didn't work either. If I leave out the WHERE clause, it works fine. But then, it pulls entire log file.
It does not spit out any hint nor error.
Please help.
TIA,
Cal
View 2 Replies
View Related
Oct 14, 2013
i have 3 table
DECLARE @a1 table(
p1 int ,
date1 smalldatetime
)
Insert Into @a1
(p1,date1)
Values
[code]....
how i get resulting table
num_p1 num_p2 num_p3
1 3 5
View 13 Replies
View Related
Jan 4, 2007
hi all
We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet
Using MS Sql - Reporting Services 2005
RecvdDate field = yyyy-mm-dd - is how it is displayed.
I added a @startdate and an @enddate parameter and am using the
calendar - so a user picks off the calendar
I read up and saw this information:
Report properties set
'Language' setting to '=User!Language' to detect user
culture automatically
My regional settings are set to English (New Zealand)
but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the parameter that is incorrect.
Can someone give me some advice on what else to check.
thanks
View 2 Replies
View Related
May 8, 2008
Hi,
I have start and end date parameters on my report. The requirement is to dynamically update the start and end date parameters of a report based on another parameter 'DateRangeSelect' selected as week, month and Year. Based on the value as week, month, the start and end dates will be for a week / for a month selected dynamically and stored in a dataset,
When Default values are set for the start and end dates as fields from the above dataset, the dates are populated first time correctly. But when I modify the criteria from week to month, the start and end dates are not refreshed. Instead the start and end dates remain the same as of the first selection, irrespective of my subsequent change in selections from week to Year. On the other hand, if I assign the dataset to the Available values, the dates are assigned properly after refreshing but now the problem is, the datepicker is not available for picking and changing the date.
So, just wondering how I can get both, ie., the date ranges refresh based on my selection of week or month and datepicker also intact for changing the dates. Thanks.
View 1 Replies
View Related
Nov 14, 2006
I have a problem in that I have a report that hooks into oracle that returns data between 2 specified dates. I have created the two parameters startdate and enddate.
The query runs nicely and returns the desired results in the Data view screen, however when I run the report in the Preview screen no results are returned and no error messages pop up. The query is below:
SELECT APK052_WARDSTAY.WARD, COUNT(APK052_WARDSTAY.X_CN) AS NUM_PATIENTS
FROM APK052_WARDSTAY, APK051_CONEPIS, APK050_HPROVSPELL
WHERE APK052_WARDSTAY.CEP_NO = APK051_CONEPIS.CEP_NO AND APK051_CONEPIS.HSP_NO = APK050_HPROVSPELL.HSP_NO AND
(APK052_WARDSTAY.WS_EDATE BETWEEN TO_DATE(:StartDate, 'DD/MM/YYYY') AND TO_DATE(:EndDate, 'DD/MM/YYYY')) AND
(APK050_HPROVSPELL.MI <> '2')
GROUP BY APK052_WARDSTAY.WARD
As a bit of a test in the preview screen I spit out the value of the parameter fields and I notice that the date value are switched to the us format. Not sure if this has anything to with the problem or not.
Can someone please help / advise.
Thanks
Matt
View 1 Replies
View Related
Aug 28, 2007
I have a standard on-demand report that now also needs to be e-mailed to a group of clerks. No problem there as I have several subscriptions in place already, however, they want this subscription to report from the system date to three days in the past. I could clone the report and change the date range to @Startdate= GETDATE()-3 and @Enddate = GETDATE() but then I would have to manage multiple reports that return the same thing. Is there a method in the date parameter field in subscriptions that can achieve the same thing?
View 3 Replies
View Related
Feb 20, 2005
HI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance
VS
View 9 Replies
View Related
Feb 15, 2012
I need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.
View 1 Replies
View Related
Mar 26, 2008
Hi,
Not sure if I will be able to get the answer I need on this forum but hopefully I will!
We have a previous report that uses the code:
AND T1."Decision_Date" BETWEEN '2005-07-01 00:00:00.000' AND '2006-06-30 00:00:00.000' AND T1."Decision_Type_Description"
With this the report was run with the "static" dates, the new report (in MS reporting services) requires that the user be able to enter in the start date range and the end date range.
I have come up with something like:
(T1."Decision_Date" = @Start_Year) AND (T1."Decision_Date" = @End_Year)
But not working. Can anyone assist?
Thanks,
jonathanr
View 1 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
Apr 14, 2008
I am trying to create a table with the following statement.create table datemanipulation(invoice_date getdate(), deadline datetime))But i am getting the following syntax error when i execute this commandMsg 102, Level 15, State 1, Line 1Incorrect syntax near ')'.I don't know what is the errorThanx
View 2 Replies
View Related
Dec 3, 2001
Hi all-
I was wondering if you had created a default for data/time (I'm looking to create one that will insert the equiv. of time()). When I try to make one and set the value to time() it tells me the value cannot be anything except a function, constant, or var. What's the deal?
Thanks.
-Chris
View 1 Replies
View Related
Feb 25, 2002
Hi,
I am new to SQL Server, and this may be a silly problem .. but here goes!
I need to create tables based on the date ie FEB2000 for EOM reports, I thought I may be able to do it by
1) Check if Table Exists, if so fop it
2) Recreate Table
3) Populate it with data
Unfortunately I'm still on step 1 <sad look>
--->
CREATE PROCEDURE TEST AS
DECLARE @TableName varchar(50)
SET @Tablename = 'FEB2000'
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Tablename)
DROP TABLE @tablename
GO
<---
I would of course hand the table name as a variable, but for testing purposes used a set variable.
Thanks in Advance
View 2 Replies
View Related
Feb 21, 2007
well i guess we were all a newb once so here goes
something is wrong with the below it just doesnt like the date part
create table PLN_Workgroup_Resource
(
Dte date(ddmmyyyy),
Workgroup varchar(20),
AM_Hrs integer(3),
PM_Hrs integer(3),
EVE_Hrs integer(3)
)
;
View 4 Replies
View Related
Jul 20, 2005
I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
View 2 Replies
View Related
Dec 13, 2007
I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.In my SqlDataSource, I setup the SQL like this.select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appendedand exists (select 1 from detailTable where pid = masterTable.id and productNo = @productNo)The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.Any suggestion on tackling this problem? Thanks!
View 4 Replies
View Related
Apr 21, 2015
I have a Start Date and End Date parameter in my SSRS report. The results are off by 1 day. For example if I enter 4/2/2015 and 4/20/2015 it will return a few results from 4/1/2015 to 4/19/2015.
View 4 Replies
View Related
