Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi

### Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

### Creating .NET Assemblies That Aggregate Data from Multiple External Systems for Business Connectivit

This article describes a quick, four-step process for creating a .NET assembly that BCS can use to retrieve external data for SharePoint Server 2010 by using Visual Studio 2010.

### Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

### About the Aggregate Function MIN

Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

### Aggregate only filtered members

I’m creating a measure in a cube as lookup from another cube: CREATE MEMBER CURRENTCUBE.[MEASURES].[Test]  AS     lookupcube("[Cube1]",     "([Measures].[PublicPrice],     [Cube1 DimDate].[TIME Hierarchy].[" + [Cube2 DimDate].[TIME Hierarchy].currentmember.name + "]     )")     If I select in filter only January 2010 and February 2010 in Cube1, for Quarter 1  I obtain 100; in Cube2  I obtain 150. Even if March 2010 is not selected in Cube2 the aggregation level (the Quarter) considers it. Could someone help me? Thank you very much, Giada

### What is best way to aggregate tasks in Intranet?

A lot of Intranet users are challenged to keep track of tasks across multiple team sites. Anyone find a technique that works? It seems like it would make sense to live in a user's My Site on SP2010.  Anyone doing it that way?

### Aggregate using update

Is this possible?  It seems trivial, but I have learned that doesn't mean there is a trivial fix.  I have a table that has an index, quantity, and total.  I want the total fields to be the sum of quantity per distinct index. (And no, I can't redesign the database from scratch, or change the way our organization stores data, etc.  These are the parameters I have to work within.) pIx    pValue    pTotalIX 1    2    0 (should be 9) 1    4    0 (9) 1    3    0 (9) 2    1    0 (7) 2    2    0 (7) 2    4    0 (7) 32    11    0 (74) 32    22    0 (74) 32    41    0 (74) Can I update the pTotalIX column without resorting to record-by-record processing, etc? Caveat - this is a table variable.

### Parent Child using Aggregate Functioon Multiplication

Hey There,   i do have a PC Dimension which i was able to report in MSRS as describe here http://liyasker.blogspot.com/2010/06/parent-child-hierarchy-in-sql-server.html http://blogs.msdn.com/b/robertbruckner/archive/2008/10/14/using-analysis-services-parent-child-hierarchies-in-reports.aspx http://www.mssqltips.com/tip.asp?tip=1939   So I got this working and my parents will be aggregated as SUM.   But what I want is a Multiplilcation RollUp.   So lets say we have the following structure: -A     =6       A will be calculate by B*C --B    = 2 --C    = 3    Currently the function I am using is the following, which works fine as aggregation for SUM =sum(Fields!SalesAmount.Value,"ParentChildGroup",Recursive)  So since x*y = e^(ln x + ln y) I tried to apply this with the following but its not working. It says Recursive is not declared! =exp(sum(log(Fields!Verkauf.Value,"ParentChildGroup", Recursive ))) Maybe i oversee something. Can anyone please point out how to use this method.  Thanks you so much.

### Aggregate Object Pass as a hidden value

How to use Html.Hidden for aggregate object?For Example, I have Model1 which aggregates Model2. Model:class Model1{.. public Model2 model2;..} class Model2{ public int id; public int name; .... //there are many fields//  } View:ViewPage is tied with Model1.ViewPage<Model1> I can pass Model2 values by specifiying its fields like<%= Html.HiddenFor(x => x.model2.id) %><%= Html.HiddenFor(x => x.model2.name) %> What I want is to pass Model2 as hidden value without specifying its each field. Any idea how to correct line below<%= Html.HiddenFor(x => x.model2) %>//I'm getting null value for model2 Thanks

### Is it possible to Aggregate tasks into SP 2010 My Sites

Is there an OOB way in SP 2010 to aggregate a user's tasks from across web applications/site collections where a user is a member of, into the user's my site?

Thanks.

### Can parition aggregation aggregate correctly at the edge of the partitions?

If I have a fact table at day level. I create a series of partitons on it based on month. Say, the first partition is for Jan, 2010. The second partition is for Feb, 2010 and so on. I have aggregates calculated on weekly level at each partition. If week 5 is spread over two partitions: 3 days in Jan/2010 partition and 4 days in Feb/2010 partition. Is week 5's aggregation data calculated correctly on Feb/2010 partition? In another word, will the aggregates pull the first 3 days from the Jan/2010 partition automatically or instead week 5's aggregation is not calculated at all?

Thanks a lot.

### Is it possible for a sharepoint VIEW to aggregate multiple TASK LISTS?

I have a few TASK LISTS, each with identical structure, that can't be combined initially, based on the groups that control the task lists.  ALL Task Lists reside on the same SITE.  I would like to find a way to "UNION JOIN" multiple task lists under a single Tasks List VIEW, if that can be done.  Alternately, is there a WEB PART that can do this?

Is there a way to merge multiple similar-structured task lists within a single site - and if so, how would it be done?

### Aggregate with Key problem

I have a couple of databases with the following problem listed below using the Adventure Works DB as an example.

The [Product].[Product Categories].[Product] aggregate is not aggregating correctly.  It looks like it is aggregating up to the all member no matter which way it is sliced, the numbers keep repeating.

Is there a way to fix this or an explaination as to why it is doing this?

```with

member [Product].[Product Categories].[All Cat] as
aggregate([Product].[Product Categories].[Category].members)

member [Product].[Product Categories].[All SubCat] as
aggregate([Product].[Product Categories].[Subcategory].members)

member [Product].[Product Categories].[All Prod] as
aggregate([Product].[Product Categories].[Product].members)

member [Product].[Product Categories].[All Sty] as
,[Product].[Style].&[U]
}))

select
(
[Measures].[Sales Amount]
,
{
[Product].[Product Categories].[All Cat]
,[Product].[Product Categories].[All SubCat]
,[Product].[Product Categories].[All Prod]
,[Product].[Product Categories].[All Sty]
}
) on 0,
(
[Product].[Style].members
) on 1

2N```

### Cannot perform an aggregate function on an expression containing an aggregate or a subquery

How do i get around this???

select

<

### SQL Query Help - Aggregate Functions Across Several Variables

I have a table that looks like this….

Size                  BillCode           Qty      Weight

4                      A                     3          230

4                      B  &nbs

### No applicable aggregate method 'Sum' exists

I am getting the subject error when using the sum in a LinkDataSource .  I can substitute the max(    and the groupby works ok.  Following is the LinkDataSource.  The environment is VS2008, SQL server 2008 on a Vista machine.

Thanks for any help.

```<asp:LinqDataSource ContextTypeName="VogasSQLDataContext"    TableName="TenKeys" GroupBy="new(checkNo,Purchaser,Property)"
Select="new(Key,
It As TenKeyEntrys,
Sum(Entry) As PropertyEntry)"
ID="ldsTenKeyEntrys"
runat="server">
</asp:LinqDataSource>```

### aggregate data source update data

Hi,

I've been building .aspx pages in sharepoint designer using the aggregatedatasource to perform calculations referencing data from different lists and displaying the calculation results to the user.

I'd like to build a form that combines user alterable fields in one list, and display calculation results based upon the primary list and several other reference lists.

Does anyone have a post or link to an article showing how to do this?

i.e. I have fields that the user can input data into, click [Save], the form refreshes displaying the new calculation results which read data from many different lists.

Best regards,

s'

 Categories:
 ASP.Net Windows Application .NET Framework C# VB.Net ADO.Net Sql Server SharePoint Silverlight Others All