Grouping Data Into Periods For Reporting

Aug 17, 2007

Hi there.

I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:









Period
Sum

May '07
41

April '07
14

Q2 '07
55

March '07
36

February '07
28

January '07
22

Q1 '07
86

June '07
N/A

YTD '07
141

December '06
33

November '06
27

October '06
42

Q4 '06
102

September '06
58

August '06
84

July '06
52

Q3 '06
194

June '06
40

May '06
41

April '06
14

Q2 '06
95

March '06
67

February '06
38

January '06
N/A

Q1 '06
105

YTD '06
496


For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?

Thanks, Mike

View 4 Replies


ADVERTISEMENT

Reporting Services :: Grouping Data In A Field?

Jun 15, 2015

I am creating a report that shows orders created by our employees but I am having trouble with one employee.  She changed her last name this year and so in the report, it shows a line her with her maiden name and one with her new name.  

Is there a way to combine all the data from both names into one name so it appears on one line and looks like a single person instead of two. 

View 4 Replies View Related

Grouping / Reporting Question

Oct 11, 2007

Hi,

I've written a series of procedures that are writing output lines into a common table that I'm then reporting on.

I've included some simplified code to show waht I've done.

I was hoping someone could tell me if it is possible to remove the loop that I've created to get the report headings.

I've tried to write this as a with statement, but can't quite get it right.


--------


CREATE TABLE JobReport(
JobRunId int NOT NULL,
ReportDate datetime NOT NULL,
Process] varchar(200) NOT NULL,
Line varchar(200) NOT NULL
)


INSERT INTO jobreport(jobrunid,reportdate,process,line)
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A2' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A3' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c4' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d3'


SELECT IDENTITY(INT,1,1) id,headings.Process INTO #headings from
(
SELECT Process FROM jobreport
WHERE jobrunid = 1
GROUP BY Process
) headings


BEGIN
DECLARE @max INT, @COUNT INT,@heading AS VARCHAR(200),@report VARCHAR(MAX), @jobrunid INT
SET @jobrunid = 1
set @report = ''

SELECT @max = COUNT(*) FROM #headings
SET @count = 0

WHILE @count < @max
BEGIN
SET @count = @count + 1

SELECT @heading = h.Process FROM #headings h WHERE h.id = @count

SELECT @report = @report + '----' + @heading + '----' + CHAR(13)
SELECT @report = @report + line + CHAR(13)
FROM jobcontrol.dbo.jobreport rep
WHERE rep.jobrunid = @jobrunid
AND rep.Process = @heading
END

--------

any help would be appreciated.

thanks



Sean

View 4 Replies View Related

Grouping With Reporting Services

Feb 7, 2007

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .



-Thanks all

View 1 Replies View Related

Reporting Services :: Grouping All Instances Of ESI Together

Apr 23, 2015

I have created a Table within SQL Server Report Builder, and the first field is a calculated field with the following code:

=Code.ChangeWord(Fields!NAME.Value)

Where the function is

Public Function ChangeWord(ByVal s As String) As String  
Dim strBuilder As New System.Text.StringBuilder(s) If s.Contains("ESI  ") Then     
strBuilder.Replace(s,"ESI")     
Return strBuilder.ToString()     
Else : Return s   End IfEnd Function

Now, when I execute this, it works beautifully ... except that the Grouping mechanism doesn't group all instances of "ESI" together, like I want it to.

How do I change this behavior so that it groups all instances of "ESI" together, like it should?

View 3 Replies View Related

Reporting Services Grouping Issue

Dec 4, 2007

Hi,

I want to create a number of Groups in a table.
Within each group I want to display that group's data details.

So far I seem to be only able to create another SubGroup within a Group.

I want my data to display as follows(using Drilldown)...

ItemA
ItemA Details
ItemA DetailsExtra
ItemB
ItemB Details
ItemC
ItemC Details
ItemC DetailsExtra



thanks.

View 7 Replies View Related

Reporting Services :: Column Header Over Grouping

Aug 11, 2015

I would like to create a group and add an overall header.

So the dimension is grouped and results in the following:
                                  Group Title
Dimension 1    Dimension 1    Dimension 1    Dimension 1

Instead of :
Group Title    Group Title    Group Title    Group Title
Dimension    Dimension     Dimension    Dimension

How can I achieve this without using a tablix within a tablix?

View 6 Replies View Related

Reporting Services :: Reference Column Grouping

Aug 31, 2015

I create a matrix,

Row groups: Province
Column groups: Project Type
Values:  Count of # of Projects, Sum of Amount ($)

What this displays is the following. How do I go about calculating what's highlighted in yellow? It is Amount of OneProjectType / Amount of TwoProject Type.

View 5 Replies View Related

Reporting Service - Grouping - Page Break At End

Jan 23, 2008

Have anyone came across an issue with Reporting Service 2005 where if you had more than 1 group within a table and the first group has the property 'Page Break at end' checked - this will cause a page break after the group footer was printed.

Usually this is not an issue but consider this scenario - the 'Page Break at end' is set against the second group (not the first group which show the overall total) - when the last footer was printed for the second group, this cause a page break before the first group (overall) is printed - it seem to me that Reporting Service is not able to know when not to generate a page break if there isn't any more data - more importantly - there dosen't appear to be a way to set an expression against 'Page Break at end' within the group

Does anyone know of this issue and is there a workaround it?

View 3 Replies View Related

SQL Reporting Services 2005 - Grouping One Field In A Row

Jan 9, 2008

Is there a way in Reporting Services layout mode to get the report to display more than one response for one field in the same cell?


More specifically, I have a list of classes that lists section, title, and instructors. If the class has more than one instructor, I want the instructor cell to list both instructors in one cell, separated by a semi-colon, rather than listing all the class information - along with instructor - in two separate rows. Any suggestions?



something like this:




Section
Title
Instructors

15
Class 1
Jones

16
Class 2
Jones; Smith

View 5 Replies View Related

Reporting Services :: Customize Column Grouping

Jul 6, 2015

I am working on one of the report. where I need to move the amount across column group.

My data is like this

Project Amount CurrentFiscalMonth DelaybyMonth
A 10 Jul 0
B 20 Aug 2
C 10 Sep 0

Report will be sum up the amount at CurrentFicalMonth grouping on column

But, if the Project is being delayed say by as per the example its 2 months than the amount should be moved from Sep to Oct column for the particular project. User has done it using the Offset function in excel.

Fiscal month starts at July and ends at June. and data source is SSAS cube.

View 4 Replies View Related

Reporting Services :: SSRS Grouping And Page Break

Mar 5, 2014

Example: A sales report that details and summarizes each Salesperson’s total sales within a company’s stores that reside in each state. 

The report has 3 levels of groupings where the highest 2 levels require summary counts on a separate page and the lowest level produces a summary count in the midst of the detail lines of the report.

The data consumed by the report is ordered by State, Store ID, and Salesperson. 

The header of each page lists the State and Store ID.  The detail lines of the report list items sold and the associated sales amount for each item sold by each store’s salesperson. 

At the end of each Salesperson’s items sold, a summary line is listed on the next line in the report showing the total amount and total count of items sold by that Salesperson. 

The very next line on the report lists items sold and the associated sales amount for the next Salesperson within that particular store. 

Summary lines will be produced for each Salesperson within each particular store on the next line of detail in the report.

At the end of all sales data for each store, a summary page is produced on a separate page listing the summary of each Salesperson’s totals. 

Each line of the summary page contains the same counts for each Salesperson that was embedded in the detail section of the report. 

The summary page also contains a grand total line listing the total count and sales for all salesmen within each given store.

At the end of all sales data for each state a summary page is produced on a separate page listing the summary of each store’s totals. 

Each line of the summary page contains the grand totals of all sales for each store within a given state. 

The summary page also contains a grand total line listing the total count and sales for all stores within each given state.

The simple breakdown is the groupings and totals for each state and store must be listed on a separate page in the report. 

The grouping and totals for each Salesperson must be listed on the next line within the detail section of the report. 

Current issue: The requirement is for no page break at the end of each salesperson’s sales data because the page breaks produce too many extra pages in the report. 

Disabling the page break at the Salesperson level produces format issues such as page headers printing on the next line in the report before each Salesperson’s summary line. 

Disabling the page breaks at this level also produces the problem of suppressing the page break for the summary page that comes at the end of each store.

Question:The report has 3 levels of grouping with Salesperson at the lowest level, Store ID being the parent group of Salesperson, and State being the parent group of Store ID.  Is it possible to format the Salesperson summary line (lowest level grouping) as just another detail line in the report without impacting the report format that requires page breaks at the parent group levels?

A sample report layout of what we are trying to achieve is on the next page.
 
Sales Report
State: Alaska                         
Store ID: 100

Item Sold               
Sales Amount
Hammer                
$10.00
Saw                        
$15.00

[Code] .....

View 2 Replies View Related

Reporting Services :: Ranking For Row Grouping In SSRS Report

Apr 28, 2015

I want to display data in following format in ssrs report. 

Rank Group  Value
1        G1      10
2        G1      20
3        G1      30
4        G1      40
1        G2      25
2        G2      54
3        G2      64

I tried row count and other ranking functions but it did not working.

View 3 Replies View Related

Reporting Services :: Tabular Report Datasets - Grouping On Column

Jun 23, 2015

I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .

I would like to create a tabular  report  datasets on the report like shown below with grouping on col1

like
col1  some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------

Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.

View 2 Replies View Related

Reporting Services :: Failed To Evaluate Filter Expression Of The Grouping

Sep 23, 2015

I am trying to add a filter and sorting to a matrix, within the matrix I have a column which works out the percentage and is as follows

=(Sum(Fields!Sum_Distinct_JobBilledExVAT.Value) + Lookup(Fields!ClientId.Value, Fields!JobClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfJobClientsFees") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsFeesID")) / (Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_MatchedToOtherPost.Value, "ProfClientsMatch") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsCC") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value,

[Code] ....

I tried enclosing the query in Abs( ), which I have done in the past, although in the past I wasn't using Lookup functions so I don't know if that is the reason it won't work.

View 6 Replies View Related

Reporting Services :: Multi Level Grouping And Page Break?

Jul 23, 2015

SSRS 2008 R2

I have two level of grouping  Example:

:::HEADER:::     
A [GROUPING 1]
B[GROUPING 2]
C[GROUPING 2]

When I apply Page Break

:::HEADER:::     
A [GROUPING 1]
                B[GROUPING 2]
:::HEADER:::     
A [GROUPING 1]
                C[GROUPING 2]

[URL]

View 3 Replies View Related

Reporting Services :: Develop SSRS Report Based On Grouping Of Certain Columns

Sep 1, 2015

I want to develop a ssrs report which is grouped by month?

View 4 Replies View Related

Reporting Services :: SSRS 2012 - Page Break With Column Grouping

Nov 16, 2015

We are facing problem in doing page break with column grouping. Our column group contains years e.g 2011, 2013 . We want to show a complete page for a year. 

Suppose 2011 has 10 records(horizontal) and 2013 has 12 records(horizontal) in column. The output should be 10 records of 2011 in first page, 12 records of 2013 in second page.

We cannot change the report layout to make column to row and vice versa.

View 3 Replies View Related

Reporting Services :: SSRS 2008 R2 / Grouping Multiple Tables (tablix) With Different Datasets

Mar 27, 2012

We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.

If we run the report passing only a single value for the grouped parameter then the report works perfectly.  What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between.  Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:

Table 1:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
....

Table 3:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...

But we want it to render like this:

Table 1:
Detail rows for Group Param Value 1...
Table 2:
Detail rows for Group Param Value 1...
Table 3:
Detail rows for Group Param Value 1...

[code]....

The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.The report must export cleanly to excel and currently does for the single value passed.

View 5 Replies View Related

SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data

May 7, 2014

I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.

So here is the DDL, sample data, and the ideal return rows

CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,

[Code] ....

I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.

SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1

One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.

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

Need Help In Grouping The Data

Jul 6, 2004

Hi all,
Hope u could help me

I have a table as follows

SupID | Week | ..| ........
-------------------------
234 1/2/03
235 1/2/03
236 2/2/03
237 2/2/03
238 2/2/03
239 3/2/03

and

I need to display theses data like

1/2/03(week)
-------------
234
235
2/2/03(week)
-------------
236
237
238
3/3/03(week)
-------------
239

If i go for Group by clause (SQL) then I can group them by week but cant display the individual rows.
Is there any way to do this (better if in a single query)

Thnx

View 3 Replies View Related

Grouping Data

Mar 11, 2004

some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table

ex:-
Id Name Value
--------------------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3

I need it in this way

x y z
------------
a1 b1 c1
a2 b2 c2
a3 b3 c3


(no of columns in the new table can't be pre determined)


and which one would be better option to do this
in VB.Net code or in a Storedprocedure?

View 14 Replies View Related

Grouping XML Data

May 21, 2014

I am joining multiple tables and my result is following:

UserGroup UserName
UG1 Tom
UG1 Harry
UG2 Albert
UG3 Jim
UG3 Sam

I want xml in following format:

<UserGroupsInfo>
<UserGroups>
<UserGroup name="UG1">
<User>
Tom

[Code] ....

I have tried all combinations of for xml path but no success yet.

View 7 Replies View Related

Grouping Data

Mar 26, 2008

I have a set of data that contains duplicates and I am running a group by query to get a unique set of rows.

The group by works upto a certain state, but I need to be able to tell it to take the first appearance of an address field as you can in Access?

current query is:

select ID,Address1,max(value)
from test
group by id,address1

I have tried using First(Address1) but it doesn't recognise that as a function?

View 3 Replies View Related

Grouping Data

Feb 9, 2007

Hi Exports

I have a simple question, but I can't seem to get around it. Please help.

I have a table like this: [select * from Enrollment]
Course ID PersonID Role
BCC110 123 Student
BCC110 321 Student
BCC110 456 teacher
BCC123 457 Student

and I want to have a report like

Course ID Total Students Total Teachers
BCC110 2 1
BCC123 1 0

How do I achieve this?

Sam

View 8 Replies View Related

Data Grouping

May 8, 2007

hi,



I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".

I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.



Now i want something like this.

Group my expenditure in three categories like

1. My expenditure on monday

2. My expenditure on tuesday

3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)



what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.



how can i do this?



Thanks

rohit

View 2 Replies View Related

Grouping Data

Feb 8, 2008

Hi Everyone,

I am trying to develop a report query that will dynamically group the data by day,week, quarter, month, or year. The user will select the interval from the site and pass it to the query. The query will convert the date field using a function I created. Is this the most efficient way to do this?

QUERY
------------------
SELECT dbo.FormatDate(DateCreated,3) As DateCreated, Count(*) As NumOfCalls
FROM Agent.LeadTracker
WHERE DateCreated BETWEEN '1/1/2008' AND '2/7/2008'
GROUP BY dbo.FormatDate(DateCreated,3)



RESULT
------------------
DateCreated NumOfCalls

2008-02-01 5442
2008-01-01 14150




FUNCTION------------------
CREATE FUNCTION [dbo].[FormatDate]
(
@Date datetime,
@Time int
)
RETURNS datetime
AS
BEGIN
DECLARE @Res datetime
IF @Time = 1 --Day
SELECT @Res = DATEADD(day,DATEDIFF(day,0,@Date),0)
ELSE IF @Time = 2 --Week
SELECT @Res = DATEADD(week,DATEDIFF(week,0,@Date),0)
ELSE IF @Time = 3 --Month
SELECT @Res = DATEADD(month,DATEDIFF(month,0,@Date),0)
ELSE IF @Time = 4 --Quarter
SELECT @Res = DATEADD(quarter,DATEDIFF(quarter,0,@Date),0)
ELSE IF @Time = 5 --Year
SELECT @Res = DATEADD(year,DATEDIFF(year,0,@Date),0)
RETURN @Res
END

View 4 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Grouping On Data Change

Feb 13, 2012

I am trying to create a group on this data example

RecordNo, Item, Date,NULL
1, ABBB, NULL, 0 << first group
2, ABBB, 01-06-2011,NULL << first group
3, ABBB,NULL,NULL << 2nd group
4, ABBB, 02-01-2011,NULL << 2nd group
5,CAAA,NULL,NULL << 1st group
6,CAAA,NULL,NULL << 1st group
7,CAAA,01-01-2010,NULL << 1st group
8,CAAA,01-01-2011,NULL << 2nd group
9,CAAA,01-05-2012, NULL << 3rd group

Basically 3 groups on date, by item, so for each date change/ per part the group increments been playing with row_number partitions, but cant seem to get what i need.

View 14 Replies View Related

Grouping Dissimilar Data

Jan 16, 2007

I€™m trying to build a report that contains bank account activity. For 3 customers their activity is this:

Deposits
Date Amount
John 1/2/2007 500.00
1/7/2007 250.00

Mary 1/3/2007 100.00

Withdrawals
Date Amount
John 1/3/2007 100.00

Mary 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00

Sam 1/6/2007 50.00

I would like the report to have deposit/withdrawal info, with subtotals, grouped by customer, looking something like this:

Deposit Withdrawal
Cust Date Amount Date Amount
John 1/2/2007 500.00 1/3/2007 100.00
1/7/2007 250.00
750.00 100.00

Mary 1/3/2007 100.00 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00
100.00 350.00

Sam 1/6/2007 50.00
0 50.00

Because the deposit and withdrawal data is only related by customer, I€™ve broken it into 2 datasets.

I€™m totally stumped! Can someone get me going in the right direction on this?

Thanks

View 1 Replies View Related

Grouping Data Problem

Sep 25, 2006

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit £100 - credit £100 - debit of £125 ( I only want to see the debit of £125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

View 4 Replies View Related

Reporting Services :: Grouping A Table Inside A Table Cell - SSRS Report

Jun 10, 2015

I have a report where in I want to show each record on a separate page.

So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.

Now, I also have to show a  sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.

When I preview, it throws this error.

"The tablix has a detail member with inner members. Detail members can only contain static inner members."

What am I doing wrong? How can I achieve table grouping inside a table cell?

View 2 Replies View Related







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