For Each Year Group In Matrix Return Last Quarter's Data

Sep 12, 2007

Hi, I have a matrix report with three data points
1. Inventory
2. Occupancy
3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

View 6 Replies


ADVERTISEMENT

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

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

Apr 30, 2007

Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.

View 4 Replies View Related

Analysis :: Get Max Quarter Of A Year MDX?

Aug 18, 2015

There is a way to get the non empty max calendar quarter of the year and the last year. For example, the max calendar quarter of the last year should be 4, and in this moment the max quarter of this year should be 3.

I am building a report in SSRS and trying to avoid query the DWH database. I want to run every query against the cube.

View 3 Replies View Related

SQL Server 2012 :: Rollover Prior Quarter Data To New Quarter In A Table - Output Identity Values

Jun 5, 2014

I have a process to rollover prior quarter data to new quarter in a table.

For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1

Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2

Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.

Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like

1,4
2,5
3,6

I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.

View 9 Replies View Related

Sum By Week / Month / Quarter / Year

Aug 20, 2014

I am trying to group counts by week,month,quarter, year for a particular activity type and I'm having issues.Here's my code so far:

SELECT
distinct
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN
SUM(CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) END AS WeekTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress
Desired Output:
EmailAddressId EmailAddress WeekTotalOpens MonthTotalOpens etc. then WeekTotalClicks and so on....

SQL doesn't seem to like the sub-aggregate. What is the best way to approach?

View 2 Replies View Related

Getting Year/quarter/month/day In The Right Order (was Dates)

Feb 28, 2005

I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week

When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!

any help would be grateful!!! not sure what I should be looking at.....

View 14 Replies View Related

Char(4) Or Integer For Year / Quarter Field

Sep 3, 2007

Hello,

what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

Both are part of a composite index.

Thanks
Silas

View 6 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

Calculating Average Count By Day / Week / Month / Quarter / Year

Aug 18, 2014

I need developing a query to get the average count by the following:

Day - use daily info for the last ??? days

Weekly - average
- Add all days and divide by 7
- As of Saturday midnight

Monthly - average
- Add all days and divide by days in the month
- As of last save on last day of month

Quarter - average
- Add all days and divide by number of days in the quarter
- As of last day of quarter

Year - average
I don't have requirements for year as of yet.

How can I get the avery count per these timeframes?

View 7 Replies View Related

Analysis :: How To Find Quarter / Year / Month / Semester Start And End Dates In MDX

Sep 24, 2015

I have a date dimension set in the SSAS Cube. I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.

View 2 Replies View Related

Collapsing/Expanding Group Data In Table And Matrix Data Regions

May 30, 2007

Hi,



Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?



Thanks.

View 1 Replies View Related

Group By Financial Quarter

Dec 4, 2007

I have a transaction file with Company_code,Gl_code,Amount,Transaction_Date

I want to sum the amount based on a Company_Code,Glcode for every financial quarter(01/04/2007-30/06/2007,01/07/2007-30/09/2007 and so on).

Expected Output

Company_Code,Gl_code,Quarter_1,Amount_1,Quarter_2,Amount_2,Quarter_3,Amount_3,Quarter_4,Amount_4

Regards

Nirene

View 6 Replies View Related

SQL Server 2012 :: Compare Current Year Data With Last Year Data - Day Adjusted

May 25, 2015

Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.

I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.

View 5 Replies View Related

Creating A Matrix With To Compare Same Period / Year

May 21, 2014

having a problem with no solution at hand: Reading data in a YYYYMM for which it has a data value (Forecast)so data in:

201201 10
201202 5
all up to 201212
201301 11
201302 6

I would like to create a matrix to a screen that does :

Month -> 01 02 03
2012 10 5
2013 11 6

Tried group by, tried everything, i think The final solution is to create another file with one record per year.

View 7 Replies View Related

Reporting Services :: Matrix - Percentage Growth Same Period Previous Year?

Aug 4, 2015

I'm trying to creating the following matrix in SSRS 2008R2 (with more product categories than shown here)The matrix only shows the last two year per product.
                           
The matrix in the report builder looks like this:

For me it's unclear what the expression should be. (or perhaps I need to make a calculation in my TSQL?)

As Row Groups in the matrix I've got: 

Product; Year

As Column Groups i've got Month

I can't get it to work. Previous doesn't seem to work in this case (only when I got a total of each year (e.g. one column instead of 12))

View 6 Replies View Related

Group By Month,day,year..

Jan 9, 2006

hi
I tried below but it doesnt work

select date_field from tbl group by DAY(date_field)

View 1 Replies View Related

Group By With Year Function

Jun 19, 2014

select empid,orderdate from [Sales].[Orders] WHERE custid = 71
group by empid,orderdate

sample data resultset:

empid orderdate
1 2006-12-25
1 2007-12-24
1 2007-12-23
1 2008-12-23
1 2008-04-24

select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)

sample data resultset:

empid orderdate
1 2006
1 2007
1 2008

why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.

View 3 Replies View Related

Group By Month And Year Transact SQL

Feb 13, 2001

I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.

View 1 Replies View Related

Trying To Group && Subtotal By Year+Month

Feb 20, 2008

Using SQL 2000 Report Designer & Visual Studio 2003 and a report for MS CRM 3.0

I need to group by Year + Month, like now would be "2008 02". Am using the FilteredOpportunity.estimatedclosedate.
I have tried using YEAR(FilteredOpportunity.estimatedclosedate)+MONTH( FilteredOpportunity.estimatedclosedate), but does not work. Also YEAR( FilteredOpportunity.estimatedclosedate.Value). I don't store "2008 02" type data in the CRM.

Any ideas?

View 3 Replies View Related

Return Number Of Days By Year From Date Fields

Dec 3, 2013

I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.

View 2 Replies View Related

T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month

May 11, 2015

I have table in which month & year are stored, Like this

Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015

I wanted a query in which it should return the value in a status field which has latest year & month.

View 9 Replies View Related

SQL Server 2008 :: Querying Last Quarter Data

Feb 26, 2015

We have this query that pulls number of days worked from the current Quarter to Date.

(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],

I need changing it to bring up LAST Quarter's data.

View 1 Replies View Related

Trying To Group Report Results By Year And Month By Other Variables

May 28, 2008

Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END

View 6 Replies View Related

Return Most Recent Membership Based On Year Stored In Joined Table

Jul 23, 2005

Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken

View 4 Replies View Related

Data Warehousing :: Order By Not Working For Quarter Years

Sep 30, 2015

Expected

[care session quarter]
Q3-14
Q4-14
Q1-15
Q2-15
Q3-15

Currently the output am getting shown below

care session quarter
Q1-15
Q2-15
Q3-14
Q3-15
Q4-14

I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in  the select clause and Group by clause then it comes correctly but groups by all dates which is not required.

Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter]  which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below

Column_name
DATE_KEY
temp_date
DATE_NAME
DATE_WEEKDAY_NAME
DATE_IS_WORKDAY
DATE_NUMBER_IN_WEEK

[Code] ....

View 2 Replies View Related

Add Space Between Matrix Row Group

Jan 30, 2007

I have a 2 row groups in a matrix and I need to add some space between the top one so that all the columns don't run together. Is there a way to do this?

View 11 Replies View Related

SSRS - Matrix Row Group

Feb 19, 2008

I have a matrix with six rows and the column is dynamic depending on if any data exists for any row item in a month/year. All rows are summations of data for the particular month. I have a row group that when toggled to visible expands all the rows. Is there a way to restrict the row group to only expand the data in a specific row?

View 1 Replies View Related

What You Mean By Static Group In Matrix?

Jul 21, 2007

What you mean by static group in matrix?

View 2 Replies View Related

Dynamic Row Group In Matrix

Oct 9, 2006

Hi All !

I want to show row groups as hierarchy levels and need the
sub total values belongs to each group and sub group levels. But the
most important point is that my top next top group (from child to
parent ) is not static its dynamic.i.e for a diffrent senario my under
displayed example can have Universe--->Earth as parent for Australia and USA.



eg:

1.Australia

|-------sydney

|-------Melbourne

2.USA

|------North US


|------North US(1)


|------North US(2)

|------South US


|------South US(1)


|------South US(2)



Can I get some help from anybody for making a dynamic row groups in the matrix.

Waiting for a kind help.

Regards,

View 5 Replies View Related

Matrix Group By Excludes Last Value

Mar 28, 2008



I have a Matrix that counts records by one group. In a simplified scenario, the group values are 1, 2, and 3. When I created the matrix without a subtotal, all 3 groups are returned. I added the subtotal column, and now only 2 of the groups are returned. The dataset query looks like this:
SELECT A

, B
FROM Table

Without Subtotal this is returned:
A CountOfB
1 3
2 6
3 7

With Subtotal this is returned:
A CountOfB Subtotal
1 3 3
2 6 6


The last value is missing. When I remove the subtotal column, the last value does not return. Where did it go? Am I missing a setting?


Thank you so much for reading this. Any help would be appreciated.
babs

View 1 Replies View Related

SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format

Nov 6, 2015

I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.

Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.

select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY

View 9 Replies View Related

How Do You Display Headers In The Row Group In A Matrix??

Jun 26, 2007

Hi, I am creating a matrix and adding multiple row groups. The problem is that I am unable to display headers for each of these groups - can anyone help??

View 6 Replies View Related







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