How To Subtotal A Distinct Count In An Olap Report?
Sep 28, 2007
I am using SSRS 2005 to develop this OLAP report. I have the "Distinct Count" as a measure and some dimensions in the OLAP cube. I assigned one of the dimension to the "row" grouping and use the wizard to add the "subtotal" to that row level dimension. However it adds up individually the "Distinct Count" values from that row level dimension instead of the "True Distinct Count" of all attributes of that dimension combined. In my report there are "column" groupings so that by creating another dataset just to calculate the distinct count seperately in the report will not work unless there is a way to link this dataset to the column grouping of the first dataset. Also I don't want to create this dataset as I need to pull in the individual "id" field in the dimension in order to do the "Distinct Count" in the report and it is slowing down the performance. Thanks.
Is there any way i can change the expression for the Subtotal to do a count , instead of summing (actually just shows first value). The Matrix groups are consitent of "Yes" and "no" values. I want to be able to put Count all the yes's and all the no's. Is this possible?
I am having a matrix as follows Survey1 survey2 Survey 3 Company1 2 3 Company2 1 2 Company3 2 4 Company4 6 7 ------------------------------------------------------------------------------ Total 2 3 3
_____________________________________________
Like that it should display instead of it is showin sum value in the total. Is there any function/customecode to do that
My first post so go easy. Also, still a bit green on both OLAP and MDX but I need help figuring this out.
The data set I'm using has patient level information and I'm interested in distinct counts of patients. There are several dimensions I have in my existing cube but there are three different types. I have a time dimension, a 1-1 dimension and a 1-many dimension (sorry not getting the proper terminology correct here).
The time dimension (month is the lowest level) I have a handle on as well as the 1-1 dimension (lets say age or gender is a good example). For the purpose of the explanation, lets just add a cost measure for a placeholder. The bugger is the 1-many dimension. When you visit a doctor, you typically get diagnosed with a code. The code rolls up to a disease category. THis happens every visit and every visit can have multiple codes. For example, you come in with a broken arm and they diagnose you with the flu too.
The business question I'm trying to answer would be something like:
How many female patients have been diagnosed with disease Y OR disease Z or disease T in the past 12 months? and how much did it cost?
Please note in the above question, the operand is "OR" meaning they would have to present with any 1 of the three to meet the criteria. Its my understanding cubes typically will be additive in nature and try to come back with counts of females with instances of all three. This is not the case and fails the business need.
I know this is a high level question but any ideas into either a design or a raw MDX syntax to tackle the issue would help.
sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?
select distinct d.docrefid from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
will return me
docrefid DC01C06027
but when i try to count(*) only the distinct record, it count all..
select distinct d.docrefid, count(*) from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700' group by d.docrefid
select a.patientid, a.providerid from PatientDrugList a JOIN Admin..Loadinstance b on a.intLoadInstanceId = b.intLoadInstanceId where a.MailReasonTypeId = -1 and b.SubClientId = 22 and b.StatusTypeId > 0
Now how could I get distinct patientid count and distinct providerid count
I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.
Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).
I have an SQL statement that looks like the following:
SELECT [Docs-Entities].entityID, entityName, COUNT([Docs-DocsEntities].filename) AS numDocs FROM [Docs-Entities] LEFT JOIN [Docs-DocsEntities] ON [Docs-Entities].entityID = [Docs-DocsEntities].entityID GROUP BY [Docs-Entities].entityID, entityName
but the problem is that numDocs (the COUNT) is not returning a distinct count. In the DocEntities table, a particular document can actually have multiple entries with the same entityID so that produces inflated numbers for numDocs. But when I do a SELECT DISTINCT on a particular entityID, the results are less and don't match the numDocs number because I only need to list the document one time. This is not a huge issue, but it looks bad on my site.
Is there a way that I can make COUNT count distinctly?
Thanks for the help and I hope I worded that cllearly...
I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.
hi, I have a table that contain(custname,ordno,ordchange,ordlocation,reaso n) I want to determine the following: how many orders per customer,how many orders changed per customer,how many order location per customer, what reason for change per customer,
here what I wrote as a query, tell me if I am right. I thank you for your help
select custname, COUNT(DISTINCT ordno) , COUNT(DISTINCT ordchange) , COUNT(DISTINCT ordlocation) , COUNT( reason) from customers a, orders b where a.custname =b.custname group by custname
helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you
I have a table with following fields tdate custcode prodcode
table is filled with full year data and i want following result
I want count of distinct custcode in every past three months.
for example Result like this
month tjan tfeb tmar tapr tmay ..... tdec
prod1 prod2 . . prod5
And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required. Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.
Can any1 help me please.
I am using MS SQL 2005 and above table is a big table (approx 10 million records)
I have a table that have the following fields: Outlet,EmployeeNumber,WorkingDate,WorkingHour,Ince ntive
when I put it into a matrix report, the Outlet is assiged to Page, the EmployeeNumber is assgined to ROW, the WorkingDate is assigned to Column and both WorkingHour & Incentive is assigned to Data(display in row but not column). There is a subtotal at the extreme right side.
May I know how can I squeeze in another element display at the report showing sum of Incentive / sum of WorkingHour? This new element shall be placed at each row as it's indicating the average incentive of each employee.
hello, i have a working stored procedure SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS 'NrOfComments' FROM Comments WHERE PictureID = @PictureID witch returns among others the number of comments for a picture i need to select the number of distinct users who commented that user too, so i added this at SELECT statement, COUNT(DISTINCT UserName) AS 'Expr1' i get that error: "Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause." what should i do, to select the number of distinct users who commented on a specific picture? here are the table rows, if you need that CommentID --- UserName --- PictureID --- DateCommented please help me, thank you
It doesn't seem possibly, but maybe? Is there a way to have an expression be used, but also benefit from using distinct on a column?
I'm looking for something like: sum(case when dtEntered > '1-1-2006' then 1 else 0 end) but also encorporating somehow a distinct count on UserName. So a username showing twice would only count once, and this would only be counted if the record's dtEntered date was greater than Jan 1, 2006.
The reason I'm writing the statement that way is because there are 5 columns which aggregate data by different time periods.
If it's not possible, I will just end up joining to the table multiple times, putting the date filter in the where clause.
I am trying to get the below query to work and can't seem to get past this error. Not sure but everywhere I look I think my syntax is right. I am probably missing something obvious but I'm just back from vacation and trying to get in the swing of things. Any help would be appreciated.
Select Distinct(chadcd) as Adjustor, Count (Distinct chclno) as NumberOfCases, Count (Distinct chclno,chwkno)as NumberofClauses from clmhdr where chpddt = '20040630' Group By chadcd Order by Adjustor
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ',chwkno'.
I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):
SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs FROM APs WHERE (completed IS NOT NULL) GROUP BY [File]
I want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------ SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek, COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration WHERE Archived = 0 GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3)) ORDER BY CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7 END
Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.
I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.
My report would produce the following output considering the above dataset:
Merchandise Number of customers who used a coupon -------------------------------------------------------------------------------------------------------------- Department 1 2
Class 1 2
Barcode 123 2 Barcode 789 1 Department 2 0
Class 7 0
Barcode 456 0
I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.
We have a table of students with fields for the ID of the school district and their school name. For example:
DistrictID StudentName SchoolName
10001 John Smith Washington Elementary
10001 Jane Smith Lincoln Middle
10002 David White Hill High
... ... ...
I want is a listing with the school district ID, the student name, and the number of schools in the district.
I believe the following statement should give me what I want:
SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)
FROM StudentData
However, I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'distinct'.
If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want. If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want. I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.
The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference: Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx
Anyone have an idea one what I'm doing wrong or how to go about getting this information? Thanks! Matt Penner
Thanks for taking the time to read my post. I greatly appreciate it!
What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.
Account Date
1 1/1/08
2 1/2/08
3 1/2/08
2 2/8/08
4 2/9/08
1 2/15/08
1 3/5/08
5 3/6/08
4 3/9/08
3 3/10/08
1 4/1/08
5 4/9/08
2 4/15/08
3 4/26/08
1 5/3/08
2 5/15/08
3 5/29/08
6 5/30/08
Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:
Account Month
3 1
4 2
5 3
5 4
5 5
I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!
Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
select a.Assignment_UniqueID as DeploymentID, a.AssignmentName as DeploymentName, a.StartTime as Available, a.EnforcementDeadline as Deadline, sn.StateName as LastEnforcementState,-----Required Column
I need to get the groupwise distinct count . For eg. Lets assume we have group as Category and under this there are two childs Category1 and Category2. I need the distinct count under Category1 and Category2 separately with the count resetting after Category1 and Category2. Any help will be greatly appreciated.
