In SRS2000, I have a simple grouping matrix report setup like the following:
Code Snippet
=Fields!Date.Value Total
=Fields!Section.Value =Fields!Count.Value
Total
It's essentially a table of departmental statistics that would report like this:
Code Snippet
5/1/08 5/2/08 5/3/08 5/4/08 Total
Dept1 0 99 33 4 XX
Dept2 1 0 2 1 XX
Total XX XX XX XX XX
The report has 2 groups - 1 for the dates and 1 for the departments.
The report data itself displays correctly. My problem comes in when I try to add the Subtotals.
I simply right-click on each of the two Groups and chose "Subtotal". This action inserts the Total column & row respectively and you would think everything is just fine.
Unfortunately when I actually run the report, I see that the Totals it's coming up with are incorrect. In fact, they are the totals of the FIRST row/column only.
Meaning - (using the previous example) - I'd see this:
It's like it's not taking anything into account except the first instance.
And it's not like you can actually *see* the expression or formula being used for the Total function. The data cells are just grayed.. all you can really change is the "Total" text.
I must be doing something really stupid here, but for the life of me I don't see what it is.
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT CustomerID, SalesPersonID, YEAR(OrderDate) AS 'OrderYear', SUM(TotalDue) AS 'TotalDue' FROM Sales.SalesOrderHeader
I'm sure this is very basic, I'll try not to be too embarrassed asking it.
Given a table called MainTable, and a second table called Details, with a one-to-many relationship between them, which is the better practice in regards to getting a number of values from each MainTable record plus an aggregate of a value from the related Details records...?
--=====================================
--Method 1 --Group by every selected field in MainTable SELECT MainTable.Rec_Num, MainTable.Name, MainTable.Description, SUM(Details.Amount) AS Amount FROM MainTable INNER JOIN Details ON MainTable.Rec_Num = Details.MainTable_Rec_Num GROUP BY MainTable.Rec_Num, MainTable.Name, MainTable.Description
--Method 2 --Group by PK record in MainTable and aggregate the others SELECT MainTable.Rec_Num, MAX(MainTable.Name) AS [Name], MAX(MainTable.Description) AS [Description], SUM(Details.Amount) AS [Amount] FROM MainTable INNER JOIN Details ON MainTable.Rec_Num = Details.MainTable_Rec_Num GROUP BY MainTable.Rec_Num
--=====================================
Or perhaps there's another option I'm not aware of?
I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
My report has two groups, company and error type for each company. Company1
Functional Error Data Error Other Error My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area. Total for Functional Error: Total for Data Error: Total for Company1:
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
How can something like this be done in reporting services? I'm having trouble to get the totals, because if I do Sum(Fields!Yes.Value) + Sum(Fields!No.Value) it will just give me the overall total (133).
Please help guys. I tried with the matrix but it always breaks or just doesn't want to give me the subtotals.
I need to total filtered values each time the filter criteria changes. So lets say I change the month to JAN then in addition to the subtotals for each category I need to get a Grand Total. Thanks much.
SELECT Location, SUM(month_est) AS SubTot_Month_Est, SUM(actual) AS SubTot_Actual, mnth, yr FROM dbo.meetings GROUP BY Location, mnth, yr
Hi,WITH SQL SERVER QUETY, I have output likePRJ item qty=================P1 I1 10P1 I1 20P1 I2 10P2 I2 10P2 I3 10================I WANT TO MANIPULATE IT LIKE THISPRJ item qty=================P1 I1 30P1 I2 10---------------P1 * 40---------------P2 I2 10P2 I3 10---------------P2 * 20---------------===============PRJ * 60===============SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSETCAN ANYBODY HELPTHANKS IN ADV.T.S.NEGI
i am using SQL server reporting services 2000. in that i am using a matrix and one column group and a one row group. i want to calculate total of row and column group and i insert a subtotal at for row and column group.
butits calculation wrong total just picking first row and first column in total.
I want to have a subtotal column AND a subaverage column (or n total columns) like the one in the following example - but without the extra Avg column in the Data area.
I have a problem about my subtotal.I cannot change the background color based on my conditions.But what makes me think is that the font of my subtotal changes, the same as when my cell data meets my condition,without changing the subtotal's property.
What could be the reason why my subtotal wont change?I have a condition in my background =iif(Fields!IsHold.Value = True,"Red","White")
Hopefully this is a simple question. We have a report with several subreports that display certain totals by a group. What we want to do now it to show a running total on the main report. Is there a way to pass data from the subreport (the subtotal) to the main report for a Grand Total?
I am using a matrix to report, and there is 2 rowgroups and onde column group. It looks like this:
T1 T2 T3 T4 type1 A 9 3 2 4 B 6 type2 A 1 2 3 B 2 4 1 7
But I need to sum only 2 columns of the report (columns T2 and T3):
T1 T2 T3 T4 T2+T3 type1 A 9 3 2 4 5 B 6 6 C type2 A 1 2 3 2 B 2 4 1 7 5 C 8
And I do not know how to create this sum column, only the default sobtotal column of a matrix. I tried to do this sum in my datasource, but with no success too. I also tried to use a table, but the columns must be create automatically from database data and I couldn't do it.
Another issue is: how to make line C appear even with only null or zero numbers?
Any ideas?
I am new with reporting services, so please be clear and detailed.
I would like to put a subtotal at the top of the report, before the data row. Is it possible, and how to do that ? Thank you in advance for your precious help.
I do get a warning, about attempting to divide by zero, which is sometimes the case, but when I deploy this report, it looks fine, it is formatted as a percentage and the divide by zero cases show up as 0%, just as I want it.
However, in a similar, second report, I used the same code as above, and I get the same warning as above, but when I deploy I get the dreaded '#error' for every single case.
I cannot work out what is different between the two and why one would work and the other not work...
Is is something really simple that I have overlooked? Anyone got any ideas?
