Aggregate Data Grouped Lists
Apr 15, 2008
Hello,
I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).
Now I want to aggregate some numbers of the table on each page (year) of the list.
The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.
Can anyone help?
Thanks a lot -
Markus
View 2 Replies
ADVERTISEMENT
May 7, 2007
I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)
Ex.
UserClokingID UserID Time IsClockedIN
2323 34 8:32:03 TRUE
2324 34 12:07:02 FALSE
2325 34 13:05:03 TRUE
2326 34 14:53:02 FALSE
2327 34 15:10:03 TRUE
2328 34 17:32:02 FALSE
All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.
my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.
Is there any work around?
I really need to get this solved.
View 2 Replies
View Related
Apr 25, 2008
How to write Aggregate functions for tables and lists as If I can write them many problems in my reports will be solved. I tried writng it in the filters but I got an error saying Aggregate functions are not allowed for tables and lists. Can any one help me in this regard?????
View 4 Replies
View Related
May 15, 2006
Hi,
I'm trying to get data from WSS lists by web services. I think there are two ways:
- using SSIS standard components (Web Service Task) and
- creating a custom source adapter.
My problem with the Web Service Task is, that the WSS method GetListItems has some non-standard parameters (see part from wsdl-file below). Thus, after selecting the method in the editor an clicking on "Ok", I get the error message "The selected Web method contains unsupported arguments". I think the problem is the missing type-attribute.
<s:element name="GetListItems">
<s:complexType>
<s:sequence>
<s:element minOccurs="0" maxOccurs="1" name="listName" type="s:string" />
<s:element minOccurs="0" maxOccurs="1" name="viewName" type="s:string" />
<s:element minOccurs="0" maxOccurs="1" name="query">
<s:complexType mixed="true">
<s:sequence>
<s:any />
</s:sequence>
</s:complexType>
</s:element>
...
My question: Is there a way to extend the Task to handle such parameters?
View 1 Replies
View Related
Nov 28, 2005
Hi,I want to get the count of rows per each 20 minutes, is that possible?Is there a date function or any other function that I can use in Groupby clause, which will group the data of every 20 minutes and give methe count?Thank you.Vidya
View 3 Replies
View Related
Mar 12, 2007
Hello,
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset.
This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
Thanks in advance
Tom
View 2 Replies
View Related
May 20, 2007
Hi all,
This is probably one of the easier questions you get, but I have brain freeze and just can't do it and very rusty.
How can i assign the the values from sql below into variables e.g I want to get all new, pending and cancelled Leads from the rows returned which will come from the status id.
SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value
FROM [dbo].[tLead] L
Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID
Inner Join dbo.tClick C on linkClick = ClickID
WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year
Group by LeadStatusID
Order by LeadStatusID asc
Cheers
View 10 Replies
View Related
Mar 28, 2008
Hi All,
I need to do data transfer from three tables in SQL server 2005 to the single Excel sheet as a report. Even If I create a Lists or named range, I couldn't able to transfer the data into the Lists. The idea is, whenever the data is transfered into the Lists, the three Lists (or Range) having data should grow separately.
Currently the data is transfered out of the Lists.
Anyone can help me.
Thanks
Sanra
View 5 Replies
View Related
Jun 7, 2007
Hello,
I have the following situation :
I have a reportmodel where i have the a datetime value (dd/mm/yyyy hh:mms), a salespersonid , a salespersondescription, and an amountsold.
i would like to display a report where i can see per hour (start worktime is 8 am and end is 8 pm) which salesperson sells the most. the objectif of this report is to see in what period of the day the sales are highest.
Can anybody provide me some tips how to accomplish this.
Vinnie
View 1 Replies
View Related
May 12, 2015
I am trying to export report to .CSV , but I am unable to export grouped data, instead to grouped data , it exporting detail data,for example my grouped data in matrix is as below , I need to export below data to .csvÂ
column1 colum2   year      amount
1 Â Â Â Â Â Â 1 Â Â Â Â Â 2011 Â Â Â Â Â Â $1.0
        2      2012       $2.0
        3      2013       $3.0
              2014       $4.0
but, I am getting like below-
column1   column2   year      amount
1 Â Â Â Â Â Â Â Â Â 1 Â Â Â Â Â 2011 Â Â Â Â Â Â $1.0
1 Â Â Â Â Â Â Â Â Â 2 Â Â Â Â Â 2012 Â Â Â Â Â Â $2.0
1 Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â 2013 Â Â Â Â Â Â $3.0
1 Â Â Â Â Â Â Â Â Â 3 Â Â Â Â Â Â 2014 Â Â Â Â Â Â $4.0
View 2 Replies
View Related
Dec 12, 2007
Hi all experts,
I have a result set like:
Project
Milestone
Info
Project1
M1
Info1
Project1
M1
Info2
Project1
M2
Info3
Project2
M1
Info4
Project2
M2
Info5
Project3
M1
Info6
I need to create a report like:
M1
M2
Project1
Info1
Info2
Info3
Project2
Info4
Info5
Project3
Info6
But while I use matrix to build this report, I got the result like:
M1
M2
Project1
Info1
Info3
Project2
Info4
Info5
Project3
Info6
The report will not show the multiple records on the row group "Project" like item "Info2".
After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:
M1
M2
Project1
RowNumer
Info1
RowNumer
Info1
RowNumer
Info3
Project2
RowNumer
Info4
RowNumer
Info5
Project3
RowNumer
Info6
It would be much appreciated if anyone could give me some hints to complete the report I need.
Thanks in advance,
Steve Wang 2007/12/12
View 5 Replies
View Related
Dec 3, 2006
I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)
A sample time series would look like this:
11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT
The result of the SQL query should be:
Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45
This is the SQL statement I came up with but obviously doesn't work:
SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)
I would appreciate any suggestions.
Thank you!
View 10 Replies
View Related
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Jul 23, 2005
I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?
View 2 Replies
View Related
Jan 30, 2008
I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.
influent
View 1 Replies
View Related
Jun 13, 2006
I have three tables:
1) Contract - columns ContractNo and ContractName
2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value
3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost
How do I write a SELECT statement to produce rows containing:
ContractNo
ContractName
Sum of Value for ContractNo between @FromDate and @ToDate
Sum of Cost for ContractNo between @FromDate and @ToDate
Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.
I've written something like:
SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost
FROM Contract CT
INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo
INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo
WHERE (ST.InvoiceDate BETWEEN @FromDate AND @ToDate) AND (CS.TransactionDate BETWEEN @FromDate AND @ToDate)
GROUP BY CT.ContractNo, CT.ContractName
The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?
View 1 Replies
View Related
Mar 14, 2001
Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.
Here's what I'm trying:
select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id
but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).
I'm new to SQL and could really use the help.
Thanks
View 1 Replies
View Related
Oct 5, 2002
I recently upsized my acess 2000 db to SQL 2000, but I am now having errors . . .
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a nvarchar data type as an argument.
/data_count.asp, line 4
The code within the asp file:
<% dataSQL = "SELECT SUM(filesize) FROM DL where show=1"
Set datacount = my_Conn.Execute(dataSQL)
anBytes = datacount(0)
%>
Can someone point me to a solution?
Thanks!
View 3 Replies
View Related
May 12, 2014
I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId
Current Output
MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000
View 7 Replies
View Related
Jun 14, 2006
Hi
I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns.
I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table.
What all the steps i need to create in SSIS to get this results ?
Thanks
Kumaran
View 2 Replies
View Related
Aug 9, 2007
Hi all,
I have a scenario which I am not able to figure out how to do it better for quite some time.
Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22
My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid
Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22
Any advise would be much appreciated.
View 6 Replies
View Related
Feb 6, 2007
how to solve this problem ....
I want to select te sum of nvarchar column and this error always happen
what is the solution this is my query
"SELECT Datein AS Date,SUM(Durm) AS DurationMin, COUNT (*) AS 'Number of Hits' FROM Rayyan_Log WHERE (DNIS = '"+DropDownList1.SelectedItem.Text +"') AND (Datein between '"+TextBox2.Text+"' and '"+TextBox3.Text+"') GROUP BY Datein ORDER BY Datein"
View 2 Replies
View Related
Oct 11, 2007
SSRS/SSAS. I'm using aggregate function no problem in a Table, but when I try to use same function in data field of a chart I get no results. I know that "aggregate" has only been supported since SP2, is there a known problem with using it in charts?
Thanks
Richard
View 1 Replies
View Related
May 27, 2004
I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)
**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table
Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID
would be
Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33
View 1 Replies
View Related
Jul 20, 2005
Hi AllWe are running SQL Server & Outlook with an exchange server. We arelooking for a way to import the address book & email details into adatabase table.We have managed to do this with MS Access by using the import optionexchange(). Is there a similar way this can be done in SQLServer 2000
View 2 Replies
View Related
Aug 16, 2007
I'm new to SQL. I don't understand yet how to add if statements and other scripting techniques. But this is what I am trying to do...
I have a list of IDs (1, 2, 3, 4, 5, 6, 7, 8). I want to query a table and then display which of those IDs are NOT in that table. It seems like it should be easy, but I don't know how to do it.
Any help would be much appreciated.
thanks
scott
View 1 Replies
View Related
Jul 12, 2006
Dear All,
I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)
So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.
Not sure what would be the right approach here.
I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?
Will this work?
View 1 Replies
View Related
Jan 16, 2008
I want to grab the Max footage in this query, there are two values for each group, so i should be able to get the bigger number. The only problem is, its in varchar format, so for some reason its only grabbing the ones with the highest number to the left. So instead of grabbing 12ft, it says 7ft is the max. What am i doing wrong. is there another way to go around this. This is supposed to be a subquery for a a much bigger query.
Code Block
SELECT MAX(Qry_Questions.Response) AS Max_Footage,
CONVERT(varchar, dbo.Qry_Questions.Date, 110) AS shortDate,
Qry_Sales_Group.salesperson_purchaser_code,
Qry_Questions.StoreName,
MIN(Qry_Questions.Response) as Min_Footage
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer AS Customer
ON Customer.Customer_Code = dbo.Qry_Questions.Customer COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Category AS CusCat
ON CusCat.Customer_Category_Id = Customer.Customer_Category_Id
WHERE (dbo.Qry_Questions.[Question Code] IN ('SN017', 'SN015')) AND (CusCat.Customer_Category_Id = 6201)
GROUP BY CONVERT(varchar, dbo.Qry_Questions.Date, 110), Qry_Sales_Group.salesperson_purchaser_code, dbo.Qry_Questions.StoreName
This displays this:
Max Footage Date store name Min Footage
8ft 09-07-2007 12140 PETCO #1437 8ft
8ft 09-10-2007 12069 PETCO #698 8ft
8ft 09-11-2007 12106 PETCO #1916 8ft
8ft 09-11-2007 12108 PETCO #683 4ft
8ft 09-13-2007 12140 PETCO #918 12ft
View 5 Replies
View Related
May 14, 2007
Hi
I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.
Regards,
Karen
View 3 Replies
View Related
Dec 30, 1999
I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...
Loop from 0 to ListLength
begin
INSERT Transaction_Data
end
Thanks from an SQLS7 newbie,
jE
View 4 Replies
View Related
May 3, 2001
Does anyone know which SP to use to get the list of available servers?
I want to call it from a VB app via ADO to provide the user with a choice of servers available.
View 1 Replies
View Related
Mar 4, 2007
I am glad to announce that there is now a version of my article "Arrays andLists in SQL Server" for SQL 2005 available on my web site.THe URL for the article ishttp://www.sommarskog.se/arrays-in-sql-2005.html. If you are curious aboutthe performance numbers they are in an appendix athttp://www.sommarskog.se/arrays-in-sql-perftest.html.The old version of the aritlce remains, as the new article covers SQL 2005only. The old version is now athttp://www.sommarskog.se/arrays-in-sql-2000.html.The old URL, http://www.sommarskog.se/arrays-in-sql.html leads to a pagethat links to both articles.And the reason that there are two articles is simply that SQL 2005 addsso many new features: nvarchar(MAX), the CLR, the xml data type, CTE thatall can be used in the realm of arrays and lists.--Erland Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/pr...oads/books.mspxBooks Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodin...ions/books.mspx
View 1 Replies
View Related
