Calculated Field Using Two Data Sets

May 12, 2008

Hi,

I'm trying to created a report.
Final report looks like this.







Total Loans/Lines (#)
13,283



Total Commitments ($ MM)
$1,703



Total Outstandings ($ MM)
$1,175







A
B
C
D
F

Bankruptcy
0
$0
$0
0.00%
0.00%

Charge Off
0
$0
$0
0.00%
0.00%


Source table looks like this;







Bankruptcy



0

Charge Off



0

CLTV
131

DSR
102

Exc Total
265

FICO
7

Foreclosure/Repossession

Grand Total
13283

Loan Amount
32


Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)

But it does not let me to use report expression as its not in the same scope.

Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.

Thanks

View 15 Replies


ADVERTISEMENT

Adding A Calculated Field By Using Two Fields From Different Data Sets

Oct 1, 2007



Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)

I tried to do that. But I got a error message.




"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."


Please can some one help me out?

View 5 Replies View Related

Analysis :: Create A Calculated Set From Union Of Values In Two Sets?

Oct 26, 2015

I have the following MDX Query:

Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube

which produced the following table:

Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00

[code]....

What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.

Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')

When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"

Then, if I was to execute the new query:

with

set NewSet as "Some Unknown Magic Here"

Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube

I would end up with 

Division
 NewSet
Dollars
AA
CustomType1  
$166.64
AA
CustomType3 
$64.24
BB

[code]....

View 2 Replies View Related

Index In Calculated Field From A Ntext Field

Nov 8, 2005

For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.

I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.

To trick the sorting I use a calculated field:

ORDER BY RSort - where Rsort is:

convert(varchar(4), RD.response) as RSort

It works but put a high load on the SQL server when the number of responses increases.

I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.

What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:

View 3 Replies View Related

Calculated Field

May 14, 2008

 HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill 

View 5 Replies View Related

Calculated Field

Jul 25, 2005

I am a newbie to SQL Server using SQL Server 2000.

I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)?
However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).

The fields that I am basing the calculation on are:-

ColumnName Type Size Allow Nulls
PurchasePrice money 8 0
LoanSize money 8 0



this is the code for the calculated field


([loansize] / [PurchasePrice] * 100)


and this is the description of the calculated field that is forced in the designer



ColumnName Type Size Allow Nulls
LTV money 8 1



and the designer does not allow me to change the type.

I would be grateful of any pointers

Regards

Tony

View 1 Replies View Related

IF In A Calculated Field

Nov 8, 2014

How is the below formula written in a calculated field in SQL?

If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if

View 1 Replies View Related

Calculated Field

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI
===================================================
1000|liability | -10,000.00 | 08 | 2005
===================================================
1001| asset | 20,000.00 | 08 | 2005
===================================================
1000|liability | -9,000.00 | 09 | 2005

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger


The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Description Amount
asset 20,000.00
liability (10,000.00)
===========
Net Asset 10,000.00
===========

The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.

Guys, hope someone out there can help me with the sql command for the above report?

View 3 Replies View Related

Calculated Field (GPA)

Sep 26, 2007

I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.

here is a sample table:








Term

ID

LastName

FirstName

CRN

Subject

LetterGrade

Calc GPA


20072

1

Doe

John

1234

CHEM

B




20072

1

Doe

John

3214

BIO

A




20072

1

Doe

John

4321

LAW

B




20072

2

Bauer

Jack

1234

CHEM

A




20072

2

Bauer

Jack

3214

BIO

C




20072

2

Bauer

Jack

5467

FIN

B




A = 4.0
B = 3.0
C = 2.0
D = 1.0

View 10 Replies View Related

Calculated Field

Apr 20, 2007



This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.



What is the simplest way to accomplish this?

View 1 Replies View Related

Calculated Field In SQL 2000

Sep 12, 2007

I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton

View 6 Replies View Related

Calculated Field In SQL Server 6.5

Mar 7, 2001

I am used to working in MS Access where you can return a value as in:
[date1]-[date2]=X

It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record.
I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause.
In Access this would not be a problem.
Can you help?
Thank you.

View 1 Replies View Related

Use Calculated Field In Same Query

Jun 25, 2004

Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example:

SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total

Obviously that doesn't work, but what could I do to get that basic thing to work?

Thanks!

View 4 Replies View Related

Calculated Field And Conditions

Nov 3, 2014

I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.

System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.

Example:

Date of Offence - Number of Points
Aug. 6, 2013 - 6
Feb. 4, 2014 - 4
Apr. 25, 2014 - 4
- 2
May 8, 2014 - 2

Explanation:

On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).

Below are DB Tables:

tbl_Driver
DriverID DriverLicense FirstName LastName

tbl_Event
EventID EventTIme DriverID CarID

tbl_EventViolation
EventViolatinID EventID ViolationID Status (1 = no, 2 = yes radio buttons)

tbl_Violation
ViolationID ViolationName Points

tbl_ViolationClass
ViolationClassID ClassName

I made an SQL Code but to sum the accumulated points only but not the whole condition. See below:

SELECTa.DriverId,
a.DriverLicense,
a.FirstName + ' ' + a.LastName as DriverName,
a.FirstName,
a.LastName,
a.DriverMobileNo1,

[Code] .....

View 2 Replies View Related

Using Calculated Field In Where Clause

Jul 23, 2007

Dear All;

i want to use a calculated field in where clause but this does'nt works: e.g.;

select (colA + colB) as calcField
from tbl
where calcField > 100

what is the right way to do it, as i m getting error;
it works like :
select (colA + colB) as calcField
from tbl
where (colA + colB) > 100

but i m using a complex query as i m getting value from a function and then i hav to use that in where clause as well ;


Thanx in advance,


--
Muhammad Zeeshan

View 1 Replies View Related

AttribDescription As Calculated Field

Jul 23, 2005

Hiin T-SQL,(how) is it possible to concatenate 3 (varchar) fields into one; eitherin a SQL query or through a calculated field (or using a view, ifanybody can explain to me how to use views), according to the followingrules:{first 30 chars of Trim(AttributeVal1)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal2)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal3)}=> define as new field StockItemDescriptionideally I would like SQL Server to do this processing rather thanbuilding all these answer strings on the client side.tiaAxel

View 5 Replies View Related

How To Generate Calculated Field

Jul 20, 2005

I need to run a data report that will query an Access_Table that has thefollowing Project info:My Form has a command_button with the following code:Private Sub Command1_Click()Set db = New Connectiondb.CursorLocation = adUseClientdb.Open "PROVIDER=MSDataShape;DataPROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &"Access_Table.mdb;"Set adoAge = New RecordsetadoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,IIf([Invoice_Date]<Now()-30 And[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROMCust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimisticSet DataReport1.DataSource = adoAgeDataReport1.ShowEnd SubMy DataReport1 has the following RptTextbox:RptTextbox Datafield1 CustomerName2 OrderNo3 Invoice_Date4 Greaterthan305 Greaterthan60Problem arise when trying to display the above report that has an errormessage that says "Datafield Greaterthan30 not found"Is this an SQL Query limitation where Invoice_Date Datafield can only bequeried once & if we have more than one Datafieldwith the same fieldname (Invoice_Date) within the same Data report,thereport will fail to run? Should there be one moreNew Recordset to hold a second Datafield (Invoice_Date)?can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Sort A Calculated Field

Oct 10, 2007



I have a quick question that i have just a simple calc field.

Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.

View 1 Replies View Related

Calculated Field Issue

Jan 22, 2008



Hi I am having a problem with something that was relatively simple in Crystal and i am guessing it is purely my inexperience that is the issue here!

Calculated Field


=iif(Fields!Fun_Debt_Status.Value <> "debt","",

iif(max(Fields!Fun_Days_Overdue.Value)>30,"On Stop",

iif(max(Fields!Fun_Days_Overdue.Value)>0,"Overdue",

iif(sum(Fields!Fun_OS_Amount.Value)> Fields!CREDIT_LIMIT.Value,"Over Credit Limit","ok"))))

Want i am trying to do as you can probably see is create a "Account Status" field depending upon overdue days for payment etc..

My report just produces an error (internal) when i try and run - the calculated field above isn't even on the report.

Please let me know what i am doing wrong! I maybe going at it in completely the wrong way altogether,

Thanks in Advance!

View 5 Replies View Related

My Problem With Calculated Field

Nov 11, 2006

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date


I use SQL Server 2005 Express

View 3 Replies View Related

Calculated Field In A Csv File

Nov 13, 2007



I have a fairly simple SQL code that includes a calculated field (date difference) in Oracle. I am bring the data in a csv format, and its giving me nothing for that calculation. I checked the results in TOAD and they look fine. I know, i am missing something for the format but have tried everything.
I am new to this so any help is appreciated.

Thanks


select rpt.emc_last_run_date "Report Run Date",

(rpt.EMC_ACCEPT_DATE_TIME - rpt.EMC_QUEUED_DATE_TIME * 24 * 60) "Time To Accept(mins)"

View 3 Replies View Related

Referencing A Calculated Field In A Query

Nov 24, 2005

I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005

SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2

This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.

The only way how to avoid the issue in the SQLExpress seems probably to be

- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)

- Utilize computed columns if possible (no idea on performance impact here)

Am I right or is there any other way how to reference a calculated field?

Any suggestion is greatly welcomed! Thanks in advance.

View 1 Replies View Related

SQL 2012 :: Index On Calculated Field With UDF?

May 11, 2015

is it possible to have a field that is calculated using a User Defined Function, and index that field?

View 9 Replies View Related

Re-using A Calculated Sub-select Field In A View?

Jul 23, 2005

I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!

View 6 Replies View Related

Design Question: Calculated Field

Jul 20, 2005

I'm trying to create an OLAP system using SQL Server 2000 AnalysisServices (AS). I want the AS cube to be based on a database with astar schema.I have a field called Ratio. The initial ratio value is based uponthis formula: (CurrentTimePeriodAmount -PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, thatinitial ratio can be manually overridden and replaced. Then the ratiois used to calculate future Amounts.Should I place that calculated Ratio field in the "source" databasedesign or should it be part of the Analysis Services cube?

View 2 Replies View Related

Views - Filtering On Calculated Field

Sep 12, 2007



Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.

Then I stuffed the self joins into a couple views for the events I'm interested in...

The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.

So how can I speed up the filtering on that calculated column?

Or am I going to have to get really creative with unions?

View 2 Replies View Related

Calculated Field Crashes Vs 2005 Sp1 ?

Feb 6, 2007

Hi!

when I'm trying something litle bit more complex thing than string manipulation in calculated field ex: =RunningValue(Fields!SALES.Value, Sum)


It just crashes visual studio when trying to run the report?! I think this could state as a bug in RS?

View 4 Replies View Related

Sort A Report On A Calculated Field??

Apr 15, 2008



OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"

That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this??
Thanks for any advice!

View 17 Replies View Related

Create A Calculated Field That Gives Me The Avg 75 Percentile

Mar 22, 2007

I'm trying to create a calculated field that gives me the avg 75 percentile.

Right now I get this value by doing the following:

Create data set:

Select top 75 percent <field>

from <table>

Then I create the following calculated field

Avg(Fields!<field>.value,"<data_set_name>")

But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.

Is it possible?

Thanks!

 

 

 

 

View 9 Replies View Related

Report - Interactive Sort On Calculated Field

Feb 27, 2008

Hello,

I have a report that calculates a field and therefore it does not exist in the dataset.

Can I perform an interactive sort on the textbox that contains this calculation?

Many thanks.

View 1 Replies View Related

Calculated Field In Footer...running Total

Apr 26, 2007



Hello,



How do I add unique values on the report? For example say I have this in my report:



Customer: Food Purchased: Amount:

Judy Cat Food $12

Sarah Dog Food $13.50

Diane Rabbit Food $17

Jason Dog Food $16

Tammy Dog Food $15



In the footer of the report I want to print a summary box that looks like this:



Product: Number Purchased: Total:

Cat Food 1 $12

Dog Food 3 $44.50

Rabbit Food 1 $17





How do I do this?



Thanks!

View 9 Replies View Related

Transact SQL :: Use Field Alias In Calculated Fields?

Jul 18, 2015

use Northwind
Go
select 
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date 
, dbo.Customers.CustomerID 
, dbo.Customers.CompanyName 
, dbo.Products.ProductName

[code].....

I cannot use the alias field names as part of additional calculations for new columns. 

total_Amount and  Grand_Total cannot be done with my skill level.

View 9 Replies View Related

Creating A Calculated Field From Within VWD? (Visual Web Developer Express)

Feb 27, 2008

I'm trying to create a calculated field using SQL Server Express, from within the Visual Web Developer Express 2008 IED. The field I am trying to create is called "total" and is supposed to be the sum of the subtotal, salestax, and shipping fields.

Is this possible from within VWD 2008? How?


Thank you!!!

View 3 Replies View Related







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