RunningValue Problem
Nov 15, 2007
I have a question about using RunningValue.
My data has multiple records for one ID. But I only want to have a
runningtotal based on one value from each ID and grouping by area.
To illustrate my problem,
Area 1
ID 1 4
ID 1 4
ID 1 4
ID 1 4
ID 1 4
ID 2 1
ID 2 1
ID 2 1
ID 2 1
I would like to have the runningtotal to be equal to 5 and not 24.
If I use =RunningValue(Fields!Amt.Value, sum, "Area"), I will be
getting 24.
Currently I'm using a custom code to get the value in the ID group footer but I can't pass
the value to the "Area" group footer.
I tried using ReportItems to pass the value out but received an error.
The error message is given:
"The Value expression for the textbox 'textbox100' refers to the
report item 'textbox28'. Report item expressions can only refer to
other report items within the same grouping scope or a containing
grouping scope." I also tried using code.Total (Total is the global variable holding the sum) at the "Area" group footer but the value is "delayed". Meaning total of Area1 = 0, Area2 = Total of Area1 and so on.Any idea on how to shift the total up?
I also tried RunningValue(Fields!Amt.Value, MAX, "Area"), it only gives me
the maximum value in the Area group, not the running total of all the
maximum values of each ID.
As for RunningValue(Fields!Amt.Value, Count, "Area"), it gives me the
total rows in the Area group.
Is there a way to sum on distinct ID in the Area group? Or inserting
an iif condition in the runningvalue? I tried
Runningvalue(iif(First(Fields!ID.value), Fields!Amt.Value, Nothing),
sum, "Area") since I wanted the first value of each ID but it says it
cannot have an aggregate function within an aggregate.
Is there a method to write something along the line of >>> iif(Fields!
ID.Value = distinct, Fields!Amt.Value, Nothing).
Using Select Distinct in the query doesn't help because the other fields are different and I need to display all the records.
Can anybody help me?
View 3 Replies
ADVERTISEMENT
Aug 1, 2006
I am having problems with the following -
this represents the dataset
Date
Customer
Voucher Number
Document Amount
Check Amount
7/15/2006
Company G
101
15.00
45.00
7/15/2006
Compnay G
101
25.00
45.00
7/15/2006
Compnay G
101
5.00
45.00
7/18/2006
Company A
102
35.00
35.00
7/25/2006
Company M
103
45.00
50.00
7/25/2006
Company M
103
15.00
50.00
This represents the final report
this is what I am getting
Date
Customer
Voucher Number
Document Amount
Check Amount
Check Amount
(Hidden)
Group 1 Header = Voucher Number
(Hidden)
Detail
7/15/2006
Company G
101
15.00
45.00
45.00
(Hidden)
Detail
7/15/2006
Company G
101
25.00
45.00
45.00
(Hidden)
Detail
7/15/2006
Company G
101
5.00
45.00
45.00
Group 1 Footer
7/16/2006
Company G
45.00
45.00
135.00
(Hidden)
Group 1Header = Voucher Number
(Hidden)
Detail
7/18/2006
Company A
102
35.00
35.00
35.00
Group 1 Footer
7/19/2006
Company A
35.00
35.00
35.00
(Hidden)
Group 1 Header = Voucher Number
(Hidden)
Detail
7/25/2006
Company M
103
45.00
50.00
50.00
(Hidden)
Detail
7/25/2006
Company M
103
15.00
50.00
50.00
Group 1 Footer
7/25/2006
Company M
103
60.00
50.00
100.00
Report Footer
Grand Total:
140.00
130.00
270.00
(I am using a table)
I can get the correct total for the Group 1 Footer Check Amount by dividing the Total by a count of the Voucher Numbers, but I have not been able to add the Group 1 totals of the Check Amount for the Grand Total in the footer. I have made various attempts using RunningValue with the Group 1 Check Amount Total field, but always getting a error - mostly that the scope was not correct.
Any suggestions will be appreciated.
View 4 Replies
View Related
Jan 10, 2007
I have a form that uses some header information (like client ID with name, address, etc) and then uses a table for orders that customer has placed. I now need to print something like a page number (but not in the header or footer). When I try RowNumber, this does not work as the dataset is returning multiple records for the first customer and then the next item in the list (the next customer ID) skips the number of records from the first. So I need the first one to have 1, the second to have 2 and so on. I tries RunningValue, but for each record it starts back at 1. What I need is like a counter.
How would I go about this and thanks for the help.
View 7 Replies
View Related
May 14, 2007
I have a couple of columns in a table where I would like to summarize the total value of that column in the group's footer. I also want to filter that total just by that column, so I am creating a RunningValue statement like this:
=RunningValue(iif(Fields!TypeClass=351154, Fields!ValueAmount.Value, 0), Sum, Nothing)
This works great with zero (0) values, however on the column that filters by the number 351154, the expression produces #error.
Is this because you cannot have a RunningValue in a group footer?
Thanks for the information.
View 1 Replies
View Related
Jan 29, 2008
I've created a CalculatedField in my dataset, set to:
=RunningValue(Fields!Quantity.Value,Sum)
...however when I Preview the report, VS returns the error:
An error occurred during local report processing.
An internal error occurred on the report server. See the error log for more details
...and then blows up, requiring a restart. I've tried this on several different reports with same results.
I must have the RunningValue() as a Field for purposes of creating something like a ReverseRunningValue() (which adds from bottom-to-top instead of top-to-bottom). This stems from the need to perform a Previous() call, which requires a field as a parameter. (If I could use Previous() to refer to a textbox object, then I could put RunningValue() in a texbox instead, but this doesn't seem to work).
Any ideas?
Thx,
Mojo
View 1 Replies
View Related
Nov 29, 2007
Hello,
I am trying to create sequential running totals based on the months. I have included the two expression I've been trying to use. I have one group created called table1_MO_NUMBER and four sequential fields that need individual running totals. I am using Reporting Services 2005. The Totals column is the result of the expression. I added an additional column that displays what the running totals should be. Any suggestions would be helpful. Barb
JANUARY
1
MTD NET ACTUAL
2,235
2
PLAN
3,158
3
FCST
3,200
4
LY MTD NET ACTUAL
1,853
Totals
Totals should be.
Act Bldgt
10,447
2235
iif(Fields!SEQ_NO.Value=1,RunningValue(Fields!BLDGT.Value, sum, Nothing ),0)
JANUARY RunningTotals
Plan Bldgt
#Error
3158
RunningValue(iif(Fields!SEQ_NO.Value=2, Fields!BLDGT.Value, 0), sum, Nothing)
Fcst Bldgt
3,200
Pr Bldgt
1,853
FEBUARY
1
MTD NET ACTUAL
2,512
2
PLAN
3,580
3
FCST
3,200
4
LY MTD NET ACTUAL
5,744
Act Bldgt
25,483
4748
FEBUARY RunningTotals
Plan Bldgt
#Error
6739
Fcst Bldgt
6,400
Pr Bldgt
7,597
MARCH
1
MTD NET ACTUAL
3,887
2
PLAN
3,780
3
FCST
3,200
4
LY MTD NET ACTUAL
4,494
Act Bldgt
40,843
8635
MARCH RunningTotals
Plan Bldgt
#Error
10518
Fcst Bldgt
9,600
Pr Bldgt
12,091
View 4 Replies
View Related
Jul 3, 2007
Hi,
I am creating some earned value reports that display project budget, actual cost to date, projected cost, and earned value. I have all of the data stored by month so I am using the Running Value function to display the data on a graph such that at any given point on the graph, it is showing total dollars as of that date. This works great for a single project or a summation of projects.
I also want the ability to create a batch set of reports for a series of projects, basically they input a list of project numbers, and a new graph is generated for each one. To do this, I created a "batch report" that has a subreport in a table's group with project id being the grouped on field, it passes that project id to the subreport as input.
I get exactly what I want - a new report for each project, but the problem is, running value on the graphs is not working across the projects. It's fine for the first project, but all subsequent projects are getting messed up. For instance, one project with a max budget of 200K is showing with a max budget of 400K, actuals are getting nulled out, etc. It's as if for some reason, the RunningValue isn't getting reset across the subreport....
An example of my value field on one of the lines (not copy and pasted so forgive any syntax errors - I'm not on my development box):
Code Snippet= Iif(MAX(Fields!actual_de.Value + Fields!actual_work.Value) >0, RunningValue(Fields!actual_de.Value + Fields!actual_work.Value, Sum, FakeSeries), Nothing)
(The point of the max is so that the graph stops on months without actuals - that is months that haven't occured yet)
My FakeSeries is just on projectID and is just for the purpose of resetting the Running Value (or so I thought).
Does anyone have any input or advice on how to get this working? Why is RunningValue getting messed up across subreports? If I only give the batch report one project ID, the data is great. (I know it's the running value because if I take out running value and just graph the SUM - all the values are correct). If anyone has an alternate way of doing this sort of batch report I'm all ears as well!
Thanks much!
Jen
View 1 Replies
View Related
Jan 10, 2007
I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.
The data for the report, in raw form is:
Value 1
A
Value 1
B
Value 1
C
Value 2
D
Value 2
E
Value 2
F
Value 3
G
Value 3
Hetc...
In its aesthetic form, it is:
Value 1
A
B
C
Value 2
D
E
F
Value 3
G
H
My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?
Value 1
1
A
2
B
3
C
Value 2
1
D
2
E
3
F
Value 3
1
G
2
H
Thanks for any input!
View 3 Replies
View Related
Apr 22, 2008
I've read a bunch of threads and can't seem to find an exact problem as what I'm having.
I believe my problem is simple enough to understand, but might not be that simple to solve. I'm hoping otherwise.
My report shows columns:
CustomerNumber
CustomerName
SummedInvoiceAmounts (summed from SQL query)
Region
The problem is that we have the same CustomerNumber with multiple CustomerNames:
CustomerNumber CustomerName Sum Region
ABC advancedballoon 1000 East
ABC AdvancedBall. 1000 East
As you can see they are the same company but in our Accounting system they show up mulitple times (about 20 companies like this). This is from clerks adding in multiple ways or misspelling, etc. Can't fix, but have to work around.
Anyhow, I have my detail row that shows two lines for the companies like this and one line for the companies that are correct. I have this report I have added two groups "Region", "CustomerNumber".
If I put all the detail Fields down in the CustomerNumber footer and hide the detail row the report looks fine to the eye. When adding a Sum down in the Region footer field though; it pulls in the added amount and makes the total wrong.
I tried running value, but it's basically the same as the sum for what I need and it doesn't work. Is there a simple way to do this using the reporting tool? In Crystal this was very easy, but I haven't figured this out yet. Any help would be greatly appreciated. Thanks.
View 1 Replies
View Related
