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
ADVERTISEMENT
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
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
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
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
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
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
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
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
Jan 15, 2008
Is there any way to get multiple subtotals in a matrix? For example, one that does a count and the 2nd that does an averages as per the desired result below ...
Code Block
A B C
A 1 2 3
B 2 3 4
C 3 4 5
Total 6 9 12
Avg 2 3 4
View 8 Replies
View Related
Oct 7, 2015
Seems simple. Â People working in departments with different types of hours (regular, overtime, vacation... thus the grid which pivots hours in rows to the appropriate columns). Â I want to subtotal by department, but whenever I try to add totals after to that group, I just get a grand total. Â No subtotals. Â Why?
View 8 Replies
View Related
Apr 16, 2008
Because i get no answers i will try it again:
Hi,
i have the following problem:
I have a matrix with a right subtotal column and this matrix was in a list (because in the end i will have more than one matrix). The list fits perfectly the matrix in design mode. But if i render the report in the viewer or to pdf, an additional blank area (like a blank copy of the subtotal column) was inserted after the right subtotal column of the matrix and increases the list too. You can see this easy by set the backgroundcolor of a list to a color. Without the subtotal column the list fits perfect after rendering. The problem is that this additional blank "column" creates empty pages in .pdf rendering, if the width of the matrix is near the page width. The same behaviour happens if i put the matrix with subtotal in a rectangle.
I must use a list in the end because the the final report contains some matrixes and a subreport.
So is this a bug?
Someone must have this problem too?
Thanks for any help.
View 5 Replies
View Related
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
Aug 22, 2007
Hi there.
I am creating a report that the requirements need different background colors based on the row or column as shown below:
Community
Total # of Respondents
Resident's Overall Satisfaction Rating
Quality of Repair
May '07
41
3.6
5.0
April '07
14
1.8
3.0
Q2 '07
55
2.7
4.0
March '07
36
3.6
3.0
February '07
28
4.0
1.2
January '07
22
2.2
4.0
Q1 '07
86
3.3
2.7
YTD '07
141
3.0
3.2
December '06
33
3.8
4.2
November '06
27
2.6
5.0
October '06
42
1.8
3.0
Q4 '06
102
2.7
4.1
September '06
58
4.0
2.2
August '06
84
2.0
1.6
July '06
52
3.2
3.4
Q3 '06
194
3.1
2.4
June '06
40
2.4
4.2
May '06
41
3.6
5.0
April '06
14
1.8
3.0
Q2 '06
95
2.6
4.1
March '06
67
N/A
3.8
February '06
38
N/A
2.8
January '06
N/A
3.8
N/A
Q1 '06
105
3.8
3.3
YTD '06
496
2.9
3.5
Rolling 12 Month Average
477
2.9
3.3'
I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?
Thanks, Mike
View 1 Replies
View Related
Feb 26, 2007
Hi there,
I'm having trouble inserting a conditional format to a specific column.
e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.
Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.
I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...
View 3 Replies
View Related
Jan 23, 2007
I have a matrix where i'm trying to have the report select the maximum value in the data area (not including the subtotals) and bold that value.
month
day1
day2
day3
day4
total
Jan
10
15
5
12
42
Feb
5
8
3
11
27
Total
15
23
8
23
69
I'm sure i will have to use the IIF function in conjunction with the InScope function.
However i'm unsure on how to isolate to the max value and make sure the Inscope function does not affect the totals.
any idea's are appreciated.
View 3 Replies
View Related
Jun 6, 2006
I am using matrix in my report with calculated fields, for example I have 3 columns : Actual amount, Budget and Variance. Variance should be in Red if it is negative. I can set up an expression to change the color on the row level, but not in Subtotal ot Total row. I cannot use sum of Fields! values, because Budget amount is also calculated field based on the "Category" value which is columns group on the matrix.
So anyway, if I am trying to reference ReportItems!Variance.Value in Subtotal Level, it gives me an error about group scope.
How can I access the cells values in subtotal and total group levels? If anybody knows any tricks for this fairly simple task?
Thanks for your help.
Olga
View 15 Replies
View Related
Nov 29, 2007
Hi,
I'm calculating matrix including totals in the database.
I would like to set a background color for all total cells.
For total cells with now dat in database I fail to put codiotional formating.
I use the following code:
=iif(Fields!Col1Desc.Value="Total", "#60759b", "Transparent")
For cells under "Total" columns that do not got any db value I got color "Transparent" and not "#60759b"
How do I recognize to which column or row a cell belong in a matrix even if it not a value that come from db but empty one?
for example, note the background color of the cell of Male, Total and 2006
I got that matrix:
Year
Gender
Employee Status
2006
2007
2008
Total
Male
Active
0
0
1
1
None Active
0
0
3
3
Total
0
7
4
11
Female
Active
6
5
5
16
None Active
5
3
7
15
Total
11
8
12
31
Instead of that:
Year
Gender
Employee Status
2006
2007
2008
Total
Male
Active
0
0
1
1
None Active
0
0
3
3
Total
0
7
4
11
Female
Active
6
5
5
16
None Active
5
3
7
15
Total
11
8
12
31
View 4 Replies
View Related
Mar 26, 2008
I've been having a problem getting the font-color right on a matrix subtotal. I searched around but could not find anything else describing the issue I'm having. Basically, the problem is stated as such:
IF a matrix subtotal row has conditional coloring based on the data, THEN changes to the subtotal format via "the green arrow" like bordercolor, backgroundcolor, fontstyle, etc, override the conditional formatting.
This only happens when the report is deployed and viewed in a browser. In Visual Studio, the formatting appears as one would expect.
It also only happens when the color is determined conditionally. If the color is explicitly stated, the subtotal format behaves.
Heres a better example:
I was making a matrix with a subtotal that has the classic "red color when the revenue amount is negative" situation:
COLOR:
=iif(Sum(Fields!rev.value) < 0, "red", "black")
All data cells, whether in subtotal or datasection, would be red if the revenue amount was less then zero. At this point, the matrix was almost newly created and this was the *only* formatting applied so far.
Next, I changed the subtotal formating with the green arrow, giving the total and subtotal cells a grey background.
At this point, the report looks fine in the Visual Studio Preview. A negative subtotal is still red. But, when deployed and accessed via IE, the subtotals are black (default) no matter what.
Is this a known issue?? It seems like a bug to me, not a feauture, for at least a couple reasons:
It only happens in a deployed browser, not the designer.
It only happens w/ conditional formatingThe version I'm using is 9.00.3215.00. I also checked on another box w/ version 9.00.3054.00 and it looks like the same problem.
For now, I'm working around it by keeping the subtotal formatting absolutely clean. All formating done in the subtotal is done through =IIF( INSCOPE("...") , , ) checks. This is annoying because the matrix I'm working on isn't anywhere near as simple as the example above, it has crazy row and column groupings with numerous subtotals and pre-existing conditional border/background/color formats. Any ideas?
View 1 Replies
View Related
Aug 13, 2007
Think I have found a bug.
I have a report that has a Parameter called "LevelOfDetail" This has 2 possible values "Summary, Detail, or Combined".
The report has a Summary Section and a subreport that holds the details.
WIthin the summary section is a Matrix (a list of all properties and some values)
If they choose to see the details, the matrix in the details section will show a break out of all this information summarized in the summary section.
The Details Matrix is set to have a page break at the beginning and end of the top level group.
I have a list control on the summary page that contains the details subreport and passes the appropriate parameter.
Everything works the way I want until I try to set visibility on sub report.
Once I set the conditional visibility of the report objects (based on the Level of Detail parameter) the page breaks are not recognized.
This is important as the user will never print the report, but will be downloading to Excel.
If the page breaks work correctly, each page is assigned a different worksheet in their downloaded workbook.
Any help, please let me know.
View 1 Replies
View Related
Feb 6, 2008
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
View 6 Replies
View Related
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
View 1 Replies
View Related
Mar 25, 2008
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies
View Related
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
View 6 Replies
View Related
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
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
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
Aug 6, 2015
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
View 3 Replies
View Related
Jan 10, 2008
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
View 1 Replies
View Related
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
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
View Related
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
Apr 23, 2007
How do I create calculated fields based on subtotals?
View 6 Replies
View Related
