I have a weekly report that i want to display mon tues wed thur and fridays date from last week.
I know how to specifically pick one date:
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))
that would set the parameter to monday. But how to do i make it select all the dates from monday-friday of the previous week?
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates.
when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),
101))))
but i keep getting an error that says:
The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.
Build complete -- 1 errors, 0 warnings
whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.
Hi,
I need to run report that shows activity for Paediatrics Consultants who starts their audit week on Fri 5pm and finish next Fri at 5pm.
Set datefirst would work Fri to Thur but not with hours. And does not always seem to work either.
I need to be able to see historical data and do not want people to enter dates (from to), will group it by wek, consultant
Any clues? Cheers
Petr
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id
FROM request Any help would be greatly appreciated.
I want to make a function which returns the next Friday, given a day in the week. I can't find a solution which works. For example:
Input -> Output
4/22/08 -> 4/23/08
4/23/08 -> 4/23/08
4/24/08 -> 4/30/08
I can't find a solution which works. According to one website, this should do it:
DECLARE @Friday DATETIME
SET @Friday = '20000107'
SELECT DATEADD(day, ((DATEDIFF(day, @Friday, GETDATE()) / 7) * 7), @Friday)
hi friends,
how to get the date of the first/last day of the week in sql...
can any one help me
Cheers,
raj
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.Â
Note : Week should be generated from Monday to Sunday within desired date range
Is it possible to show previous week Sunday date as default in Date Parameter in Reports.
View 2 Replies View Relatedi have this FUNCTION
and the FUNCTION work OK
now how to add to this FUNCTION another mondy date ?
"last month last monday"
Code Snippet
CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime )
returns table
as
return
(
select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset, 103)
as Date,
[Index],
Number
from
( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all
select 2, 'Second_Monday', 7 union all
select 3, 'Third_Monday', 14 union all
select 4, 'Fourth_Monday', 21 union all
select 5, 'Fifth_Monday', 28
) x
where month(dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7)
= month((dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset)
)
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
now i need to add to the FUNCTION "last month last monday"
like this
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
04/02/2008 last monday 0 ---------------------- i need to add
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
------------------------------
TNX
@pvColumnName  VARCHAR(100) = Default,  
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
My boss would like to see one report with parameter promts. I tried doing this, but I keep getting an error. Can someone help me out here?
Thanks.
WHERE
(@YEAR(CLM_Dout) = YEAR(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND MONTH(CLM_DOUT) = MONTH(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
Hi,
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.
Thanks !
JF
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies View RelatedI have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.
But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.Â
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of ordersÂ
The chart needs two series:
Previous Week. The running count of orders placed that week.Â
Current Week. The running count of orders placed this week.Â
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.Â
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.Â
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
-Â [URL] ...
-Â [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
How can I get Saturday's date given the week number?
This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.
i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday
CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES
[code]....
The output should look like this
weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000
Hi all,
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]
But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
Thanks
Greg
I'm using a stored procedure that receives one parameter namely @EmployeeID INT
but when I want to give this parameter a default value, my SP fails.
I did it like with a varchar where it works.
---
@Employee INT = '%'
---
Is this correct or did I use a wrong syntac/wildcard?
Greetings,
Geoff
Hi
I have a report parameter that may contain one item or it may contain more than one item. Is there a way I can default the parameter if it contains one item, to that item. But have no default if there is more than one option?
Cheers
I am adding week hrs using while loop , but it can continue next week hrs also.
I can get every week start hrs while update @tem1 table
employeeidreportdatereportatleftattoaccountwrhrn
12902014-01-29 00:00:00.00009:3019:1556001
12902014-01-28 00:00:00.00009:0018:4555802
12902014-01-27 00:00:00.00009:0018:455585583
12902014-01-25 00:00:00.00008:0010:0012004 -- week end
12902014-01-24 00:00:00.00009:1718:4554105
12902014-01-23 00:00:00.00009:1918:4654606
12902014-01-22 00:00:00.00009:1718:4754507
12902014-01-21 00:00:00.00009:1618:3552608
12902014-01-20 00:00:00.00009:1818:555435439
My loop statement
while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh
129029 Jan 201409:3019:15008:0009:20024:00065:54
129028 Jan 201409:0018:45008:0009:18016:00056:34
129027 Jan 201409:0018:45008:0009:18008:0009:18
129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end
129024 Jan 201409:1718:45008:0009:01040:00045:16
129023 Jan 201409:1918:46008:0009:06032:00036:15
129022 Jan 201409:1718:47008:0009:05024:00027:09
129021 Jan 201409:1618:35008:0008:46016:00018:04
129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
View 4 Replies View RelatedI need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.
Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008
Is there a SQL function that will do this?
Thanks,
Mike
Hi... need to default the customerId on a sub form.
Insert string is:
InsertCommand="INSERT INTO CustomerNotes(Note, CustomerID) VALUES (@Note,@KEY)"
Parameter:
<asp:Parameter Name="KEY" DefaultValue='123456789'/>
Need to make the '123456789' the value of the request.querystring('ID")
How is this done?
Thanks in advance....
in a StoredProc I want depending on whether a parameter is passed formulate the Where clause accordingly.
If parameter IS NOT NULL
THEN take all Rows
Else take the row with the ID (INTEGER)
How do I formulate the where clause?
I am using a query to grab information about the parameters of stored procedures. I am trying to determine if a parameter has a default value. I have tried adding a default value to the parameter of the stored procedure, but the returned value of params.has_default_value stays at zero. What is going on?
Here's the query to pull the stored procedure information:
SELECTprocs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROMsys.procedures procs
LEFT OUTER JOINsys.all_parameters params
ONprocs.object_id = params.object_id
LEFT OUTER JOINsys.types types
ONparams.system_type_id = types.system_type_id
ANDparams.user_type_id = types.user_type_id
WHEREprocs.is_ms_shipped = 0
ANDprocs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BYprocname,
params.parameter_id
Here's the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = 'a'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
WHEREDISPOSAL_AREA_NAME = @DISPOSAL_AREA_NAME
END
END
I have a report which has paramets as startdate and enddate. I want to set default value as yesterday. So when It opens it shows yesterday data.
I have a expression like this.
(DATEDIFF(dd, 0,MoneyFlowTransaction.ProcessedTime) = DATEDIFF(dd, 0, GETDATE()) - 1)
But I'm wondering how I set this as dafault value.
can anyone help me pls?
Hi everyone,
I have a report that a user can enter parameters in. If the user opens the report the parameter box is at the top and expanded. The report is also linked to by another report, so when a user clicks on something in this other report it becomes the parameter for the one I'm having the problem with. The problem is the user can select the number of days that are displayed, so when I link from the other report I just pass 10 by default, but now in the report the parameter box is collapsed and I'm worried that users won't find it. Is there a way to force it to be expanded? Sorry this is probably confusing to read.
Thank you,
Keith
I set parameter as Multipul selection.
how to set it as "Select all" by default?
Thank you!
Hi Folks,
I'm having a problem showing my boolean default parameter on report server in our test environment.
The report server shows my boolean parameter with a yes and no radio button. I assigned my boolean parameter with a 'No' default. The default shows and runs fine when I run the report within Visual Studio.(I hope I'm using the correct terminology.) However, on the report server the 'No' radio button is not checked by default. What am I doing wrong?
Thanks in advance for your assistance.