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
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.
If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).
View 3 Replies View RelatedI 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]....
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
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.
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.
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
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.
How to calculate grand total in sql server using pivot?
View 1 Replies View RelatedHi,
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
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?
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?
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.
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.
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.
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
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.
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?
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?
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!
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]))
Hi,
I've created a calculated measure which is a division between 2 other measures which i also have displayed in the cube.
of course as soon as there is some level of aggregation, the shown result is an averaged division and therefore is wrong.
here is some example:
A 16874
B 956
C 1354
D 264
E 103
F 81
G 6
H 3
X 23
Total 5198.36...
here the wanted result would be a simple some of all the other values (=19664)
How could i treat totals/aggregation differently or have any good way of solving this issue?
I know there is the aggregatefunction propertie for normal dimension, but i'm not familiar with calculated measures and it seems properties that can be accessed through BIStudio's interface is rather limited.
thanks a lot in advance for your help. I've been looking for tweaks on the data to trick the cube, but couldn't find any way of getting the result i'm looking for.
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 !!
is it possible to separate Grand Total of a sub group to a new page?
I tried using advanced mode, find the first line the Grand Total section and set its "RepeatOnNewPage" to true, but, when I execute the report, it will throw an error something like the value of RepeatOnNewPage of the TablixMemeber must be the same as its previous object.
I need my report pages to separate by subGroup(SubGroupA, SubGroupB, A+BTotal).
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.
Greetings,
Godofredo
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?
Using Studio, I created a user defined database role but I can not delete it because
"TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
I am quite annoyed because the "owned schema" is db_owner, which can not be unselected. Quite an innovation. How do I drop this relationship?
Create report with SharePoint list. Columns are displaying on the report I used out of the group SUM function and it is group by report. one group total is showing correct total and other groups are showing wrong total and showing too much big value even there is whole column is empty.where value is not in column I am using "-" with IIF function. these columns are calculating by using date difference function between two date columns. these values coming with minus value . so ABS function is also using.
Columns calculation expression:
=IIF(ISNOTHING(Fields!DateCAPackage.Value)
OR (ISNOTHING(Fields!Date_CA_Application.Value)) ,
"-" , CINT(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value))))
Total calculation expression:
=Sum(ABS(DateDiff(
"d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value)))
Hello all,I have the following t-sql batch:create procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as beginset @p_date1 = isnull(@p_date1, <some expression>)set @p_date2 = isnull(@p_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @p_date1 anddate2 <= @p_date2 and(date1 >= @p_date1 ordate2 >= @p_date2)endgoexec stp_testThis gives a WRONG resultset.When I replace the variables with hardcoded values in the right format, thereturned result set is CORRECT, as followswheredate1 <= 'hard coded date value 1' anddate2 <= 'hard coded date value 2' and(date1 >= 'hard coded date value 1' ordate2 >= 'hard coded date value 2')When I elimate the derived table with a temporary table, the returned resultset is CORRECTWhen I store the parameters in a local variable, and use the local variable,the returned result set is CORRECT, as followscreate procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as begindeclare @l_date1 datetimedeclare @l_date2 datetimeset @l_date1 = @p_date1set @l_date2 = @p_date2set @l_date1 = isnull(@l_date1, <some expression>)set @l_date2 = isnull(@l_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @l_date1 anddate2 <= @l_date2 and(date1 >= @l_date1 ordate2 >= @l_date2)endgoWhen I put less columns in the select list, the returned result set isCORRECT, it doesnt make sense wich columns I remove from the select list.The tables are not small (500.000 rows) and also is the result set. I usethis construction elsewhere, on other table combinations, but dont haveproblems. So the content of the data makes difference.Seems to me as a bug.My question is: Can I say the derived table is instable in SQL server andcauses the problem of the wrong result set here?Peter
View 3 Replies View RelatedHi all,
As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
DangKhoa
99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.
I have written this query but it returns all PersonnelID that fit into the date block
requrirements
PersonnelID
select if DateStart falls within 2006
select if DateEnd falls within 2006
query i built:
Select * From tblResourceList Where PersonnelID=72
And tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')"