String Aggregation In SQLCompact

Nov 22, 2007

Is this possible in any way? Basically I have a standard pair of tables in a master/detail relationship. I need to somehow be able to query the child table for all children related to a parent record and return some sort of aggregation of a certain column. The problem is that I still need to know what each value was, so it can't be something like a SUM or an AVG.

As if that wasn't complicated enough, this will be done on 1000+ records at a time and is being used in a binding situation.

I've figured out how to do this with standard SQL Server with some dirty FOR XML hacks, but I'm beginning to think that it's not in any way possible with SQL Compact.

Any thoughts?

Thanks.

View 1 Replies

SQL Server Management Studio And SQLCompact

Jan 3, 2007

Hi,

just wondering if there are any plans to have SQLServer Management Studio Express connect to SQLCompact databases - given express and compact are both free products it would seem to make sense. Or am I missing some configuration setting that already allows SSMSE to do this - dont seem to be able to connect to a non SQLExpress datasource?

thanks,

Roger

View 1 Replies View Related

I4 To I8 In Aggregation

May 30, 2007

I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.

Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.

I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.

I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?

View 1 Replies View Related

GROUP BY Without Aggregation -- Is It Possible ?

Jan 14, 2004

Here is what I want to do.

I have a database with HotelInfo

hotelid
hotelname
hotelstate
hotelcity
hotelphone etc etc

I want to display all hotels I have grouped by state

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate

So basically I was For eg.

TX
hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx

CA
hotel2 San Fransisco xxx-xxx-xxxx

How can I group by without Aggregation ?

View 5 Replies View Related

Aggregation Query

Feb 21, 2003

Hi.
i have a problem that related to aggregation functions.
i have a table (requests)that consist with the following fields:event_id, request_type,data.
i want to present a report that for each event_id it will show the number of records where the request_type value is:"VMR".

i wrote the following query-
SELECT event_id, Count(request_type)
FROM requests
WHERE ((requestType)="VMR")
GROUP BY event_id

the problem is that:if event_id don't has a record with the value "VMR" it will not be showen in the report and the goal is to present those kind of events with the value 0.
who can i make it happen.

thanks.

View 6 Replies View Related

SQL Aggregation Poser

Jul 20, 2005

Can anyone help me with an problem I have come across in my databasedesign.I have a primary table and a related table with 3 child records (eachwith a numeric field). I require a query to return the primary keyfrom the main table and the PRODUCT (i.e. all numeric valuesmultiplied together) of the three child records, much like a SUM wouldadd them together.Any help would be gratefully received!Tony.

View 3 Replies View Related

Sum And Aggregation In A Matrix

Jun 14, 2007

Hello,

I have a matrix where I count number of employees per country, per product line.

The calcul is a simple sum :

(sum(Fields!ID__T_E_Employee_Distinct.Value)).

My report is correct at week level:

March 2007

W9 W10 W11 W12 W13

Product_Line1 17 17 17 17 17

Country Product_Line2 4 4 5 5 5

Product_Line3 25 25 26 25 25

Product_Line4 12 12 12 12 12

However my report is false when I drill-up to month level.

I obtain:

March 2007

Product_Line1 85

Country Product_Line2 23

Product_Line3 126

Product_Line4 60

but I want to obtain:

March 2007

Product_Line1 17

Country Product_Line2 5

Product_Line3 26

Product_Line4 12

For information, my datasource is an OLAP cube.

I have tried to add scope in the sum function but it doesn't work...

Moreover the sum is mandatory for me at week level, but I can have max of the sum at month level.

How to do max of sum in SSRS (I can have only one aggragation function in an expression).

Any help is welcome.

Thanks.

Guillaume.

View 2 Replies View Related

Problem With An Aggregation

Aug 6, 2007

Hi,

I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.

Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.

From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.

I hope I managed to explain my problem

**********************************************************************************************
Manager Region Market Company Meeting Detail

+Manager 1 (9 meetings)

+Manager 2 (37 meetings)

- West (37 meetings)

-Denver (37 meetings)

+Company 1 (5 meetings)
+Company 2 (2meetings)
+Company 3 (2meetings)
+Company 4 (3meetings)
+Company 5 (0meetings)
+Company 6 (0meetings)
+Company 7 (5meetings)
+Company 8 (1meetings)
+Comapny 9 (19meetings)
+Company 10 (3meetings)
Total (40 meetings)

View 5 Replies View Related

Aggregation Usage

Apr 10, 2008

I know we can design Aggregation usage for a dimension within a cube. For example, you can choose Full , None, Unrestricted and Default method. I assume this is the Global settings.

Is it possible that Ican do the same thing for the attributes within a dimension? I cannot find such property in attributes. Does this means we can't control which attributes we want Analysis Services to aggregate in a dimension?

View 6 Replies View Related

Aggregation Table

Jul 3, 2007

Hi all

i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS

thanks & regards

View 1 Replies View Related

Smart Aggregation

Jan 1, 2007

I'm having problems implementing the following in reporting services 2005.

My hierarchy looks like this (just to illustrate the problem...):

University->Student->Exam

My query returns the following fields:

University,Student,StudentPayment,ExamName,ExamScore

I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.

The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.

If only I could set the granularity level of the StudentPayment measurement...

Note that I don't have access to the query, so I can't change anything on that front.

Thanks,

Efi

View 6 Replies View Related

BIT-Wise Aggregation

Oct 29, 2006

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

View 6 Replies View Related

T-SQL (SS2K8) :: PIVOT Without Aggregation

Jun 28, 2015

I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:

DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));

If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.

TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.

I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:

OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53

[code]...

But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.

View 2 Replies View Related

Measure Displayed Without Aggregation ?

Sep 27, 2007

Hello,

I have a Sales cube and I want to be able to display Products and the
Price at which they were purchased in a given period. I have a
Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price.

My Product Dimension consists of product_numbers (such as 100, 101,
102 etc...)
My Sales Fact Table consists of sales data (such as product_key,
price, net_sales, returns, etc...) at the transaction level.

I want to be able to view the data like this:

Price Net_sales Returns
Product
100 $5.99 $2005 $320
101 $3.51 $7110 $998

where Net_sales and Returns are "summed" and Price is simply a "Group
By". In other words, this report would show the net sales and returns
by product for a given price.

I'd rather not use a Price dimension since we have hundreds of
products at hundreds of different prices. Moreover, this data is
already in the Sales Fact table.

Thanks for any help provided

View 1 Replies View Related

Class Does Not Support Aggregation

Feb 7, 2006

I am trying to port a database from SQL Server 2005 Express to SQLServer 2005 Standard. Idetached my database from the SQL Sever 2005 Express instance andattached it to the SQL Server 2005 Standard instance. Whenever I do aselect on tables in the database I have no problem. However if Iattempt to open or modify a table by selecting open or modify from thepopup menu, I get the following error --TITLE: Microsoft SQL Server Management Studio------------------------------Class does not support aggregation (or class object is remote)(Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))(Microsoft.SqlServer.SqlTools.VSIntegration)------------------------------BUTTONS:OK------------------------------I tried repairing the .NET 2.0 framework but to no avail.Please help!!!Thanks,

View 3 Replies View Related

Pivot Table Without Aggregation?

Mar 29, 2007

Hello, I have a resultset as follows:fields: Name, RankIDvalues:Prorduct A, 4Product B, 33Product C, 221(etc)Name is always unique. RankID may not be.I want to take that result set and basically pivot it to have the Namevalues as columns, and the RankID of each one as the data. So youwould end up with only one row like:Product A | Product B | Product C | etc4 | 33 | 221 | etcIs this possible? I do not want to sum the data or anything, simplyrotate it sort of.Any advice is appreciated.

View 3 Replies View Related

Aggregation On Monthly Base

Jul 24, 2006

I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:

Name
StartEmp
EndEmp

tom
1/jan/05
15/mrt/05

dirk
1/jan/05
31/mrt/06

jan
1/feb/05
NULL

In order to get the right information for the report the information should be represented in the following way:

January 2005
February 2005
March 2005
"April" 2005

Tom
1
1
0,5
1

Dirk
1
1
1
1

Jan
0
1
1
1

HeadCount
2
3
2,5
3
(there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )

I need some help about building up the query to aggregate the info from table1 to the format of table2.

View 4 Replies View Related

Failure On Null Aggregation

Dec 20, 2007

Hi all,

my package is failing every now and then on the following error:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".
yes, I do have a null aggregation, but in all my tests this didn't cause a failure...
anyone had experience with this?

Thanks.

View 13 Replies View Related

Transact SQL :: Getting Last Record In Aggregation

Jul 17, 2015

I have daily data that I wish to summarise into monthly. The field [Close] will return the last day of the month that has a value.The query below summarises the data as I want it but using the max of [Close] in the month instead rather than the Last value for the month. In Access I used to use Last function that worked perfectly. From googling, Last doesn't seem to exist in T-SQL but there must be a way to do it.

D.YearMonth_Number,max(AT.[Close])
FROM
tbl_Date
ASD
INNERJOINtbl_ASXStats
ASAT
OND.YearMonthDay_Number
=AT.YearMonthDay_Number

[code]...

View 5 Replies View Related

How To Design Aggregation For Partition Using XML Or AMO

May 23, 2008

Hi,

Can anyone please help me.....

how to design aggregation for new partition using XML or AMO ???

I know how to clone partition using AMO(I guess it will take care of aggregation) and set AggregationDesignID in XML.

But, want to know how to design aggregation for new partition using XML or AMO....

Thanks
Nisanth

View 1 Replies View Related

Turning Off Aggregation On A Certain Hierarchy

May 20, 2008

I have a multi dimensional cube. Among the dimensions I have, there is one dimension that has one hierarchy defined. When I view any of my measures on this hierarchy, I don't want the measures to be aggregated. I read some threads here having to do with semi additive behaviors, but Im not sure if it applies to my case. (or maybe it does and I'm just not getting it)

For example,

DimPerson

PersonID, PersonName, AnotherPersonID
1 , Person_A , null
2 , Person_B , 1
3 , Person_C , 1

The purpose of "AnotherPersonID" is to have a self join that describes a certain relationship. So, in this case, Person B and C are related to Person A. I describe this relationship using a hierarchy where A is a parent of B and C. However, I don't want the measures for Person_A to be replaced by the sum of the measures in Person_B and Person_C.

Why do I have a hierarchy if I don't want to sum the numbers? This is motivated by the need to dynamically report on A's numbers when B or C's numbers are reported. I'm just using persons as an example, but, in my case I have a bunch of members that can be paired with another member in that dimension. And when I report on a member that joins to another member in that dimension, I need to dynamically report on that member's measures as well.

Lastly, If the use of a hierarchy is not the best approach for this, would you recommend another approach?

View 4 Replies View Related

Aggregation That Results Two Columns

Mar 12, 2007

Hi,
I have following data coming from prior transformations.

Id Unit Name NewFlag
___ __________ ____________

1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0

and so on.

I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.

Just wondering if it is possible by using Aggregate and how.

Thanks for your help in advance,

View 1 Replies View Related

Class Does Not Support Aggregation

Dec 23, 2005

Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)

Program Location:

   at Microsoft.SqlServer.Management.UI.VSIntegration.ISqlToolsVSHlpFactory.CreateHierarchyWrapper(Guid& clsidInnerHier)
   at Microsoft.SqlServer.Management.UI.VSIntegration.SqlWorkbenchHierarchy.CreateHierarchyWrapper(Type typeToWrap, ILocalRegistry localReg)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.get_SqlVirtualProject()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeDesigner(IManagedConnection connection)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()Hi there,

I'm using SQL Server 2005 Dev. with Windows XP Pro. SP2.
50GB free space with 1GB of RAM
After I installed SQL Server with no problems.
I tried to open a table in AdventureWorks database and I'm getting an error:

Could some one tell me what is going on ?

Thanks an advanced

Oded Dror
Email: odeddror@cox.net

View 13 Replies View Related

Problems With Aggregation Of Distinctcount-measure

Mar 26, 2005

Hi Experts,

I have a problem:
I have various cube packed in a virtual cube to give me distinctCount Values of customers who purchased products (DC customer] and purchased products (DC product) along with Saleries [RW] over numerous dimensions such as productgroups customergroups etc.
In the calculated measure [RW kum] I can sum the the salaries from the beginning of the year until [time].currentmember with the following MDX-Code:
With member [Measures].[RW kum] As 'Sum(Periodstodate([time].[year]).[Measures].[RW])'
Select {[Measures].[RW],[Measures].[RW kum], [Measures].[DC customer]} on rows,
[Productgroup].[Itemgroup].Allmembers on columns
From [vcub_Sales]
Where [Time].[2005].[march]

IF I use this PeriodsToDate-Formular with the the measure DC customer I will get the sum of distinctcounts of the periods.

What I want is the distinct count of all customers who have purchased since the beginning of the Year until the [Time].currentmember in the same Level.

Given this problem, do I..
1. have make another Cube or
2. another time dimension or
3. is there a way in MDX to get the result?

I would appreciate any replies.

Zannadar

View 1 Replies View Related

Pulling Data Into One Line (Aggregation)

Aug 19, 2015

eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...

I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?

E.G.

CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT
CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT
ETC...........

View 4 Replies View Related

SQL 2012 :: Aggregation Of 200 Mil Data Records?

Jul 7, 2015

We are having a requirement to Aggregate the data and create LY, CY data across 5 Metrics.

the Volume of Data will be 200 Mil - 250 Mil

Server Configuration:
Memory: 32 GB
Processors: 16

understand the bench mark configuration needed for the server & any hints on better aggregation methods & LY / CY data

View 5 Replies View Related

T-SQL (SS2K8) :: Daily Sum Aggregation Script

Jul 14, 2015

I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it. How to make this code daily?

SELECT
Receipt
,"Date"
,Item
,Reason
,Division
,SUM(Cost) AS Cost

[Code] ......

View 1 Replies View Related

SQL Server 2012 :: Daily Sum Aggregation

Jul 14, 2015

I have a problem that requires me to write Daily sum aggregation script, I wrote the script; But what I'm not sure of is the "Daily" part, my script will give me only give me results when I execute it.

SELECT
Receipt
,"Date"
,Item
,Reason

[Code] ....

View 3 Replies View Related

Bulk Insert And Data Aggregation

Jan 30, 2008

I manage a legacy system that dumps it's data into a number of different databases (same schema) on a nightly basis using bulk insert. I need to formulate a strategy for efficiently aggregating that data into a single database right after these nightly extractions complete. Here is my current stategy:

1. Duplicate the legacy system's database schema and add an identifier column to specify which database the data loaded from.

2. Each night, delete all records in the table.

3. Each night, for each database:

3a. Set each table's default value to a value that references the current database being loaded.

3b. Use the legacy system's flat files and format files to bulk insert into the database.

3c. Clear the default value.

What other steps would faciliate performance? Dropping and recreating the indexes? Does anyone forsee faults in this strategy?

Thanks,
Matt

View 3 Replies View Related

Analysis :: Aggregation Design - Using Partitions

Jul 31, 2015

I have 3 partitions using a year grouping. Current year, previous 4 years, older than 5 years. I have two measure groups, one is a distinct count, so I actually have 6 partitions.I also use usage based optimization to build my aggregations. Should each partition have a separate aggregation or should there be one for each measure group?

View 5 Replies View Related

Analysis :: Different Aggregation For Different Card Type?

Oct 21, 2015

I have a fact, like this

DimCustomerKey, DimCardKey, Amount
1, 1, 20
2, 3, 30
1, 1, 40
3, 4, 50
2, 3, 50

I want a report like this

1, 1, 40
2, 3, 80
3, 4, 50

Logic :

- Each DimCustomerKey has only 1 DimCardKey

- If DimCardKey = 1, reported amount = max(amount)

- If DimcardKey other than 1, reported amount = sum(amount)

How can I implement this with MDX script ?

How can I implement this inside the CUBE (using scope or calculated member etc...)?

View 2 Replies View Related

Aggregation In SSAS Not Working In SSRS

Jul 5, 2007

I have a measure in a SSAS cube that uses AggregateFunction "Sum". Dimension usage seems to be right so that it sums correctly.

But when I try to use same measure in SSRS report it comes out all wrong.

TO be precise the measure is called "CalendarDays" and it's the number of days in a month (28,29,30 or 31). Say I have 5 products and 4 years, If I look at this in the cube browser then the CalendarDays column aggregates correctly to 365 or 366 for each year even with the five products and 12 months in the rows.

However if I do a SSRS report by product and month with subtotals of years, then SUM(CalendarDays) adds up all the CalendarDays for each month and each product (ie 5 x 365 or 366). If I leave out "SUM" and just enter =Fields!CalendarDays in the subtotal then it doesn't aggregate at all, it jsut gives the first value (ie 31).

How can I get SSRS to aggregate the same way as the cube?

View 1 Replies View Related

Pivot Table With No Numeric Aggregation?

Aug 4, 2006

I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.

For example, my initial table could be represented by this structure:

ID
Label
Value

1
a
val1

1
b
val2

1
c
val3

1
d
val4

1
e
val5

2
a
val6

2
b
val7

2
c
val8

2
d
val9

2
e
val10

and I am trying to get it into the following structure:

ID
Label a
Label b
Label c
Label d
Label e

1
val1
val2
val3
val4
val5

2
val6
val7
val8
val9
val10

The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?

Thanks for any replies on this,

Eric

View 3 Replies View Related