How To Get Total Page Count For Individual Groups

Aug 3, 2007

Hello,



I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.



For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,



For 1st group

'Page 1 of 3 when user click next page it should be 'Page 2 of 3' etc



Similarly for other groups as wll.



Thanks!

View 3 Replies


ADVERTISEMENT

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Getting Individual Count In Table

Mar 28, 2008

I have a link that inserts postid, catid, and postdate.  I am trying to get count of catid(how many times its in the table) then display the number on a page.  i have never tried this before.  one more thing.  Can you put a datalist inside a formview when the formview is databound already? Can someone help?  Thank you.
 

View 7 Replies View Related

How To Find Out The Count Of Individual Chars

May 14, 2008

Hi,
Can any one tell me
how to find out the count of individual chars in a String

ie., my string is s='ganesh kumar'
my output must be as below

Character Occurance
--------------------
g 1
a 2
n 1
e 1
s 1
h 1
k 1
u 1
m 1
r 1
----------------------

Can any one do it,
This is interview question..

Ganesh


Solutions are easy. Understanding the problem, now, that's the hard part

View 7 Replies View Related

SQL Server 2012 :: Return Count By Individual Days

Aug 5, 2014

i am using the followig query :

select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'

This returns the count for the month but I want to see what the total each day was.

View 5 Replies View Related

Running Total On Rows Between Groups

Dec 5, 2006

I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.

For example:

AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2

100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5

I pulled the following code from the internet but I'm getting errors.

select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

View 5 Replies View Related

Running Total Across Matrix Groups

Dec 17, 2007

Hi

I have a report that calculates shipping quantities based on customer orders, and color codes them based on whether or not we have stock for a full shipment (green), have partial stock for a shipment (blue), or if we have none (red). Every week we get customer orders and build accordingly.

I have this all worked out except for parts with multiple ship to locations. RS seems to parse the entire row of the first ShipTo, then the second ShipTo. I'm using the running total function to calculate if there is a need or not. Anybody have any ideas?





Here is an example of the output I am getting. On the 17th, the 360 should be in green not red. I double checked the grouping, and it is on part number. This is for a matrix.

http://img519.imageshack.us/img519/8650/productionqg0.png

View 1 Replies View Related

Reporting Services :: SSRS Group Total To Contain Values Of Specific Groups Only

May 25, 2015

I have a requirement to display the total of a Group after subtracting a specific value from the same Group.

Example: Say the below data is grouped on a particular column 

Group Values Month

Jan-15 Feb-15 Mar-15

A 10 20 30
B 5 10 25
C 1 2 3
D 5 10 15

Total 11 22 33

Formula is :  Sum(A+C+D)- Sum(B)

What is the best way to Group the above scenario from SSRS level and display the result as shown above. I am able to display all the values except the last total row where am displaying the complete total i.e. 21  42  73.

How do I dynamically subtract the values for row B which is one of the group values.

View 4 Replies View Related

Top Count For Groups Possible

Apr 7, 2007

Hello,
in a report in Reporting Services 2005 I want to create a top count 100 for groups.
I have a report sheet with a table containing some groups.
Under the groupings there are other groupings and so on.
Now I want that only the top 100, ordered by a value on group level of the 1st group are contained in the report.



Any ideas?



Best regards,


Stefoon

View 1 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

How To Count Number Of Groups In MS-SQL ?

Nov 30, 2005

The following SQL works on Access and Oracle to return the number of
Groups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is the
equivalent in MS-SQL?

Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight]
from ORDERS  WHERE OrderID < 123456  GROUP BY ShipRegion )

Thanks,


Frankk

View 2 Replies View Related

Page Breaks On Groups Within Lists, Tables

Mar 3, 2008

Are groups within a list or table forced to be kept on the same page (if possible)? If so, is there any way around this?

My problem is a report with a list with 3 tables inside the list. All tables show details associated with the grouped individual. The size of one list item can be slightly smaller than half a page or over. When the report can not fit two list items on one page, it starts a new page and leaves unwanted white space.

I have tried:


adding a rectangle
adjusting width, margins, etc.
not using lists, only tables nested within tables
to check for page breaks, I adjusted the page size to 8.5 X 20. Same thing occurs, as many lists as possible on one page without splitting between pages.Thanks for any guidance.

View 1 Replies View Related

How To Count Cases For Different Groups Based On Different Criteria

Apr 25, 2008



Hello,

I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)

Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)

MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null

BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')








12-Jan

19-Jan

26-Jan

2-Feb

9-Feb

16-Feb



MTS





















New Cases Received

85

84

79

98

79

95



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

44

29

26

35

55

54



CreditB

6

12

9

5

7

13



CreditS

-

-

-

-

3

-



CreditP

10

11

11

24

17

7



MTS Subtotal

140

125

110

144

151

150

























BNB





















New Cases Received

12

13

14

14

6

11



Sales

-

-

-

-

-

-



SalesD

-

-

-

-

-

-



CreditR

12

11

12

10

5

9



CreditB

8

13

9

17

16

6



CreditS

-

-

2

-

-

-



CreditP

1

1

1

1

4

3



BNB Subtotal

21

25

24

28

26

19

























Total





















New Cases Received

97

97

93

112

85

106



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

56

40

38

45

60

63



CreditB

14

25

18

22

23

19



CreditS

-

-

2

-

3

-



CreditP

11

12

12

25

21

10



Grand Total

161

150

134

172

177

169



This is just a very brief bit of code

SELECT MTS_new_cases_sales, mts_new_cases_salesd .

FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...



Can you please show me how to accomplish this?

Thank you in advance for your effort,



Rhonda

View 2 Replies View Related

Total On Each Page

Jul 24, 2007

hi,

in my report i have amount field. i want to display its total (sum) on each page..... i have tried runningvalue in tablefooter but it gives final total.......means grand total... i have also tried sum function in tablefooter & set repeateoneachpage = true but it gives same result.............. and i want pagewise totals...... anybody can help me...................

View 6 Replies View Related

Total No Of Row Count

Sep 25, 2007

Hi,



I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.

I want to do the following -



Sum the total no. of rows in mdb while the package executes

Sum the total no of rows transferred to Sql server.

Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)


Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.


I saw a RowCount transformation..but just dont know where to place and what to do with it.



thanks

View 2 Replies View Related

Reporting Services :: Row Groups Sort By Count Not Working?

Nov 4, 2015

I have a simple matrix with a row group that is basically a name.  I want to sort a list of 10 or so names based on a count.  I also have a column grouping -basically 1 years worth of month end dates.  I want to sort on the count on the most recent date, so I set the expression to:

=IIF(Fields!week_ending.Value = MAX(Fields!week_ending.Value, "customer_2015_select"), COUNT(Fields!application_id.Value), -1)

And then Sort Z-A.  It allows the expression, but doesn't sort the values correctly.  It doesn't seem to be picking up the date.  

View 6 Replies View Related

Reporting Services :: How To Display Count Of Column Groups

Oct 14, 2015

How to count the column group values and how to print the counts on every column for column group.

I used below expression to get the total column group count.

=CountDistinct(Fields!MMU.Value, "DataSet3")

View 8 Replies View Related

Get Records With A Count Total

Feb 14, 2008

Hello, I am having problems with this query below:
 1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
 

View 1 Replies View Related

Getting Total Count From Query

Sep 21, 2013

I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is

Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date))
which gives me a result like
inv_cust ' Salescount

[code]...

How can I modify my existing query to get this.

View 2 Replies View Related

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC.  SRC is a number which I use later to get counts and sums for the records in a distinct list. 

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate

OR

-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,

[code]....

View 5 Replies View Related

How To Create Total Time, Count It

Aug 29, 2005

i hv a problem in sql,

i) if in data hv time_start, time_end, date , id, how i can calculate the total time for very sesion user login

ii) how to make it if the total come out hv same id(may be two id hv two diffrent total), i want make it just one id then the total time...

post me at jeffliew2001@yahoo.com

View 6 Replies View Related

Getting Total Row Count For Complex Query Using CTE

Apr 26, 2007

I have a query that uses a CTE which looks similar to this

WITH cte_Products AS
(SELECT SELECT
ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS RowNum,
RANK() OVER (ORDER BY id DESC) AS rank,
FROM Products
WHERE SomeColumn = @SomeVariable)
SELECT rownum,
columns,
(SELECT COUNT(*) FROM cte_Product) AS TotalRowCount
FROM cte_Product AS products
WHERE Rank <= @LastXItems
AND RowNum BETWEEN (@StartRowIndex + 1) and (@StartRowIndex + @MaximumRows)
ORDER BY RowNum

The (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount is in there because I need the total row count that is queried by the CTE. But I feel like this is an inefficient way of doing this. I would just split out the query for the total row count, but then I have to do another sub query to get the rank again since rank is calculated using the Rank method. Does anyone have any ideas of how best to do this?

View 3 Replies View Related

Reset Total Page Number In A Group

Sep 26, 2006

I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX. Code for page of total pages

="Page " & Globals.PageNumber & " of " & Globals.TotalPages



EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function

=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)

What I need is code for a combination of the two...to display code for page of total pages that resets within a group.

Any help is greatly appreciated.
Thanks!

View 4 Replies View Related

Count The Total Number Of Results Returned

May 17, 2007

Iam using front page to dispalay my results.

At the bottom it shows me 1/10 i.e 1st page of 10 pages.

but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).

or it would be really good if i get count of both no. of recors as well as no. of pages.

View 2 Replies View Related

Select TOP 1 And Count Total Records At Same Time

Jun 7, 2012

I have a Select TOP 1 statement which works fine, but i also want to count the total records at the same time. This is my SQL

Code:
SELECT TOP (1) dbo.ClientOffers.ProductID, dbo.ClientOffers.ClientID, dbo.Offers.Offertext, dbo.Offers.Offerlink, dbo.Offers.Image, dbo.Offers.Cost
FROM dbo.ClientOffers INNER JOIN
dbo.Offers ON dbo.ClientOffers.OfferID = dbo.Offers.OfferID
WHERE (dbo.ClientOffers.ProductID = 1284) AND (dbo.ClientOffers.ClientID = 0)

How do i also count the total records?

View 7 Replies View Related

Trying To Return Total Record Count With Query

Feb 26, 2007

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>


Thanks for any help!
Dale

View 10 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

Transact SQL :: Total Count Of Table Rows

Sep 15, 2015

I have a question regarding the total count of the table rows

Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".

So now I have to store the count in 'nn'.

View 5 Replies View Related

Get Total Count And Top N Rows In Single Call

Dec 3, 2007



Hi,

I want to return the total number of rows and top n records from my query. Is it possible to do this by simply running a single query. My query is dynamic so I can not create any temp table or table variables.

Bacially I want to eliminate 2 calls for sp_executesql

Set @ColumnCount = "Count(*)"
Set @Columns = " Top 100 a,b,c"
Set @Filter = "a = 1001"

Set @sql = "select " + @ColumnCount + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Set @sql = "select " + @Columns + " FROM TABLEA WHERE " + @Filter
sp_executesql @Sql

Thanks
Pun

View 3 Replies View Related

Counting Total Number Of Queries Executed Within The Page

Aug 25, 2007

Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance! 

View 3 Replies View Related

Distinct Count With Null Values (grand Total)

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

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 View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved