Aggregating Member Properties Or Not Aggregating Distinct Data
Feb 23, 2005
I have an employee, who received an initial starting bonus of $50k.
This value will be static from day 1 to day n and will never change. I
want to see the intial starting bonus, but any sets higher than the
employee dimension will need to aggregate the starting bonus. Is there
an easy way to do this?
If I just look at the data from an employee's perspective, I can do
this by making the measure a Min, Max, or Avg Aggregate function. But,
if for instance I want to view the data from the perspective of
departments, it would need to sum() the data instead (which min/max/avg
don't do).
If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.
View 4 Replies
ADVERTISEMENT
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
View Related
Aug 19, 2006
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice,
DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp
FROM MasterData
GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
Any ideas?
thanks in advance.
View 1 Replies
View Related
Apr 2, 2015
I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.
********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';
WITH CTE AS (
SELECT bkg_nbr
[Code] ....
The raw data from the CTE is:
Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1
The result of the query above was:
Category RecCount
Non-Recidivists3
Recidivists 1
The outcome I was looking for was:
Category RecCount
Non-Recidivists2
Recidivists 1
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
View 2 Replies
View Related
Mar 7, 2008
I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits
------ | ------- | ------- | ------------- | ----- | -------
Female | English | Elena | Phys 101-b | 1 | 4
Female | English | Elena | Hist 101-c | 1 | 4
Female | English | Elena | Engl 101-a | 1 | 4
Female | English | Elena | Engl 105-b | 1 | 4
Male | History | Rich | Phys 105-a | 1 | 4
Male | History | Rich | Engl 101-c | 1 | 4
Male | History | Rich | Hist 101-b | 1 | 4
Male | History | Jacob | Phys 101-a | 1 | 4
Male | History | Jacob | Hist 101-b | 1 | 4
Male | History | Jacob | Engl 101-c | 1 | 4
Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table:
Gender Program Heads Credits
------ ------- ----- -------
Female English 1 16
Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
Thanks for taking the time...
tom
View 1 Replies
View Related
Apr 4, 2015
I have the following table which shows the location of a person at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006
The delta increments by +1 every time the location changes.I would like to return an aggregate grouped by delta as per example below.
EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200
I am using the following query (which works fine):
select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2
I have the following table which shows the location of different people at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003
I would like to return an aggregate grouped by person and location.For example
EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200
What modifications do I need to the above query (Problem 1)?
View 1 Replies
View Related
Mar 5, 2007
Hello everybody,
I'm facing a big problem in my actual report development task... however, what i need to do is (in my opinion) so basic that there has to be a way to do this...
What does it all about:
My customer needs a report in which different measures are shown in rows in a matrix table. columns are reserved for the months, simulating a sort of calendar aspect.
However, the matrix has one additional group in it's rows and the whole matrix is contained within a list.
A possible situation could be following:
Coca Cola
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec
TOT %
PM BRU 619 53% 122 23 34 24 0 67 72 78 56 90 78 65
NET 572 58% 87 12 30 13 0 54 65 50 43 85 73 60
IN BRU 554 47% 45 54 8 23 32 45 6 76 34 87 87 65
NET 421 42% 30 43 5 20 12 32 3 60 32 80 65 39
TOT Jan Feb Mar ...
BRU 1173
NET 993
Pepsi Cola
...
The problem are those percentages in red. They are calculated on total amount of BRU/NET without distinction of the first group in the table (PM or IN). If I set the value for the textbox to the following expression:
=(sum(Fields!value.Value)/sum(Fields!value.Value,"Level2"))*100 Where Level2 is the Details specified in the list (in our case Coca Cola or Pepsi Cola) Reporting services makes the sum of the value field without making the distinction of which type of value it is dealing with...
My question is now rather obvious: Is this calculation possible and if yes how?? I've thought to write a custom method in Reporting Services (no assembly) but I've no Idea how i can 1) access the dataset. 2) I need to be able to specifiy the name of the measure for which the calculation needs to be done and 3rd I also need to pass the current detail of the list item to be able to apply the necessary filters on the dataset to perform the aggregation....
Any idea's how to perform this??
Thanks in advance
Alain
View 1 Replies
View Related
Aug 3, 2007
Hi all.
I have what I thought would be a simple problem, maybe my approach is all wrong!
Say you have customers, registered in a year/month group. They can be active in another year/month group.
So we get a simple table with registration year/month down the side and activity along the top.
This shows "of all the people p who register in month m, how many are active in month a?"
So assuming that all people registered in month m are also active in month m, the max(activity) for that slice (ie the max value for any active month for the registered month) should be the value if we collapse the activemonth group to activeyear.
Let me try to draw it :D
Registered/Active
2006 2007 ...
Jan Feb Mar ... Jan Feb Mar ... ...
2 Jan 20 10 10 20 . 5 3 1 5 20
0 Feb 0 25 15 25 . 8 5 5 8 25
0 Mar 0 0 40 40 . 11 1 6 11 40
6 ... 20 35 65 65 . 24 9 12 24 85
2 Jan 0 0 0 0 . 50 34 44 34 50
0 Feb 0 0 0 0 . 0 45 40 41 45
7 ... 20 35 65 65 . 50 79 44 75 95
..... 20 35 65 65 . 74 88 56 99 180
I think. The ...'s represent the grouping band total.
View 1 Replies
View Related
Oct 30, 2015
I've built a cube which has all the measures and dimensions I want, unfortunately the numbers are off: the product prize is aggregated then rather simply being fetched from the underlying oledb destination file in the data warehouse. So if product x costs 10 USD and has been purchased 3 times in a certain month by a customer, the cube shows a product prize of 30 USD. I've switched the measure's usage from SUM to no aggregations but I still don't get the simple value list without aggregation. Why is this happening?
View 4 Replies
View Related
Sep 23, 2015
The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.
-- greatly simplified schema that makes as much sense as the real schema
CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10));
CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue));
CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));
[Code] ....
Desired results:
SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment
UNION ALL
SELECT 2, 'second row', '2015-10-11', 150
UNION ALL
SELECT 3, 'third row', NULL, NULL
I know I'm doing something wrong in the last query and I believe another sub-query is needed?
Let me answer a few questions in advance:
Q: This schema looks horrible!
A: You don't know the half of it. I cleaned it up considerably for this question.
Q: Why is this schema designed like this?
A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.
Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical.
A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).
View 2 Replies
View Related
Aug 15, 2012
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
View 2 Replies
View Related
May 23, 2007
In MSAS 2000, it was possible to drag/drop the member properties in a particular order. This came in handy when showing the properties in the screen tips...like information could be grouped together.
In MSAS 2005, it appears that the member properties are ordered by name and I cannot change the order in the Dimension Structure tab.
How do you change the member property order in 2005?
View 1 Replies
View Related
Oct 28, 2015
Is there any option in SSAS cube to hide the member properties from the user by default in the cube itself. We know we have a option in the excel pivot 'Show properties in tooltips check box where we can hide or display the member properties. But I need an option in the cube to hide the member properties by default and display in the excel depends on demand.
View 2 Replies
View Related
Oct 23, 2014
I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named groupÂ
the codeÂ
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
 "The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?Â
View 4 Replies
View Related
Sep 10, 2015
Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
View 6 Replies
View Related
Mar 23, 2007
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
View 18 Replies
View Related
Mar 23, 2007
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
View 4 Replies
View Related
Sep 24, 2015
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
View 9 Replies
View Related
Mar 25, 1999
Below is code that I have aquired from both the SQL6.5 Books online and a VB5 book. The problem is that when I run this code it returns back the error Method or data member not found. OK I have been to Projects References and made sure that both the Microsoft RemoteData object 2.0 and Remotedata control 2.0 where both included in my project.
The Remotedata control works great, however it does not allow me to Add data and Remove Data.
Am I missing a Reference file?
Do I have the correct version of Remotedata objects?
Private Sub Command1_Click()
Dim cn As rdoConnection
Set cn = rdoEngine.rdoenviroments(0).OpenConnection("sqlser ver")
Dim mysql As String
mysql = "select cuscode from customer where cuscode = '1122'"
Dim myqy As rdoQuery
Set myqy = cn.CreateQuery("myqy1", "")
myqy.SQL = mysql
Dim myrs As rdoResultset
Set myrs = myqy.OpenResultset(rdopenfowardonly, _
rdConcurReadOnly)
While Not myrs.EOF
Debug.Print myrs(0)
myrs.MoveNext
Wend
myrs.Close
myqy.Close
End Sub
Thanks in advance.
LoPingKill
loping@inlink.com
View 2 Replies
View Related
Apr 4, 2007
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
View 1 Replies
View Related
Jul 6, 2015
My problem seems simple but I can't how to do it with MDS... or even if it's possible !
I've got 2 entities "Agent" and "Function".Â
"Agent" has a code, a name, and an attribute called "function_code" which refers to "Function"'s code
"Function" as a code and a name. Name is the description of the function.Â
I'd like to see in a single row :Â
"Code", "Name", "Funcion_code" and "Name" (the last one from the entity "Function").Â
In SQL it will something likeÂ
Select a.code, a.name, a.function_code, f.name
from agent a, function f
where a.function_code = f.code
I've tried with explicit hierarchy, derived hierarchy, consolidated members...Â
I was able to have an entity with all those attributes but I can choose the attribute I want. My goal is that, according to "Function_code" in Agent, I get the "Name" of the function...Â
View 3 Replies
View Related
May 24, 2006
Hi, all here,
I currently have a question about unknown member in cubes (as we know, there is always an additional member called unknown in an OLAP dimension)for data mining. I mean is there any way for us to deal with unknown member in cubes when mining cubes?
Thanks a lot in advance for any guidance and help.
View 8 Replies
View Related
Mar 16, 2015
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
View 1 Replies
View Related
Mar 18, 2007
Error 2 'Text' is not a member of 'System.Data.SqlClient.SqlParameter'. I think this error has something to do with the lack of an Import command, anyone point me in the right direction?
View 4 Replies
View Related
Dec 1, 2006
I have a SqlDataSource that has a parameter that I am trying
to set.
The item is stored in a session field called “GameObject�
Game Object is a simple class that has a several variables.
I am trying to access the .name data member of the class.
<asp:SessionParameter Name="GameCode" SessionField="((GamingSystem)Session[‘GameObject’]).Name" />
This does not work. Is there a way to declarative bind to
the item that I am for.
View 3 Replies
View Related
May 28, 2015
I am using excel 2010 and SQL server as a data source. When I create a report with the User name "demo_user" which has db_writer access, and mail it to the colleague , he could not refresh the data with the "demo_user" credentials and apparently its throwing user name or password invalid. The one who created only could be able to refresh the data.
What should I do If other people want to refresh the data? How could I fix this issue? We are using SQL server 2012 and excel 2010 powerpivot.Â
View 2 Replies
View Related
Jun 12, 2015
I want to setup a link from an SSRS report to Master Data Manager so when the click on the SSRS report item, it will pull up Master Data Manager filtered to the particular member that the user clicked on from the report. I cannot find any references to that being possible. Is it possible? What is the query key value to pass a member codeid or other filter criteria to Master Data Manager explorer?
MID is Model ID
VID is Version ID
EID is Entity ID
Are there other possible parameters? Can a pass a member ID or code?
View 2 Replies
View Related
Dec 21, 2012
I have an API which uses the MDS WCF methods to update and insert entity members into MDS. Its working as expected. But whenever a entity member is inserted or updated, the validation flag is set to "requires re-validation" (With "?" symbol). Is there a setting which has to be set to validate the record once inserted or updated by API? Or should it be validated explicitly once the insert or update happens?
View 4 Replies
View Related
Jan 9, 2005
Does anyone know how to display the properties of a column / data field in ASP? Thanks!
View 6 Replies
View Related
Mar 21, 2008
Hi,
I have SQL Server 2005/BIDS installed on a 64-bit server. When I open an SSIS package the properties window for each of the Data Flow tasks is empty. The properties window is there and displays correctly for other types of task but for the Data Flows it only shows the name of the task. To further compound my misery if I try to open a package that dynamically sets a property of a Data Flow task (using an expression) the package fails to open with errors concerning reading the XML of the package.
Packages containing Data Flow tasks still run on this server (both in BIDS and using dtexec) as long as they don't contain expressions that set any Data Flow properties.
Any ideas?
Thanks
View 3 Replies
View Related
Nov 6, 2007
Hi,I'm trying to import data to my database on the live server from my local server. However when I do this it doesn't seem to be importing the properties for the fields in the tables. How can I import the properties of the fields too?Thanks,Curt.
View 1 Replies
View Related
May 28, 2008
In SQL 2k5 every db object say table , table fields etc have extension properties . I plan to populate these extension properties with meta data related information like field description , string length , case conversion , field alignment , enum options etc .
I wish to know if anybody has done, anything like this . Can this properties be easily avaialble in .Net development , reporting services , BIDS etc . Can there be significant advantages wrt this .
I would like to get user experiences .
View 1 Replies
View Related
Jun 25, 2007
Hi,
I'm thinking of implementing a rudimentary Data Dictionary for a database using extended properties to store table and column descriptions.
Does having this information for every table and column in a database (200 tables; 2000 columns) cause any (negative) impact on database performance? I'm guessing it shouldn't but want to make sure before implementing this in the production environment.
Thanks,
Smitha
View 3 Replies
View Related
