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

Reporting Services :: Show Current Month / Date And Year In Report Header In SSRS

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

Reporting Services :: Date Control Does Not Allow Double Click To Move By Month Or Year

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

Fiscal Year Totals - Calculating Sales By Month And Current Year

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

How To Select Maximum Of Year-month In Reporting Services ?

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

Reporting Services :: How To Get Running Total Depending On ID / Year And Month

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

Reporting Services :: How To Sort Data In SSRS Based On Month And Year

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

Reporting Services :: How To Color Format Column Bar Chart That Are Categorized On Month-year

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

Current Month && Year Query?

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

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

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

Sql For Financial Reporting Periods This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year

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

Transact SQL :: Compare Event Time Value With Current Year And Month

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

Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year

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

Create WHERE Statement That Will Calculate Values From Current Fiscal Year To Last Complete Month

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

Reporting Services :: Displaying Previous Months On The Basis Of Start Date

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

Reporting Services :: Date To Be Displayed Below Particular Month

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

Getting Date In Day/month/year Instead Of Month/day/year

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

Reporting Services :: In SSRS Report Need To Limit Date Range To One Month

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

Extract Date,month, Year From The Date Getting From Sql Table

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

See If Date Is Same Year And Month And Day

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

Subtract A Year From Current Date

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

Date In Current Year Or Last Two Months

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

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

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

Want To Concatenate Year And Month To Get A Date

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

Want To Concatenate Year And Month To Get A Date

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

Last Year Month To Date Function

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

Transact SQL :: How To Get Month And Year For A Given Date

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

Only Month And Year From Date Time

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

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

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

Current Date Last Month

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

Date Query Problem Last 8 Days, Different Year From Current

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

How To Get Month And Year From A Date Which Is Of Type Varchar(20)

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

Extract Month & Year From Date Column

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







Copyrights 2005-15 www.BigResource.com, All rights reserved