Subtotals & Grand Totals

Feb 28, 2007

I am new to SQL and have been given the task of adding Subtotals and a Grand Total to a report. Below is my code...can someone point me in the right direction?

SELECT
POP30300.POPRCTNM,
receiptdate,
VENDORID,
VENDNAME,
POP30310.ITEMNMBR,
SERLTNUM LOTNUMBR,
LandedCost = ISNULL(CONVERT(money,LOTATRB1),0),
STNDCOST = ISNULL(CONVERT(money,STNDCOST),0),
LandedCostStatus = CASE WHEN CONVERT(money,ISNULL(LOTATRB1,0)) = 0 THEN 'Missing' ELSE
CASE WHEN (CONVERT(money,ISNULL(LOTATRB1,0))-STNDCOST)/STNDCOST*100 NOT BETWEEN -30 AND 30 THEN 'OutOfRange' ELSE 'Okay' END END,
PONUMBER,
ISNULL(POP30310.UNITCOST,0) / ISNULL(POP30310.UMQTYINB,1) POPRICE,
ISNULL(QTYAVAIL,0) QTYAVAIL
FROM ODB.dbo.POP30300 POP30300 (NOLOCK)
INNER JOIN ODB.dbo.POP30310 POP30310 (NOLOCK) ON POP30300.POPRCTNM = POP30310.POPRCTNM
INNER JOIN ODB.dbo.POP30330 POP30330 (NOLOCK) ON POP30310.POPRCTNM = POP30330.POPRCTNM AND POP30310.RCPTLNNM = POP30330.RCPTLNNM
INNER JOIN ODB.dbo.IV00301 IV00301 (NOLOCK) ON POP30330.ITEMNMBR = IV00301.ITEMNMBR AND POP30330.SERLTNUM = IV00301.LOTNUMBR
LEFT JOIN ODB.dbo.IV00101 IV00101 (NOLOCK) on POP30330.ITEMNMBR = IV00101.ITEMNMBR
LEFT JOIN (SELECT ITEMNMBR, LOTNUMBR, SUM(QTYRECVD-QTYSOLD-ATYALLOC) QTYAVAIL
FROM ODB.dbo.IV00300 IV00300 (NOLOCK)
GROUP BY ITEMNMBR, LOTNUMBR) QTYAVAIL
ON POP30330.ITEMNMBR = QTYAVAIL.ITEMNMBR AND POP30330.SERLTNUM = QTYAVAIL.LOTNUMBR
WHERE POP30300.POPTYPE IN (1,3)
AND POP30300.VOIDSTTS = 0
-- AND POP30300.receiptdate > DATEADD(dd,-35,GETDATE())
AND ISNUMERIC(LOTATRB1) = 1
AND ISNULL(QTYAVAIL,0) <> 0

View 20 Replies


ADVERTISEMENT

Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Example:
...
....
Group1              Value
                           10
                            20
Sub Total 1:         30

Group2                 Value
                              15
                              25
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies View Related

Howe To Get Grand Totals?

Sep 24, 2007

Hi,

Can you get grand totals row in sql?

I want to add val column to a grant total, Time1 to a grand total and time2 to a grand total in table below. How to do this please?

PVal TIME1TIME2

p101900-01-01 00:49:59.0001900-01-01 00:00:00.000
p2221941900-01-01 06:33:13.0001900-01-01 00:26:55.000
p3229741900-01-01 06:33:13.0001900-01-01 00:52:39.000

Thank you!

View 1 Replies View Related

Grand Totals Page

Sep 7, 2007

We have a report that includes a list that expands down to 17 inches to accomodate two pages for a report and this list is grouped on a particular location. Each report is grouped by State. So in each state there can be many locations that report various items that comprimse two pages.

Now, what we need is a "state grand total" page and I am wondering about the best way to go about doing this. We do not want to create a new report with a separate stored proc that returns grand totals. What we would like is to do a running total or something like that. I could then include this on a subreport, but I need it to be the last page. For some reason, I thought there was a Report Footer section, but I only see Page Footer.

I hope I made myself clear enough and not being too confusing and I many thanks for any helpful information.

View 1 Replies View Related

Horizontal Totals Of Subtotals In A Matrix

Sep 19, 2007

I'll admit I am fairly new to the reporting services, but I managed to figure most things out so far except this one. I have a matrix report where I have column groupings of:

Sales person
Region

Basically, the report shows sales by sales person and region, and I added subtotals to each so each salesman/region combination gets subtotals, as does each salesman entry. Now, the one piece missing is the 'total of the subtotals' so to speak. So if I have something like:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
002 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
Grand Total $800 $950 $400

What I want to do is sum up the totals and add another column like so:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
002 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
Grand Total $800 $950 $400 $2150

And that is where I am stuck. In Report Designer, with the existing matrix report as above, how do I get these horizontal totals of the subtotals?

View 5 Replies View Related

Analysis :: How To Hide Grand Totals For Particular Dimension

Aug 3, 2015

I have a calculated measure. On a particular dimension I want to hide the grand total. for remaining dimension grad totals to be visible. Like the below screen shot ....

View 4 Replies View Related

Error With Subtotal And Grand Totals With Iff Checking.

Jun 8, 2007



I have a table with amount columns and I want the amount column to either insert the value from the database or a zero based on a condition.



For the table rows I use the following to find the amount:



=iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0)



which works fine. However, when I try to Sum in the group foot I get #Error when I use



=sum(iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) )



for the groupings that have a type other than Material and Other. For some reason, it doesn't total the amounts of Material and Other with the Zeroes that were placed in the table rows based on the Condition.



For example, the subtotal errors out when trying to total Material with Labor but if it was just Material and Other, it works.



Example of what the Columns are:



job, year, month, type, amount



s57, 2007, 2, labor, 0

s57, 2007, 2, material, 500



month total errors out

year total errors out

job total errors out



Any help would be appreciated.

View 12 Replies View Related

Pb With Calculated Measure And Totals (grand Total Or Anysubtotals)

May 20, 2008



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.

View 1 Replies View Related

Analysis :: Grand Totals Not Adding Up In Power View On Molap Cubes?

Nov 12, 2015

My Power view reports do not show the grand totals correctly.  I think my issue relates perfectly to [URL]. However we have updated service pack 2 on both the sql data warehouse and the ssas box.  They are 2012.  However the power view is still incorrect.  Pivot tables seem to work just fine.  In AS I have a 2 separate measure groups that has a total count.  I then use mdx to bring those two together.  Something I have done many times in past with as 2008.  My mdx looks like this below.  I then hide Total Count EC (from measure group 1) and Total Count SF (from measure group 2). This way I have 1 total count value and they can use another dimension to see the two separate if needed. 

CREATE
MEMBERCURRENTCUBE.MEASURES.[Total
Count New] AS
//
([Measures].[Total Count] + [Measures].[Total Count SF]),
DISPLAY_FOLDER
=
'Totals', 
ASSOCIATED_MEASURE_GROUP=
'Report',
FORMAT_STRING
=
"#,#0",
VISIBLE=
1;

The measure Total Count EC works just fine but the Total Count (MDX) does not .  All in Power Pivot works fine but in Power View the grand totals are not correct.

View 3 Replies View Related

Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations

Jul 9, 2007




































Some DB Field ID
X
2007
2008

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B

1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270

2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340

3
600

































All,



I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?



Thanks.



View 5 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!

 

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

Create Subtotals

Jun 29, 2005

I would like to be able to add subtotals for extended_cost and tax_amt for the following query and then a net_cost that is a sum of extended_Cost and tax_amt for each of the orders I have. I believe I need to somehow use the Group by command, but with so many fields from so many fields, I am not sure where to start.
Here is my code so far...


Code:

SELECT Customer.First_Name,
Customer.Middle_Name,
Customer.Last_Name,
Customer.Address,
Customer.City,
Customer.Region,
Customer.Postal_Code,
Customer.Country,
Customer.Customer_ID,
Store.Store_Address,
Store.Store_City,
Store.Store_State,
Store.Store_Zip_code,
Store.Store_Phone_Number,
Store.Store_Fax_Number,
Orders.Order_ID,
Employees.LastName,
Employees.FirstName,
Item.Item_ID,
Item.Taxable_Nontaxable,
Order_Line.Price,
Order_Line.Units_Purchased,
Order_Line.Discount,
Tax_Table.Tax_Rate,
'Extended_Price' = (Order_Line.Price*Order_Line.Units_Purchased*(1-Order_Line.Discount)),
CASE WHEN
Item.Taxable_Nontaxable = 1
THEN
((Order_Line.Price*Order_Line.Units_Purchased*(1-Order_Line.Discount))*Tax_Table.Tax_Rate)
ELSE
((Order_Line.Price*Order_Line.Units_Purchased*(1-Order_Line.Discount))*0)
END AS Tax_Amt
FROM Orders
INNER JOIN Order_Line ON Orders.Order_ID = Order_Line.Order_ID
INNER JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID
INNER JOIN Store ON Orders.Store_Code = Store.Store_Code
INNER JOIN Employees ON Orders.Sales_Person_ID = Employees.EmployeeID
INNER JOIN Item ON Order_Line.Item_ID = Item.Item_ID
INNER JOIN Tax_Table ON Orders.Store_Code = Tax_Table.Store_Code
WHERE Orders.Order_Filled = 0
ORDER BY Orders.Order_ID
COMPUTE SUM((Order_Line.Price*Order_Line.Units_Purchased*(1-Order_Line.Discount))) BY Orders.Order_ID

View 3 Replies View Related

Making Subtotals

Jun 18, 2007

Hello Everyone,



I have a dimension that has the following members:

Directors

Senior Managers

Managers

Senior Associates

Associates



But they way they need to be displayed includes subtotals. Senior Managers and Managers are placed into Management and Staff is a total of everyone except Directors. They should appear on the report in something like this:



Directors 10

Senior Managers 15

Managers 9

Management 24

Senior Associates 17

Associates 40

Staff 81

Total 91





What is the best way to do this? Right now I am using a matrix because I am also showing headcounts by Period. Is a table a better way to go? Is it something that should be done in my dimension?



I can do the Total part. I am just not sure how to make the subtotals inbetween the other lines.



Thank you for the help.



-Gumbatman

View 3 Replies View Related

Matrix Subtotals

Aug 31, 2006

I have this:

2003 2004 2005 Total
cars apples cars apples cars apples
Tom 3 1 4 2 5 1 16
Sally 0 2 4 1 7 6 20



And I need the last column (the group subtotals) to be like this:



Totals
cars apples
12 4
11 9

So basically, the question is, how do I get the second column-group (cars/apples) subtotals to appear in the last column? As seen, the the subtotal for the first column-group (year) is meaningless.

View 4 Replies View Related

Need Some Help With Subtotals In A Matrix

May 16, 2007

Hello all,

This is all being done under SQL2000 and VS2003

I have a matrix report which is showing user information. The Rows are displaying numbers for each user, and the columns show the user info in weekly increments. I have 7 fields of info for each user. My stored procedure already is set up to give me the correct numbers. I dont need to SUM them or anything. Although in the report designer it forced me to SUM them since it was part of an aggregate. This still worked for me anyhow because it was Summing a single value.

However, at the end of the report i want to display totals for all the users combined, per week. So right now the report is showing 21 weeks, so at the end of the report i should have 21 sets of totals.

I right clicked on the users name column and selected subtotal. This gave me some of what i want. But some of the numbers are not correct. Some of the numbers should not just be a simple SUM of the column. Some of the values should be averages etc. I know how to calculate those values myself (its very simple math) but i dont know how to do it using this setup in the report designer. So in the matrix, for each week, how can i calculate the totals for all the users combined and specify the formula used to get the totals for each field?

thanks

View 1 Replies View Related

Help With Group Subtotals

Feb 8, 2007

I have contracts grouped by project then grouped by division. On the division group header I want to show the subtotal of all active projects only. CountDistinct(Fields!ProjectId.Value) gives me count of all projects.

I can't figure out what I need to get only a subset of projects counted. I need something like:

CountDistinct(Fields!ProjectId.Value & Fields!ProjectStatusId.Value = 1)

but that isn't allowed.

Any help appreciated.

View 3 Replies View Related

Generating Subtotals For Each Group?

Mar 17, 2015

SELECT
LTRIM(LoanAnalyst) AS [Loan Analyst]
,DischargeType AS [Discharge Type]
,CONVERT(varchar, DateCompleted, 101) AS [Date Completed]
,COUNT(ClaimID) As [Completions]

[Code] ....

This produces this

Loan AnalystDischarge TypeDate CompletedCompletions
Bill ReidType 1 3/3/20151
Bill ReidType 1 2/11/20151
Bill ReidType 2 3/11/201518
Bill ReidType 3 3/11/20151
Bill ReidType 4 3/11/20159

[Code] ....

I would like my results to look like this. I've tried using WITH ROLLUP but it doesn't give me the below.

Loan AnalystDischarge TypeDate CompletedCompletions
Bill ReidType 1 3/3/20151
Bill ReidType 1 2/11/20151
Bill ReidType 2 3/11/201518
Bill ReidType 3 3/11/20151
Bill ReidType 4 3/11/20159
30

[Code] .....

View 3 Replies View Related

Custom Subtotals In A Matrix

Apr 23, 2007


FY 2006
FY 2007
Total

1ST QTR
2ND QTR
3RD QTR
4TH QTR
Total
1ST QTR
Total

Customer1
Count1
290
271
233
207
1001
200
200
1201

Count2
111
110
123
118
462
113
113
575

Customer2
Count1





12
12
12

Count2





9
9
9

Customer3
Count1
616
540
513
526
2195
531
531
2726

Count2
362
299
324
368
1353
347
347
1700

Customer4
Count1
7
12
15
22
56
26
26
82

Count2
3
1
7
9
20
9
9
29

Customer5
Count1

4
9
2
15
3
3
18

Count2

1
1
1
3
3
3
6

Customer6
Count1
1
0


1


1

Count2
0
0


0


0



I have the matrix above that displays the subtotals per customer(group_row), per quarter(group_column). I added a subtotal to the top group_column (year) so I can get grand total. I need to modify grand total formula to display variance instead.



I don't need to modify the subtotal formula per Customer/quarter , only per cutomer/Year. Is it possible?

View 12 Replies View Related

Page Subtotals (Not Cumulative)

Jul 30, 2007

Hello,

I want to display subtotals for a column only for that page. Like;

Index Value
-----------------------------
1 4
2 5
Subtotal 9
----------------------------
3 1
4 2
Subtotal 3
Total 12


RunningValue gives cumulative totals. I need subtotals for each visible page only. Is there a way to do it ?
Constraints:
I'm using a table. And I shouldn't use page breaks on my report.

Thanks in advance

View 3 Replies View Related

Expressions Based On Subtotals

Apr 23, 2007

How do I create calculated fields based on subtotals?

View 6 Replies View Related

Multiple Subtotals In A Matrix

May 8, 2007

I would like to set up a report which would look simular to



Mon Tues Wed Thurs Fri Total Month to date total

salesguy1 10 5 11 10 9 45 120

SalesGuy2 9 1 15 0 0 25 89



I have created a matrix which shows the data upto total.

Is there a way to include the Month to date total after total?



Thanks

View 2 Replies View Related

Subtotals Within A Select Statement

Jan 28, 2008

For the past two hours I've been looking for a solution to my problem. Any help is appreciated. Basically, I need to know the T-SQL Command(s) that create subtotals and grandtotals within a set of results. I am designing a school transcript generator and I need to calculate the GPA and Credit Hour Totals per Semester, For Example:

Semester 1 Record
Semester 1 Record
Semester 1 Record
Semester 1 Record
Semester 1 Record
Semester 1 Subtotal
Semester 2 Record
Semester 2 Record
Semester 2 Record
Semester 2 Record
Semester 2 Record
Semester 2 Subtotal
Semester 3 Record
Semester 3 Record
Semester 3 Record
Semester 3 Record
Semester 3 Record
Semester 3 Subtotal
Year 1 Grand Total


Thanks for your help in advance...

View 1 Replies View Related

0's Om Subtotals But Not In Matrix Columns, How?

Mar 5, 2008

Hi
I have a matrix report. It expands to the right when the user chooses the amounts of months he/she wishes to see. Then it is populated with revenues for each month and deportment. On the matrix report I have put in a couple of Subtotals. Some of the totals are empty due to a month in a department with no revenue.

I wish to see a 0 on the subtotal column but I don€™t want to see a 0 on the month column. If I use
=IIF( Fields!Revenue.Value is Nothing, "0", Fields!Revenue.Value)
on the month columns I will see 0 all over the place. But I only want a 0 on the subtotal column. Can I do this?

Thanks in advanced
Kind regards
//Javier

View 5 Replies View Related

Calculating Subtotals And Using Where With Union

Feb 26, 2008

I am having some trouble with this very complicated stored procedure. It is for a transcript generator. The following code does work as it is. The stored procedure return all rows from the ClassGrades table, with subtotals for each semester and a grand total. The two things that I need to add to this stored procedure is:

The ability to query only the records, subtotal, and total for the studentID in the variable @StudentID. I have tried adding WHERE statements where they are usually found, but ends up return no rows as a result. Should I be using the HAVING statement?

The calculation of cumulative GPA, where each semester before affects all semester at a later date. If this is impossible, which to my knowledge is, its not required. Its just a nice thing to have.

Here is the current code:

ALTER PROCEDURE [dbo].[ClassGrades_FillStudent]

@StudentID text

AS

BEGIN

SELECT StudentID, SemesterCode,ClassID, Grade, CreditHours, QualityPoints FROM (

SELECT 0 AS sort1, StudentID, SemesterCode, dbo.DropDownItems_GetValue(ClassID) AS ClassID, Grade, CreditHours, Grade*CreditHours AS QualityPoints

, 0 AS sort2 FROM ClassGrades

UNION ALL

SELECT 0,Null, SemesterCode + 'TOTAL',Null,(SUM(Grade*CreditHours)/SUM(CreditHours)), SUM(CreditHours), SUM(Grade*CreditHours) AS QualityPoints , 1 FROM ClassGrades

GROUP BY SemesterCode + 'TOTAL'

UNION ALL

SELECT 1, Null, 'Grand Total',Null, (SUM(Grade*CreditHours)/SUM(CreditHours)), SUM(CreditHours), SUM(Grade*CreditHours) AS QualityPoints, 1 FROM ClassGrades



) a





ORDER BY Sort1, SemesterCode, Sort2

END

Thank you in advance.

View 4 Replies View Related

Create Subtotals Dynamically

Apr 3, 2007



I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals



i.e.

col1 col2 col3

aaa 111 2005

bbb 222 2006

aaa 333 2006

ccc 444 2005

ccc 555 2006

bbb 666 2006

bbb 777 2006

ddd 888 2007



2005

aaa subtotal: 1

ccc subtotal: 1

total: 2

2006

aaa subtotal: 1

bbb subtotal: 3

ccc subtotal: 1

total: 5

2007

ddd subtotal: 1

total: 1

grandtotal:

aaa 2

bbb 3

ccc 2

ddd 1

totalnumber 8

View 1 Replies View Related

Using Matrix Subtotals In Expression

May 8, 2008

Hi all,

I'm wondering if its possible to use the subtotal for one group in a matrix in an expression for another group in the matrix.

Im getting the total number of units someone holds in one column, and need to show the percentage of the total units in another column.

An example of what im doing is below... argh images dont seem to be working when you post then! sorry





the first group is Date - This will show all there quantities anytime a trade occurs.

the second and third groups are Quantity (of units) and % of fund.

Quantity is a running value of all the units the account holder has. The percentage needs to be that number / subtotal of all units on that date. So the expression needs to be something like:


=runningvalue(Fields!Quantity.Value, sum, "matrix1_Account_Reference") / --subtotal of date group-- * 100

any ideas?

Thanks

View 1 Replies View Related

Conditional Matrix Subtotals

Apr 9, 2007

Let's say I have raw data that looks something like this

Fruit_name status count
apples Fresh 5
apples rotten 3
pears Fresh 3
pears rotten 2

and I was to matrix it and group on fruit_name and add a subtotal to count. In that subtotal, without changing what was displayed in the details number, could I conditionaly only show a sum of fresh fruit? Example below


Apples 5
Apples 3
-------------------
Total 5

Pears 3
Pears 2
----------------------
Total 3

in a nonmatrix situation I'd use something like

sum(iif(status="Fresh",count,0))

Thanks for the help.

View 3 Replies View Related

Grand Total

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

Monthly Subtotals From Date Range

Dec 5, 2007

Hi,

My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'

I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this

Maybe a case?

Thanks for your help guys. Let me know if any more info would help.

View 1 Replies View Related

Monthly Subtotals For A Date Range

Dec 5, 2007

Hi,

My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'

I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this

Maybe a case?

Thanks for your help guys. Let me know if any more info would help.

View 1 Replies View Related

Parent Child Subtotals / Reverse

Apr 4, 2008

Hi

I'm working with parent-child hierarchies. My source is AS2005. What I have now is:
+ parent 1
child 1.1
child 1.2
child 1.3
+ parent 2
child 2.1
child 2.2

What I would like is:
child 1.1
child 1.2
child 1.3
+ parent 1
child 2.1
child 2.2
+ parent 2

This would allow me to simulate subtotals for the hierarchy in a more financially intuitive way. I call it a simulutation because the optimal solution would be to use a normal group for the parent-child hierarchy and then apply group-headers and -footers. However, this does not seem possible and the documentation I've found also describes parent-child hierarchies wrt. the details-group only.

I have thought sorting the data - perhaps on levels, but I haven't got anything to work yet. One of the things that annoys me the most when developing in RS2005 is that it's very hard to debug the layout. For instance, when I apply sorting - first ascending then descending - and the result is the same I get no messages telling me what's wrong (which there obviously must be...).


Best regards Mikkel

View 5 Replies View Related

Matrix Report And Customized Subtotals - Can It Be Done?

May 31, 2007



I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.

The data used for the report is like following (INV is sold and FC is forecasted):












rtype
region

week

wgt

INV
EU
1
150

INV
US
2
200

FC
US
2
400

FC
US
3
1000

FC
EU
2
100



I want the report to show data like this:












WEEK 1

WEEK 2

WEEK 3

TOTAL


INV

INV

FC

FC

INV

FC

EU
150

100

150
100

US

200
400
1000
200
1400



So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:








WEEK 1
WEEK 2
WEEK 3
TOTAL

INV
INV
FC
FC


EU
150

100

250

US

200
400
1000
1600



How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.

I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.



Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?



Thank you in advance,

Wapper



View 3 Replies View Related

How To Have 2 Subtotals Under Column Side For Reports?

May 9, 2007

Hi to all,



I am a newbie to SQL Report 2005 and currently i am required to a report with a format something like this.



Month Total Total (%)

Employee A

Employee B

Employee C



Currently, i am able to generate a table with [Month], subtotal[Total], Employee rows...



I do not know how to display another subtotal of [Total(%)] which calculates the percentage of the Total (i.e, Total/GrandTotal x 100%)



Any advice is appreciated.

Thanks



Rgds

Eric

View 3 Replies View Related







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