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


ADVERTISEMENT

Power Pivot :: Running Total For Hours Resetting On Change Of Field?

Oct 23, 2015

I have a table called PJLabDet.Total_Hrs which is the sum of hours of 7 days.

I would like to get a running total for this field which would reset when my group (called PJLabDat.pjt_entity) changes.

View 2 Replies View Related

Sum(calculated Member) In Table Footer Returns #Error

Apr 17, 2007

I have a table that contains a column for a calcuated member (x) of type decimal number. When I tried to display the total of this calculated member in the table footer (=sum(x)), I am getting "#Error" instead of the sum of all displayed calculated values.



Column X

--------------

0

0.67

0.10

0.23

#Error (footer cell, expression -> =Sum(x))



=First(x), =Last(x) and =Max(x) worked fine, not sure why Sum failed. Please help...



Thanks.

View 8 Replies View Related

Analysis :: Total Calculation Incorrect When Using IF In Calculated Member

Oct 27, 2015

I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?

Calculated Member:
     CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
        AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
     VISIBLE = 1;

It seems that Total is calculated without checking value in "AverageScore" for that month.

View 3 Replies View Related

Pb With Calculated Measure And Totals (grand Total Or Anysubtotals)

May 20, 2008



Hi,

I've created a calculated measure which is a division between 2 other measures which i also have displayed in the cube.
of course as soon as there is some level of aggregation, the shown result is an averaged division and therefore is wrong.


here is some example:
A 16874
B 956
C 1354
D 264
E 103
F 81
G 6
H 3
X 23
Total 5198.36...

here the wanted result would be a simple some of all the other values (=19664)

How could i treat totals/aggregation differently or have any good way of solving this issue?
I know there is the aggregatefunction propertie for normal dimension, but i'm not familiar with calculated measures and it seems properties that can be accessed through BIStudio's interface is rather limited.


thanks a lot in advance for your help. I've been looking for tweaks on the data to trick the cube, but couldn't find any way of getting the result i'm looking for.

View 1 Replies View Related

Hide Calculated Column In Matrix Report And Show Only In Total

Sep 25, 2007



Hi All,

I need to show the Cumulative calculated value only in Total by year/Group. I could not use Visibility expression using

InScope, as it creates *Blank column. Please go thru details below.



Year
Month01 02 03 Total
Salary Salary Salary Salary Cumulative (Calc)

Employee01 20 5 25 25
Employee02 10 10 20 45
.....
Total


How can i achieve this?. Any suggestion on this would be appreciated.

Thanks,

View 1 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

Hidden Field Populating Header/footer Trick Not Working

Sep 25, 2007

I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here???

View 2 Replies View Related

Running Total

Mar 8, 2006

Help can anyone see anything wrong with this I am trying to get a running total to show on my form for the users to see, as a team member makes a payment they should be able to see the remaining amount

SELECT SUM(DISTINCT TotalPayments) AS Payment, SUM(AmountDue) AS Due, SUM(FinePayment) AS Fine, [TM #]
FROM dbo.FinePayments_Tbl
GROUP BY [TM #]
HAVING ([TM #] = 9282)

View 2 Replies View Related

Running Total In SQL

Jul 24, 2006

Good day,

I have been trying to figure out how to get a running total using my sql statement and just can't.
My current table has 2 fields:
Value
Name

Then there are a whole bunch of entries:
31 Jon
23 George
3 Bob
4 Jon
9 Jon
98 Bob

What I would like is from my outputed SQL is something like:
23 Geogre 23
43 Jon 66
101 Bob 167

Can anyone help me, this is really confusing for me and need it to work. I am not able to change the table layout in anyway what so ever and only have the 2 fields, Value Name to use.

Please help me

View 14 Replies View Related

Running Total

Mar 25, 2008

I'm using the following codein a MSSQL/PHP intranet project for paging through records:



DECLARE
@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT

SELECT@PageSize = $pageLength,
@PageNumber = $pageNumber;

SELECT@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;


WITH results AS


( SELECT


t1.Mortgage_Number,
t1.Advance_Number,
t1.Effective_Date,
t1.Due_Date,
t1.Transaction_Type,
t1.Posting_Type,
t1.Transaction_Amount,
t1.Account_Source,
t1.Sub_Posting_Type,
CASE
WHEN t2.description IS NOT NULL
THEN t2.description
ELSE t1.Transaction_Description
END AS Transaction_Description,
ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber

FROM format_transactions AS t1

LEFT JOIN sd_posting_types AS t2
ON t1.Transaction_Type = t2.Transaction_Type
AND t1.Posting_Type = t2.Posting_Type
AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
AND t1.Account_Source = t2.Account_Type

WHERE (Mortgage_Number = '$mortgage' $additional_date_params)



)

SELECT

Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description

FROM results

WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY [Effective_Date] $direction

View 7 Replies View Related

How To Keep A Running Total

Aug 28, 2005

Hi there. As you will see from my questions, I am an SQL newb. Idabble but never get to spend enough time to get proficient so base anyfeeedback on that basis please. This is all theoretical information atthis point so I am also going to post this in a MySQL related group. Iwill create some designs and post back to the group if I get anyfeedback I can use.Problem:I would like to be able to keep a running percentage total in a fieldassociated with my users. In order to calculate the totals, I willparsing a text file with entries from my users in it. The parser (AWKetc) will search the file for specific text, compare it to informationin another file and output some entries into a csv file which cansubsequently be imported into the database.The users make posts that are considered good and bad and the ratingpercentage must be based on that. For example, if a user makes 10posts in a day, and 4 of them are considered 'bad' by my criterion, therating should reflect a score of 60% for that day.However, the rating is an ongoing value that will be adjusted daily andI must maintain a running total against all previous posts. So, letssay on day two the same user posts 10 more times and 3 are 'bad', Imust adjust his score to reflect a total percentage rating which wouldthen be 20 posts with 7 being bad for an overall rating of 65% etc.My question is, how should I go about recording and calculating allthis information?Here are my thoughts. I have a users table with a field calledsomething like 'Rating' which stores the overall value (65% etc). Thisvalue would have to be calculated from fields in another table like'Posts' which records each post in 'Good' and "Bad' fields thatincrement. The Good and Bad fields would be incremented (populated)from the text that gets imported etc.Looking for thoughts from experienced db designers please. Thanks alot in advance for any responses.

View 5 Replies View Related

Running Total Of Another Running Total

Mar 28, 2007

I have a running total (RunningValue) in one of the column, in my report. I want another running total of that existing running total. This is not allowed in SQL Reporting Services (Aggregate inside an aggregate function is not allowed).

How do I do that? Is it possible to use Visual Basic...using Code function? I'm not familiar with VB syntaxes. Would someone please help me with this... how to pass values to VB code... do the calculation... and display the answer in another column in the report.

Is there any other way of doing it (without using VB)?

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

How To Retrieve Query Results With Now Field Names, Dotted Line Or Footer

Oct 5, 2006

Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....

View 2 Replies View Related

Running Total In Table

Apr 28, 2004

Hi, i have an assets table with a purchase cost column and an updates table with an update cost column.

is there any way for me to add a total cost column to the assets table that will automagically add the purchase cost to the sum of the updates costs for each asset so that when queried, the total cost column can be returned with no calculation in the client?

am i looking at creating a view with the calculation in it?

If so, how do i define the total cost column please?

TIA
Fatherjack

View 3 Replies View Related

Running Total Cursor

Apr 16, 2006

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

View 1 Replies View Related







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