Report Server: Division Between 2 Subtotal, Can This Be Done?
Feb 1, 2007
I have a table that have the following fields:
Outlet,EmployeeNumber,WorkingDate,WorkingHour,Ince ntive
when I put it into a matrix report, the Outlet is assiged to Page, the EmployeeNumber is assgined to ROW, the WorkingDate is assigned to Column and both WorkingHour & Incentive is assigned to Data(display in row but not column). There is a subtotal at the extreme right side.
May I know how can I squeeze in another element display at the report showing sum of Incentive / sum of WorkingHour? This new element shall be placed at each row as it's indicating the average incentive of each employee.
I am using SSRS 2005 to develop this OLAP report. I have the "Distinct Count" as a measure and some dimensions in the OLAP cube. I assigned one of the dimension to the "row" grouping and use the wizard to add the "subtotal" to that row level dimension. However it adds up individually the "Distinct Count" values from that row level dimension instead of the "True Distinct Count" of all attributes of that dimension combined. In my report there are "column" groupings so that by creating another dataset just to calculate the distinct count seperately in the report will not work unless there is a way to link this dataset to the column grouping of the first dataset. Also I don't want to create this dataset as I need to pull in the individual "id" field in the dimension in order to do the "Distinct Count" in the report and it is slowing down the performance. Thanks.
I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.
For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.
I'm dealing w/ SSRS 2005. I have my main matrix report which has five row groups.
What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time....so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).
At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.
I have a Matrix report in SSRS and have added the subtotal in the report. Now I would like to change the color of the subtotal to a different color than the matrix report cells. However when I change the color, only the subtotal cell changes color and not the whole row which includes the summarised total. Is it possible to color the entire row in the matrix report?
Please, i need a very urgent help, for this question... tkssss :-)
I need to create a report with dynamic columns. I try use Matrix, but in this time, i dont konw how to calcutate the subtotal e groups in the correct way.
The data for generate the report ( http://xs116.xs.to/xs116/07233/DataReport.JPG ) This is the actual report ( 2 rows groups and 1 columns group ) ok, but how group dates now ? ( http://xs116.xs.to/xs116/07233/ReportInvalid.JPG The correct report i need( if is possible?) (http://xs116.xs.to/xs116/07233/ReportOK.JPG) I need group by Filial (idFilial) Dates (Dta) and inside dates the Dispositivos(NomeDispositivo or IdDispositivo) of the date.... and finally total all by group Filial (idFilial)...
I am currently trying to figure out how to change the background colour of a cell when that row happens to be a subtotal
I have included a screen shot of my report design below:
(The link to the image is http://diagnostic.iipuk.co.uk/images/RS_Questions.JPG should the image not be displayed)
When the cell that is circled is on a subtotal row (i.e. is the subtotal of either the first subtotal (which I have called RegionTot) or the second subtotal (which I have called QCTotal) I want the background colour to change to black). This is so the user cannot see the subtotal for that column on the screen.
I have tried using the following expression in the background colour property for that cell (without success!):
The error that comes up states that report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
I have searched help and haven��t managed to find anything!
Any help would be greatly appreciated!
Thanks
Marek Kluczynski Information Manager Investors in People UK
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:
Hi - I have what seems like a simple problem and I cannot find an answer to it. I'm hoping someone here can help.
I want to divide 2 numbers but have the answer come back with no decimal places. I don't want to round up or down..just give me the whole number and drop the decimal places. For example:
Select units/60 as answer
And units = 280. I want the "answer" to be 4 and NOT 4.66.
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
So am writing a Stored Proc to get all the table values like above ., but i need to write Stored Proc in such a way it returns the above table like result along with subtotal when ever vendor code changed. as below
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
I have a prob with the following code... in that the value of the amount of the year 2001 is 0 so it is showing me division by zero prob can any one change the code and let me know the details
declare @year1 int declare @year2 int declare @month int
set @year1 = 2002 set @year2 = 2001 set @month =9
select case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0 else ( (sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) - sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/ sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) )* 100 end as Percentage, from oper_sundata a, oper_type_new b where a.site_id = b.sun_site
In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X). The problem is that X can be 0 sometimes and then I get an error. How can I prevent errors like this for the case of X=0? In access I would use IIF function, but it doesn't appear in SQL SERVER views. Thanks.
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT, [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END), [FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END), [MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END), [APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END), [MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END), [JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END), [JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END), [AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END), [SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END), [OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END), [NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END), [DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END) FROM PRODUCTION_CMPLT PC INNER JOIN FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND PC.PROD_LINE = FL.PROD_LINE INNER JOIN PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB' GROUP BY FL.REPORT_PLANT
hi in a view, i create a field like field1/field2. in case that field2 is zero, i've got division by zero error. how can i change the value to 0 when field2 is 0 for avoiding get error.
It is common for me to need to create ratios from data in my database such as
SELECT ( list_value / sale_price ) as ratio FROM values
The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned. BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?
I have a table in which i have two colums say discription and counts. the table has 10 rows. This table is created by extracting data from other table means its not a table that exist in system.
Now in my last row i want discription as '%mailed' and count as row1/row2
SELECT [Record Number], Date, Car, Miles_Start, Miles_Stop, Gallons_FillUp, Miles_Stop - Miles_Start AS Miles_Total, Miles_Total / Gallons_FillUp AS MPG FROM Sheet1_Table ORDER BY Date DESC, Car
Hi, I have Visual Studio 2008 Pro / Using VB 9.0 / SQL Server Express 2005. I am making a Miles per gallon calculator for my dad. All Fields /Cells are populated in two records excepting the Miles_Total & MPG. The Miles_Total Comes out fine but... the MPG does not . Any help with this Query would be greatly appreciated! Jeff L
I've got the following SQLSELECT count(*) FROM tablea AJOIN tableb B ON ..etc..WHERE a.string_val = 'test' ANDb.divider 0 ANDa.number 0 AND(a.number / b.divider 0)The b.divider value can be 0, but still I get the division by zeroerror message.I guess the reason is that the database performs the where statementsone at the time?So when performing the division a.number / b.divider it could get 0 inthe divider even if b.divider 0 is also part of the wherestatement??My question is then.. How can I work around this problem? Changing thedatabase to set b.divider always 0 is not an option
I would expect the first one to give me the most accurate result but the second query with a lower precision and scale returns a result with a greater scale.Thanx.
I want to do something simple but I don't know how to do it using MDX. I searched a solution on the web but I found nothing. I want to SUM the result of the division of two measures. I tried using the WITH MEMBER Measures.sum_divided AS SUM(x/y) but the result in the cells are all #Error.
Hi to all, Im using SQL Server 2000. If i divide two integer column values, the result i am getting is integer. i.e Update tblStudents SET Percentage=(AttendedClasses/TotalClasses); AttendedClasses,TotalClasses are two column names(Integer). I hav declared Percentage column as float. The result im getting is integer value.. I want the actual float value(i.e Percentage) How to Resolve this?? Help me with this issue.. thanks in advance.
I have a function that divides the results of 2 seperate datafiffs to provide a ratio. For some reason I cannot get the result to return as a decimal - it is only giving me integers. I have tried cast and converting both the idividual numbers and the results, no luck. If I write the query on it's own, casting the one of the values as decimal will return the right value. Any ideas what I am missing? Here is the function:
Create function fn_FTE( @tkpr varchar(5), @start datetime, @end datetime)
Returns decimal Begin declare
@fte decimal
select @fte =
sum(datediff(dd, (case when eedatebeg < @start then @start else eedatebeg end), (case when eedateend > @end then @end when eedateend is null then @end else eedateend end))) /datediff(dd,@start,@end)
from vw_employ_dates where eedatebeg < @end and (eedateend > @start or eedateend is null) and eudescrip = @tkpr
ALTER view [dbo].[_tec_SOP_POP_link] as select SOP60100.SOPNUMBE as SOP_ORDER_NUMBER, SOP60100.QTYRECVD as QtyReceivedSoFar, SOP30200.sopnumbe as SOP_INV_NUMBER, SOP60100.PONUMBER, SOP60100.QTYONPO as POQTY_REMAININGQTY, POP10110.ITEMNMBR as ITEM_NUMBER, POP10110.ITEMDESC as itemdescription, POP30310.UNITCOST as ReceivedCost, Sum (case when pop30310.ponumber = sop60100.ponumber AND pop30310.trxsorce like 'POIVC%' then pop30310.umqtyinb else 0 end) as TOTALINVOICEDMATCHED, Sum (case when pop30310.ponumber = sop60100.ponumber AND pop30310.trxsorce like 'POIVC%' then pop30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED, from sop60100 inner join sop30200 on sop60100.sopnumbe = sop30200.ORIGNUMB AND sop30200.soptype = 3 inner join POP10110 on SOP60100.PONUMBER = POP10110.PONUMBER inner join pop30310 on sop60100.ponumber = pop30310.ponumber where SOP60100.ORD = POP10110.ORD GROUP BY SOP60100.SOPNUMBE, SOP60100.QTYRECVD, SOP30200.sopnumbe, SOP60100.PONUMBER, SOP60100.QTYONPO, POP10110.ITEMNMBR, POP10110.ITEMDESC, POP30310.UNITCOST, pop30310.umqtyinb
I want to add a column (UNITCOSTINVOICEDMATCHED)that divides TOTALDOLLARSINVOICEDMATCHED/TOTALINVOICEDMATCHED
I inserted the following (which did not work)-
Sum (TOTALDOLLARSINVOICEDMATCHED/TOTALINVOICEDMATCHED) as UNITCOSTINVOICEDMATCHED
error said - TOTALDOLLARSINVOICEDMATCHED column not recognized
The I inserted this statement - thinking I needed another case statement -
Sum(case when pop30310.ponumber = sop60100.ponumber AND pop30310.trxsorce like 'POIVC%' then sum(pop30310.extdcost)/ sum(pop30310.umqtyinb) else 0 end)as UNITCOSTINVOICEDMATCH
this returned the error -
Msg 130, Level 15, State 1, Procedure _tec_SOP_POP_link, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
