Creating Mathematical Formulas And Calculations
Aug 5, 2007
I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2
4
3
6
0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
Any help will be appreciated.
View 1 Replies
ADVERTISEMENT
Jan 16, 2008
I'm creating a report that is designed to be exported to Excel so that the end user can manipulate the data. There are two main columns that I'm concerned with - TimeTaken and OTTimeTaken (for overtime).
Our application does not track OTTimeTaken so it's default will just be 0, but I need the excel file to have a formula that automatically adds the TimeTaken to the OTTimeTaken in a third cell for the total number of hours as that value will be added by the A/R department for invoicing.
I thought adding this would give me what i need:
="=SUM(C" & RowNumber(Nothing) & ": D" & RowNumber(Nothing) & ")"
when exporting to excel, it dumps it in as plain text, requiring that the user double click on the cell in order to "activate" the formula.
Is there a way to have SRS create this formula so that it works in excel rather than requiring the additional user interaction?
Thanks!
View 4 Replies
View Related
Aug 11, 2014
The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.
I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748
[Code]....
View 8 Replies
View Related
Aug 17, 2005
writes "Hi,
I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me.
I would like to create a new table that does calculations on some of the same fields.
•IPA number (from the IPA_Num field)
•Total number of Rx's (sum of rx field )
•Total Paid( sum of amt_paid field)
•Total generic paid (If the field drugs = 1 then sum the amt_paid field)
•Percent generic (If the field drugs = 1 then sum and divide by the sum of the field rx)
•Percent formulary (if the formulary field = A then sum and divide by the rx field)
•Cost difference ( Average amount paid per brand Rx - average amount paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
Yvonne"
View 5 Replies
View Related
Sep 28, 2007
how to round the exponential values.
for example my input is 8.14444367893246e-4
my expected output should be 8.14444367893246
how to achieve this?
View 10 Replies
View Related
Jan 10, 2008
hello sir
Thank for ur response to me in such a way.
I m student of cs in final semester & designing a project for online test series for all subject like mathematics, physics, chemistry, geography. Ihave a pb for storing mathematical questions which includes special symbols in organic chemistry structural farmulas etc.How can we store in database. i m sending a test paper for better understanding. plz help me.I will be hioghly obliged to u for this.
Your's sincerely
Avanish Yadav
View 1 Replies
View Related
Mar 5, 2008
What is the equivalent in Transact SQL for VB.net ^ (Exponent Operator)
I have a following equation in VB.net
DotGain = ((1 - 10 ^(D0 - D50)) /(1 - 10 ^ (D0 - D100))) * 100 - 50
where D0, D50 and D100 are decimal variables
Working on creating a SQL UDF where @D0,@D50 and @D100 are transact sql decimal variables
DECLARE @DotGain decimal
DECLARE @D0 decimal
DECLARE @D50 decimal
DECLARE @D100 decimal
SET @D0 = 0.10
SET @D50 = 0.54
SET @D100 = 1.20
SELECT @DotGain = 1 - POWER(10,(@D0 - @D50)) / 1 - POWER(10,(@D0 - @D100)) * 100 - 50
but it is giving me different result in TSQL than VB2005
Can someone correct me?
Thanks in advance.
View 7 Replies
View Related
Sep 17, 2006
Hi
I would like to is there any way we can store mathematical equations, that can be typed using equations 3.0, in sql server database as a column. What would be the datatype for the same.
Thanks
View 1 Replies
View Related
Feb 20, 2008
Hi,I would like to know if its possible to add/subtract/multiply/divide values between a cell in a database and a textbox/label on a web form? how would I go about doing this? Also, I would like a number from a cell to appear in a label when a page first loads. How can I achieve this?
View 8 Replies
View Related
Sep 17, 2014
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.
I want to perform following without using case if it is possible.
When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty
And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
View 9 Replies
View Related
Aug 2, 2007
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening?
Is there a way to bypass this problem?
Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = ‘test’
Long query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
Short query execution plan:
Execution Tree
--------------
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
View 1 Replies
View Related
Sep 21, 2006
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Looking for a decent source that has some examples of column formulas aswell as a list of column formula functions that can be used and how to usethem.Hard time finding something online. Any suggestions or references would begreatly appreciated.Fairly new to this.Thanks
View 1 Replies
View Related
Jan 24, 2008
ive come from a crystal background and im having difficulty writing reports.
An example of the problem im having is with saved formulas (crystal calls them formulas)
eg
i would create a forumula called Difference
it would be something like this
=DateDiff("d",Field1, Field2) save that and then it was stored
then in a text box i would reference it like so.
=iif( Difference <= 23,"Too New","Just Right")
is this possible in RS??? as i have reports i need to convert from crystal to RS, and the iif statements are becoming huge, especially when you reference the same formula in the expression multiple times.
bit long winded i know, and i hope this makes sense, but i am witts end with this issue
thanks in advance
Jonny
View 3 Replies
View Related
Sep 4, 2007
im moving over from a crystal background, in crystal i was able to create custom formulaes and use them somewhere in the report, can this be done in RS, if so how?
thanks
Jonny
View 1 Replies
View Related
Dec 9, 2005
Sorry, I made a mistake. How can I delete this message?
View 3 Replies
View Related
Apr 28, 2004
I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?
---- Jake
View 4 Replies
View Related
Jun 25, 2006
I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.
Thanks
View 1 Replies
View Related
Dec 27, 2007
Trying to get the blank out of my report and replace with (0). My formula is SUM(Loan Amount). If there is nothing to sum there is a blank space. Would like it to have a (0) in it.
View 1 Replies
View Related
Jul 23, 2005
I'm working with a DB design that seems to me to be rather complex.This is a very slimmed down version of what I'm doing, but I believe itis enough to get my question resolved.Here is my layout.These 4 tables are used to generate a questionaire.Survey OrderID========= ==========SurveyID OrderIDOrderID QuestionGrpIDQGrp Questions============= =============QuestionGrpID QuestionIDQuestionID QuestionTextThe following two tables are used to calculate a report that is sent tothe customer.RawData=========================OrderIDQuestionIDValue is string but is Cast as decimal for numeric formulasMetrics==============================================QuestionGroupID | ReportText | Formula | MetID==============================================2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1The OP references are questionIDsNow to calculate the result for the report we programatically parse theformula creating a temp table (table name = Temp & orderID & _ &QuestionID) with OrderID and OPxx as the field names. We create onetable for each question.We then use dynamic SQL again to calculate the result using the aboveformulaSELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERETemp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =Temp5_23.OrderIDThis select is used to create a single table of calculated values.This table is in turn used to tell the customer how they compare toother customers. Percentile, Mean, Median, Std Dev, and a few others. Idon't claim this part of the project, but I'm not sure how I might havedone it, had it been assigned to me.MY PROBLEM!!!Sometimes a 0 is valid data and is the denominator of a devisioncalculation. Since this is so dynamic and it might be difficult todetermine when division is used. I need a way to default divide by 0execptions to NULL. This DB is on a hosted server.Thanks for bearing with me,Greg Kelley
View 2 Replies
View Related
Mar 19, 2008
Re: SQL Server Reporting Services
I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.
Please help! Thanks!
View 11 Replies
View Related
Apr 27, 2007
Hi,
I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.
Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:
Code Snippet
=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value
This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.
99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...
I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.
Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;
- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)
- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)
Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.
And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.
Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.
*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...
Thanks in advance for any advice
View 4 Replies
View Related
Jul 20, 2010
I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.
View 11 Replies
View Related
Aug 29, 2007
Hello all, I´m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:
iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,
null,
iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,
Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),
Sum([Time].CurrentMember.Children)
)
)
<<this formula overrides the week aggregations>>
and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how?.
Thanks in advance!
View 10 Replies
View Related
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
View Related
May 18, 2007
Hello all. I am trying to do a calculation within an SQL script, however it doesnt seem to be working and i'm a little bit lost. If anyone could shed some light on where i'm going wring it would be much appreciated. The code I have is:
select
EMPLOYEE.EMPLOY_REF AS EDIT_REF,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT,
SV_EMPLOYEE_CURRENT_HOLIDAY.CARRIED_FWD,
SV_EMPLOYEE_CURRENT_HOLIDAY.TAKEN,
SV_EMPLOYEE_CURRENT_HOLIDAY.REMAINING,
SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD,
SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT + SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD - SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED AS TOTAL_ENTITLEMENT
from
EMPLOYEE
left outer join
SV_EMPLOYEE_CURRENT_HOLIDAY
on
EMPLOYEE.EMPLOY_REF = SV_EMPLOYEE_CURRENT_HOLIDAY.EMPLOY_REF
where
EMPLOYEE.EMPLOY_REF = = 027
Incidentaly SV_EMPLOYEE_CURRENT_HOLIDAY is a view which currently exists.
Thanks in advance people.
View 2 Replies
View Related
Jul 1, 2007
In order to find out if an event is late or not I need to do some time calculations in SQL as a Stored procedure.
I have a DateTime variable called Due
I also have an Allowance variable which is an integer and is an extra allowance for that day and a third variable Now which is set with GETDATE()
If I compare Now to Due I can decide if the task is late or not - but I need to take itno account the Allowance.
I tried :
IF @Due + (@Allowance /24) < @Now ......
However I find that @Allowance/24 always equates to zero so this doesn't work.
I'd appreciate any advice.
Regards
Clive
View 2 Replies
View Related
Mar 21, 2008
Hello,
I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database
TitleTimes left todayTimes left
My first excercise!15
My second excercise!19
The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores.
Has anyone an idea how i can solve this?
An example excercise would be great!
Thanks in advance
View 5 Replies
View Related
Jun 5, 2008
I am attempting to construct a SELECT statement which incorporates some variables. The variables begin life as strings (not String objects) looking like :"6/08/2008" and "06/10/2008" for example. The first is a start date which was retrieved using an AJAX calendar object and the second is an end date retrieved in the same manner. My records are all timestamped by MS SQL (2003) including the clock time. I am stumbling on the syntax. "CallStartTime" is the record's timestamp. The "TraversalString" is something else but I am not attacking that yet. Can anyone make a suggestion or two?
SELECT count(*)FROM RealTime WHERE CallStartTime >= '@starttime' AND CallStartTime <= '@endtime' AND TraversalString LIKE '%1.0%'
View 2 Replies
View Related
Jun 5, 2006
Timespan = 30 days
Start Date = January 1st
Last Processed Day = NULL
Next Cycle Day = IF(Last Processed Day IS NULL) Start Date + TimeSpan ELSE Last Processed Day + TimeSpan
Is it possible to setup a column to do this from sql?
View 1 Replies
View Related
Jul 8, 2002
I have a timesheet table and I am having trouble getting a calculation to work correctly. I'd like to subtract the punch-in times from the punch out times for a specific period, such as 1 week, and then add the time together and get the number of minutes worked altogether during that time.
The table is simply
Employee ID (int)
PunchIn (datetime)
PunchOut (datetime)
Thanks for any help.
EL
View 2 Replies
View Related
Jan 4, 1999
I am trying to do a calculation to find rows which have a date which is 2 days older the the getdate().
i.e Select documentdate from table where documentdate < (getdate() - 2)
Any ideas on how I can perform this operation.
Thanks
Vinny
View 1 Replies
View Related
Mar 5, 2007
Hi,
I think that I want to want to build a temporary table in a stored procedure that handles multiple calculations.
I'll try and explain and hopefully you can tell me what i want
First i want to populate a list of all dealerstaff.
Code:
Select * From tblDealerstaff
I Then want to calculate the percentage tasks completed. So i find the number of available awards.
Code:
SELECT TOP (100) PERCENT COUNT(NoPerStaff) AS NoAwardsAvailable
FROM dbo.tblIndivAwards
Then i can find the number awards recieved per person.
Code:
SELECT TOP (100) PERCENT COUNT(dbo.tblIndivAwarded.AwardID) AS AwardCount
FROM dbo.tblIndivAwarded INNER JOIN dbo.tblIndivAwards ON dbo.tblIndivAwarded.IndivAwardID = dbo.tblIndivAwards.IndivAwardID
WHERE (dbo.tblIndivAwarded.staffID = @StaffID)
Trying to get my recordset to look like this >>
Fname + Lname + ((iNoAwarded / iNoAvailableAwards) * 100)
I want to know if it's possible to piece all of this calculation into a single stored procedure. I need to do this sort of thing all the time, as i display overviews of the projects i run to the senior managers.
I always do the maths in the ASP, and consequently i can't sort on the calulated totals.
Is this possible, or am i asking too much ?
Thanks in advance, as any help will be greatly recieved.
Regards,
Chris Brown
View 2 Replies
View Related
