Reporting Services :: Displaying Sum Of Current Month And Year To Date Without Using Two Separate Datasets?
Jun 4, 2015
I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.
This is my dataset
SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period
[code]....
I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.
StaffId | Non Chargeable Time | Chargeable Time
CJJ | 0:20 | 4:20
| 4:50 | 19:20
JN | 0:05 | 5:30
| 1:30 | 25:30
The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.
I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.
View 6 Replies
ADVERTISEMENT
Jun 29, 2015
How to show the CurrentMonthanddateandyear in my report header in ssrs?
1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.
2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.
View 9 Replies
View Related
Aug 21, 2015
I have a SSRS report developed in Visual Studio 2013 and using SQL server 2012. It has a date control based on parameters which works correctly in the development environment. I can select by day AND I can move from date to date by clicking on the month name to be able to move by month or double clicking to be able to move by year.
When the report is deployed to Internet Explorer 11 or Firefox I can only, click back and forward by one day at a time and I can not double click the date control to move by month or double click to move by year. I can type the dates into the parameter box and get the correct result. I have set the compatibility view settings in IE for my server.
View 4 Replies
View Related
Sep 18, 2013
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
View 2 Replies
View Related
Sep 28, 2007
Hi,
How to select max of year-month in the Reporting Services. For example I have 3 fields :
1. Year-Month (format : yyyy-mm)
2. City
3. Amount Sales
In the bar chart graph, I have put the City as X-Categories, Amount Sales as Data
And I want to filter only the lastest year month in the database.
Can we do this without going to database and set the filter there ?
Thanks
best regards,
Tanipar
View 4 Replies
View Related
May 28, 2015
I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.
ID, Year, Month, Value
36002084 2014 Jan 8391
36002084 2014 Jan 0
36002084 2014 Jan 3440
36002084 2014 Jan 0
36002084 2014 Jan 548
36002084 2014 Jan 109
36002084 2014 Jan 125
I wrote the Query below
select
[UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total'
from
RegData
where
[UEP ID]=36002084 and [Year]=2014
group by [UEP ID],[Year],[Month]
I got this output
36002084 2014 Apr 887620
36002084 2014 Aug 789440
36002084 2014 Dec 528453
36002084 2014 Feb 606627
36002084 2014 Jan 331613
36002084 2014 Jul 681314
36002084 2014 Jun 330384
36002084 2014 Mar 1044301
36002084 2014 May 671818
36002084 2014 Nov 465576
36002084 2014 Oct 590147
36002084 2014 Sep 527861
Now I need to get a output like this,
36002084 2014 Jan 331613
36002084 2014 Feb 938240
A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.
Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.
View 10 Replies
View Related
Sep 30, 2015
I have below report with following data
Date Count
April-2015 100
January-2002 30
November-2010 55
July-2000 10
What is the best approach to sort this data based on date in a tablix and also sort the date in Column bar chart?
View 4 Replies
View Related
Oct 1, 2015
I have a column bar chart that displays counts based on category (month-Year). I have used an expression to sort the category data also.
Sample data
category countMarch-2011 2
Feb-2012 4
July-2012 7
Aug-2013 10
I have to color format the bars in set of 4 colors. I have used SWITCH statements for other charts and it worked. But here in this case since it is a date field, I am getting error.
View 4 Replies
View Related
Jan 18, 2007
I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
View 5 Replies
View Related
Aug 30, 2004
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()
And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
Thank you,
Cesar
View 4 Replies
View Related
Oct 26, 2006
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
View 8 Replies
View Related
Aug 27, 2015
I have the following code block
CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2
My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'
View 4 Replies
View Related
Mar 11, 2014
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------
DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)
SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL
[Code] .....
View 2 Replies
View Related
Feb 3, 2015
I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
View 6 Replies
View Related
Nov 4, 2015
I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
These columns are dynamic acc to start date for which i have used this exp for first cell:
=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.
These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:
q12015 |q2 2015 |q32015 |q42015
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
exp for quarterly is :
="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) & year(Parameters!Startdate.Value).
Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)
q12015 |q22015 |q32015 |q42015
feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.
What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.
And in q4 nov ,dec and jan is coming and what is want in q4 is oct,nov and dec.
So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..
Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.
View 5 Replies
View Related
Nov 2, 2015
I have an ssrs report which is something like
taskname| jan|feb|march|april|.........
[taskname value]|{now i have merged all cell till the end of months row.}
I have another value (start date value). I want date to be displayed below that particular month as i have merged that all cells so its is causing me some problems.
View 5 Replies
View Related
Feb 2, 2004
I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.
I'm using a DatePart function that converts my date:
CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END
This returns te american version, can anyone help me to get this to convert the UK way.
Thanks
View 3 Replies
View Related
Jul 22, 2015
In SSRS report I have 2 parameters start date and end date, so when somebody is selecting end date more than a month(if today date is 22 then they are selecting aug 23) then it should give error message to user like "Date out of range".
View 2 Replies
View Related
Apr 14, 2008
Hello All,
i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne
View 3 Replies
View Related
Mar 8, 2008
Hi,
I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.
SO I need to do:
IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)
But it has to be comparing the year,month and day (excluding the time part of the datetime type).
View 9 Replies
View Related
Jan 12, 2005
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
View 6 Replies
View Related
Apr 17, 2008
Hello
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
So, this is what I have.......
Code Snippet
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
Is there a neater/better way of coding this?
Jon
View 5 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
May 14, 2004
Hi,
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....
Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
View 5 Replies
View Related
May 14, 2004
Hi,
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....
Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
View 7 Replies
View Related
Feb 16, 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jun 3, 2015
I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.
how to modify my where clause so that it runs regardless of even if its Jan ?
View 6 Replies
View Related
Apr 21, 2008
Hi All,
How do I show only month and year from datetime data type field?
As a example; If the date is 01-10-2008.
I wanna show the date in my report as Jan - 08
Thanks
View 17 Replies
View Related
Mar 27, 2015
I'm trying to generate a report using matrix like this
Month
Product PreviousYearSalesAmount SelectedYearSalesAmount %SalesDifference
I can populate year sales amount, but i cant calculate the percentage.
Note: Month and Year are passed as parameters.
View 5 Replies
View Related
Apr 22, 2008
hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08
?thank you
View 9 Replies
View Related
Feb 19, 2008
Hi,
I'm having problems creating a query that brings the results based on last 8 days on date column (column9) and diferrent year from the current one.
If I run the each filter,
Code SnippetYear(Column9) <> Year(GetDate())
and
Code Snippet
(Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
it returns the right values.
So far I've got (it returns no results):
Code Snippet
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14,
Column15, Column16, Column17, Column18, Column19, Column20, Column21, Column22, Column23, Column24, Column25, Column26, Column27,
Column28, Column29, Column30, Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, data_anulacao, data_instalacao
FROM Contratos01
WHERE Year(Column9) <> Year(GetDate()) AND (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
ORDER BY Column1
Please give me your input.
THX.
View 10 Replies
View Related
Dec 2, 2011
In my table I am using varchar to save a date like this 2-dec-2011. Now I have a requirement where i need to select employee who where present in a month so for that I need a query so that i can pull out all the employees who are present in dec like :
Code:
select * from emp_attendence where date = 'dec-2011'
But this returns nothing ...
View 8 Replies
View Related
Feb 6, 2008
hi
i have column in database as account open date
format as:Jan 27,2004 12:00:00:AM
How do i extract month& Year from this column..
all i have to do a calculation
if accountopendate is prior to dec 31 1994 then jan 1995..
and if the account open date is after 2100 then ist jan 2011.
how do i write the calculation
Thanks guys
phani
View 11 Replies
View Related
