Distinct Count With Null Values (grand Total)
May 10, 2007
Hello,
I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years
For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
View 2 Replies
ADVERTISEMENT
May 9, 2007
Hello,
I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years
For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
View 1 Replies
View Related
May 28, 2007
Hi,
I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube
can some body help me !!
View 1 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Mar 12, 2007
Hello,
I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:
FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007
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]
Total count should be: 4
View 9 Replies
View Related
Mar 17, 2008
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.
departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...
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.
View 3 Replies
View Related
Sep 21, 2015
I have the below query,
selectÂ
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column
[Code] ....
o/p: is :
LastEnforcementState NumberOfComputers PComputers
Compliant 7056 91.54
Downloading update(s) 39 0.51
[code]....
I want to add these two rows information into single row,
Failed to Download Updates(s) Â Â 131(127+8) Â Â Â 1.66(1.65+0.1)
how to do this in my sql query?
View 12 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Oct 18, 2007
Hello All,
I've created a report that has a count of dates reports. Every company has at least one date report. I've group the report by company and did a count this works find. Now I want to SUM up this field and put it at the end of the report.
I've used =sum(reportitems!textbox10.value) but it only works at the page footer. And only give me a grand total for that page. I want a grand total for the whole report. Is there a report footer???
Thanks
Set_Shot_Dave
View 2 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
Dec 12, 2003
I need to get the Grand Total of the results of this query.
The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
" Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
" (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
"(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
Thanks in advance.
View 2 Replies
View Related
Jan 18, 2007
I have two subreports (Revenue and Expense) that each have subtotals.
This works just fine, but I need a Grand Total that would sum the two subtotals.
How would I go about doing this?
So it looks something like this:
Revenue Report (matrix)
Subtotal
Expense Report (matrix)
Subtotal
<~~~~ Need Grand Total here.
View 18 Replies
View Related
Mar 6, 2007
Hi All,
My doubt is, how to get gand total in SQL Reporting Services2005 for the following scenario?
Sample report content:
CustomerId Product Name Price
123 Apple $100.00
Orange $77.00
SUB TOTAL $177.00
124 Apple $105.00
Orange $70.00
Olive $450.00
SUB TOTAL $625.00 // sum(Fields!Price)
GRAND TOTAL $802.00 //How to get Grand total? please..
Regards,
Pragash
View 6 Replies
View Related
Aug 5, 2007
Hi,
I am trying to calculate grand totals in the table footer area on a single table report. The report table has a grouping with a filter. I want the grand total to sum the visible grouping data but instead it sums the entire dataset. I've tried summing the textbox but RS does not allow that from the footer. I've tried specified the group for the scope but apparently RS scope works outward not inward in regards to groups. A simple example of my problem might be a list of bank accounts whose balance is overdrawn. In the report you want to show only the accounts whose balance has gone negative and all the transactions for those accounts. You can create a group on the account field and then a filter on the group to sum the transaction amount and filter out the accounts whose sum is not below zero. The grand total would supposedly sum the accounts whose balance is negative. Unfortunately the sum is on the entire dataset and therefore includes accounts with good balances. I'm sure some will suggest changing the SQL but that is not an option for me. These seems like a very basic operation for a report writer so I'm guessing there is some easy way to do this that has eluded me. Thanks for any help.
View 15 Replies
View Related
Nov 4, 2013
I have this bit of code below and would like to bring back stat_code's which have no value as 0...
select stat_code, COUNT(stat_code)AS Stat_Count
--INTO tableSroStatABD
from fs_sro
where sro_stat ='O' and whse = 'ABD'
group by stat_code
having stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')
order by stat_code asc
View 5 Replies
View Related
Apr 25, 2014
How to calculate grand total in sql server using pivot?
View 1 Replies
View Related
Apr 20, 2007
Hi,
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
Anyone help!?
thanks
View 3 Replies
View Related
Jun 12, 2007
how to add group subtotal and grand total in report? i try to add formula Sum(Field!Net_Weight.Value) in group footer and unable repeat footer on each page, it return same total on every pages. I hope to get subtotal on each page by group. the expected result would be like this:
Page1
1.Group 1
Date
D/no
Net Weight
6/1/2007
A00000100
10.45
6/1/2007
A00000101
10.95
6/1/2007
A00000102
11.45
6/1/2007
A00000103
11.95
6/1/2007
A00000104
12.45
6/1/2007
A00000105
12.95
Subtotal
70.2
Page 2
Date
D/no
Net Weight
6/1/2007
A00000100
20.15
6/1/2007
A00000101
20.25
6/1/2007
A00000102
20.35
6/1/2007
A00000103
20.45
6/1/2007
A00000104
12.45
6/1/2007
A00000105
12.95
Subtotal
106.6
Grand Total=
176.8
2.Group 2
Date
D/no
Net Weight
6/1/2007
A00000100
10.45
6/1/2007
A00000101
10.95
6/1/2007
A00000102
11.45
6/1/2007
A00000103
11.95
6/1/2007
A00000104
12
6/1/2007
A00000105
12.95
Subtotal
69.75
anybody know how to do it?
View 3 Replies
View Related
Oct 19, 2007
Hi All,
I have to create a table like this using SSRS.
LoanStatus NoOfLoans Amouunt Percentage
Started
1,000
1,000,000.00
100%
Sent to Partner
350
350,000.00
35%
Denied by Partner
50
50,000.00
5%
Accepted by Partner
300
30,000.00
30%
Loan Apps Pnd Disb
10
100,000.00
1%
Loan Apps Disbursed
290
290,000.00
29%
Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.
Please can anyone tell me how to calculate the grand total and Percentages?
View 5 Replies
View Related
Jul 20, 2005
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Mar 16, 2006
Hi,
I would like to ask how can I get count of null values in each column together?
I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?
View 9 Replies
View Related
Nov 9, 2013
I've been messing around with ROLLUP but have got nowhere,I have this:
DECLARE @sql NVARCHAR(MAX)
DECLARE @pivotsql VARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @selectlist VARCHAR(MAX)
[code]...
which works a treat - I just need a grand total at the bottom for each column.
View 7 Replies
View Related
Jul 23, 2015
I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.
For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set  to this is SUM.
For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.
View 4 Replies
View Related
Oct 9, 2015
I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.
the SP is as below :
/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders = COALESCE ( (@columnHeaders) + ',[' + [Date] + ']', '[' + [Date] + ']')
[code]....
I am getting the below error:
Invalid column name 'Grand Total'.
Msg 205, Level 16, State 1, Line 16
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
View 10 Replies
View Related
May 19, 2008
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
View 10 Replies
View Related
Sep 10, 2007
Hi There i have a Report That Display's data in following format
Areas
Code | Descirption | Emirates | GCC | ASIAN | OTHER ARABS | so on..
1 What ever 3 0 2 1
2 ABC 0 0 1 0
-------------------------------------------------------------------------------------------
Total 3 0 3 1
The Areas are Matric Columns and are dynamically generated .
i dont want sub total of each row , but i want Grand total at the end of the all columns generated by matrix
I have no clue how do to it
Kind Regards
View 7 Replies
View Related
Jan 15, 2008
Hi,
Hi All,
I am having some trouble getting a Grand Total column at the end of my matrix report (not row at the bottom), such as you get by default in an Excel pivot table.
I have managed so far to add another column that sums up all values across the data range for a particular product code. My intention was to hide all but the last (I did something similar on rows). However you cannot use an expression on the width property for columns, so this will not work.
My report is very standard:
Year
Month (grouped by year)
Product code | Description [qty]
I want a column that displays the total qty across all dates for each row. Surely this is possible?
Any suggestions would be appreciated! Thanks.
View 6 Replies
View Related
Jan 11, 2014
I joined different tables and got a result like this:
result | process | goal | date |
------- ---------- ------ -----------
ok | process4 | 1 | 12.10.2013
bad | process1 | 2 | 13.10.2013
ok | process1 | 4 | 12.12.2013
good | process4 | 1 | 03.01.2014
ok | process1 | 3 | 10.04.2013
bad | process3 | 6 | 09.01.2014
bad | process4 | 3 | 30.12.2013
best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result
------ -------
1 | good
1 | bad
0 | ok
0 | best
But I got only:
count | result
------ -------
1 | good
1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.
View 4 Replies
View Related
Feb 3, 2008
Hi,
I have a table employee with 4 columns,
empno fname lname deptno
1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50
suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?
select count(deptno) from employee
where deptno IS NULL..
When i query this i get the result as 0..
View 8 Replies
View Related
Sep 7, 2007
Hello Guys,
I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.
For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!
View 7 Replies
View Related
May 22, 2008
HI,
In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values.
for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country.
Any help will be appreciated.
Thanks
View 3 Replies
View Related
Jul 16, 2015
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008
;WITH CTE AS
(
SELECT
Case WHen left(MONTHDate,3)='Jan' Then '01'
WHen left(MONTHDate,3)='Feb' Then '02'
WHen left(MONTHDate,3)='Mar' Then '03'
WHen left(MONTHDate,3)='Apr' Then '04'
WHen left(MONTHDate,3)='May' Then '05'
[code]....
View 4 Replies
View Related
Nov 18, 2015
I've created a measure counting instances of unique identifiers utilizing the following formula. However, the Grand Total does not equal the sum of the sub-totals.Â
=CALCULATE(COUNTROWS(DISTINCT('Rawdata File'[Identifier])),ALL('Rawdata File'[Identifier]))
View 8 Replies
View Related