Using RunningValue In Charts With Subreports
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
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
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
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
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
Dec 27, 2007
Hi
I have a subreport to which I am passing values. Now my requirement is only if there are records to be displayed the
report should be displayed or else the report should not be displayed. Is there anyway I can do this. I know
that there are expression for the visibilty tab in sub reports properties. But how do I set it up.
Thanks
Sai
View 1 Replies
View Related
Feb 21, 2007
Here's my dilema. Our organization has two db servers. One is Oracle and the other is SQL Server. Oracle contains our HR information on all employees and SQL Server contains information on various projects that we are undertaking. The HR system (Oracle) contains Employee ID's, Names, addresses, phone numbers, etc. SQL Server has information about the varous projects and Employee ID's of who is assigned to those projects. I have a report that has two data sources (one to each server) and two datasets (each populated from the two datasources). I would like to have a single table that displays the project and the name of the person who's assigned to that project. This would be achieved by linking the tables on similar information, which is the Employee ID. I've received responses in various news groups etc. saying this can be done by using subreports and query parameters but it's never explained how to go about it. Kind of like, I can tell you what you use to fly to the moon, but I can't tell you how to actually go about building the rocket that gets you there. :)
Is the functionality that I'm looking for possible in SSRS 2005??
Thanks,
James
View 3 Replies
View Related
Jun 4, 2007
Hi,
I have a strange problem.
I have developed a report with 2 subreports in the main report.
I am talking about a visual studio environment.
when i test the report and run it local the report works fine. I see my report (multiple pages) with on each page
my subreports included.
When i deploy the report to my reportserver, all goes well and i don't get any error.
When i access the report on my reportserver and i run it, all goes well.
But, on my second and third page of my report, the subreports don't show (Error: Subreport could not be shown).
Does anybody has a explanation for this ?
Greetings
Vincent
View 1 Replies
View Related
Dec 27, 2007
Hi
I have a main report and sub report. The subreport has some fields like SYS, LOC, MSG etc.
But the report should display the values filtered basis on system. i.e. I must have a sub report
for SYS1 , SYS2 , SYS3 and so on. I can create a sub report for each system. But I want to create only subreport and
call them from the main report. How do I do this?
Thanks
Sai
View 5 Replies
View Related
May 25, 2007
I have a main report and I plan to use a drop-down box to select one of four different sub-reports, at a time, based on the selection. These subreports are all based on their different data-sets, due to too complicated conditions which could not be accomodated in a single SQL select. In addition, there are about 9 different filters which are common to all the four sub-reports. These data filters are in drop-down boxes and get populated through their own datasets. I have created and tested the subreports.
My problem is bringing these subreports together in the main report and making them visible based on their selection or otherwise in the main report. Can I use the same parameter I am using to select the subreports, in the drop-down box in the main report, to be passed down to the subreports ? HOW (could someone provide sample code of the lif or IIF statements) ? How does the main report know which dataset to switch to ? And how do I make visible or invisible the individual reports ?
Any help, suggestions and code samples explaining things would be appreciated.
View 4 Replies
View Related
Jul 10, 2007
Hi all!
I need to create a report with a number of subreports. The problem is that those subreports might be different for each datarow.
Is it possible to set the report name for the subreport in some dynamic manner, e.g. extract it from the data?
View 7 Replies
View Related
Jun 18, 2007
Hi!
I have a report (A) that has a list where a subreport (B) is displayed. This subreport includes nested sub-reports (C) which have other nested subreports (D) (globally there are 3 levels of subreports). When I try to preview report A in Visual Studio I get the "Object not set to an instance of an object" but when I try to preview report B everything is ok. I have RS SP1 installed and I have already tried to include a dummy query in the subreports so that they always have a result....
Does any one had a similar error?
Thank you in advance
csr
View 1 Replies
View Related
Feb 12, 2007
Hello colleagues,
I have the following issue:
I am trying to create a report with subreport this way:
1) create report
2) create subreport. Set parameter @AccountName in the SQL query (...WHERE account.name = @AccountName) and in the parameters collection - AccountName, data type string
3) place the subreport into the report and set parameters AcountID = =Fields!name.Value, so that the WHERE clause in the subreport should filter the content according to the field Fields!name.Value.
So far, I suppose this should work. Nevertheless, I get this result in Visual Studio .net:
An error occurred while executing the subreport €˜subreppp€™: An error has occurred during report processing.
Cannot read the next data row for the data set DatasetXYZ.
When I publish the reports into our CRM, I get the main report but the subreports shows: "#Error: The subreport could not be shown".
What can I do to make this work properly?
Any help would be VERY appreciated as I have already wasted several hours with this issue.
Thank you a lot!
Ravie.
View 6 Replies
View Related
Feb 15, 2007
Hi All,
I am new to Rs2005. I have 4 seperate reports, r1, r2, r3, r4,.....etc. I want these reports to be called in sequence in a Master report. I want to make all 4 reports 1 big report. Can this be done, and if so can somebody please point me in the correct direction. Many Thanks in advance.
Kenny
View 20 Replies
View Related
Aug 21, 2007
Hi,
1. Can we acess Datasets from main report to sub report in SSRS 2005?
2. While i preview the report main report and navigate to the sub report the "Back to Parent Report" icon is displayed in the toolbar of the sub report but when i deployed and viewed in Internet explorer it cannot see the "Back to Parent Report" icon. Whether i have do any settings while deployment?
Any help would be appreciated
Thanks
Dinesh
View 1 Replies
View Related
Feb 6, 2007
Is it possible to set up a subreport so that I can determine which report it displays based on parameters? From what I've seen, the report that a subreport displays must be hardcoded... Anyone got any workarounds?
View 1 Replies
View Related
Nov 30, 2004
Hi everybody,
I've got a quick question.....hopefully :)
I have two reports based on two different stored procedures, they both need the user to enter a begining and end date as parameters.
I would like to have one main report and add my two other reports as subreports.
The problem I have by doing that is it asks for the parameters for the first subreport and then for the second and these parameters are the same for each subreport.
Could someone tell me how to have the parameters asked only once ?
Thank you all any help will be greatly appreciated.
Seb
View 1 Replies
View Related
Aug 21, 2007
I have a main report that calls subreports. When a subreport has enough data such that it will not fit into the remaining area of the current page the renderer inserts a page break leaving alot of ugly white space. Is there any way to sop this behaviour? This seems like a very basic thing.
View 17 Replies
View Related
Sep 14, 2007
I am trying to create a dashboard that is split into the five main focus areas of Customer, Finance, Management, People and Innovation. Each focus area has a main report and a series of subreports (within a panel). I use the €˜Jump to report:€™ property at the domain level €“ which works well.
My problems arise when dealing with the subreports. For example, in the €˜People€™ focus area €“ the panel contains 4 subreports covering; contractor vs. fte trends, gender balance trends, compliance trends and investment trends.
Ideally I would like to have four €˜tabs€™ which when clicked would show the relevant subreport. I know this can be done via a full report refresh, but it looks clumsy and is more complex and costly to maintain.
I am happy for any method that might change the €˜Subreport€™ property dynamically or set the visibility of the objects. I want to avoid using the toggle capability as this could result in two or more subreports conflicting and being visible at the same time.
View 2 Replies
View Related
Jun 8, 2007
We have a bunch of small single pupose reports (individual charts, tables, etc.) that we used as subreports across several larger reports. I want to sperate the subreports into their own folder on the Reporting Server and I tried to tdo this by creating a solution with subprojects and specifying different publishing folders. That seems to be going well, but when I want to add a subreport from one project to a report in another project Visual Studio doesn't seem to like this at all. It won't let me drag them to the layout surface.
Shoudl this work? Is there something different I need to be doing since I'm working with multiple projects now?
Thanks,
-p
View 15 Replies
View Related
Feb 4, 2008
hai,
i was created report with 6 sub reports for muliple columns driil down as based on client requirements, and also iam using one subreport with in the all 6 sub reports,when run the report i got the correct data. my report output having total 11 fields. with in the 6 fields iam using the 6 sub reports, with in the 6 sub reports iam calling the another sub report.
while exportin my report into excel sub reports are not shown, in the place of sub reports i got the error that is "
subreports within a table or matrix cells are ignored"
my client must need report output in excel format, so how to solve this problem. in my analysis excel is not support sub report feature, is there any diffrent method or is there or any third party control is there
please give me solution how to solve this probelm.
Thanks in advance
jacks v
View 3 Replies
View Related
Jun 18, 2007
We have a recurring project status report that uses a matrix (each matrix group is for an individual project). The name of the project renders fine and displays in the group, but when we try to pass this same value as a parameter to a subreport (a graph) which we also want to display in the matrix the subreport is only renderd for the last instance of the matrix group (i.e. 4 of 4 has it but 1-3 show a blank field in the matrix).
I assume this has something to do with using parameters within the matrix control, but didn't find any posts about that.
I should add another way we've tried to do this is with a list instead of a matrix and it also fails in the same way (doesn't render all of the subreports).
Thanks for the help,
-p
View 3 Replies
View Related
Apr 23, 2008
One of the columns within a table on my parent page contains data that increases the height of the row, (a 'comments' field). Unfortunately a subreport within that table does not expand to fill the entire height of the cell it is in because of the expanded row height, therefore leaving "white space" below it. Some rows have a different background color depending on a certain fields' value but in the column where the subreport is, I can't set the background color in the properties window because that attribute has been removed.
Everything with my report works correctly but I just can't seem to get that white space to change to the correct color.
I've tried the following so far with no luck:
1. Setting the background color of the table row. This option is not available within the properties dialog box. I assume because the row has a subreport nested in it. (Can I set the background color somewhere else?)
2. I passed the row color variable as a parameter to the subreport but obviously that only colors the subreport and not the whitespace underneath it within the table row.
My report looks goofy with a whole row one color except for the whitespace under my subreport in each row so if anyone could point me in the right direction I'd appreciate it.
Thanks.
View 5 Replies
View Related
Dec 11, 2007
I'm looking for advice on how to approach a reporting task I've been given. The report needs to be grouped by one of the fields in a view. The view contains patient data. Within each group, the report has to show the results when the view is processed for patients under 18 and another set of results when processed for patients 18 or older. Should I create a subreport for each age group? How would I show both age group sections under the same group? I've heard that subreports are resource-intensive. Is there a better way? Data regions? How would that work?
Thanks!
View 1 Replies
View Related
Feb 19, 2008
Hi everyone,
For a project I'm working on right now we issue a bunch of 2-4 page PDF reports (a main page and a subreport) for every entity within a retail hierarchy. Our client would like to have some of those reports rolled up into a single file so that starting in the second level of the hierarchy there is one file for each member and all its ascendants/descendants. In order to do this, I'm putting the existing report into a list on a new template (as a subreport) and creating a dataset for the list to pass the parameters into the subreport. I initially had to do this for 1000+ stores and realized that it was going to take a LONG time (the main page report can take as long as a minute to run as it's kicking off stored procs that retrieve a ton of data from a cube), so I stopped it and restricted my list's dataset to 3 rows to create a 3-report rollup for testing. When I previewed the report, it showed up after a minute or so and it looked fine - everything was where I expected it to be, pages were breaking properly, etc. However, when I tried to export that into PDF it hung forever. I cut the dataset back to 2 records and had the same problem, so then I cut it back to 1 record and it exported just as quickly as it always does. Does anyone have any information on why this is happening and what I can do to get around it? It's going to be a pretty critical component of this project for us.
Thanks in advance,
Adam
View 1 Replies
View Related
Apr 25, 2007
I have Report with subReport,
in the designer its looked well, but when I export the report to excell format
I get this error,
is it poosible export report with sub report to excell?
thanks!
View 33 Replies
View Related
Nov 19, 2007
Hello All,
I have a subreport that has interactive sort on one of the columns in a table. The interactive sort works well when i run the subreport. But when i run the parent report that contains the subreport then the interactive sorting does not work.
Has anyone come across this issue, any help will be greatly appreciated.
Thanks a lot!
Mohit
View 16 Replies
View Related
Dec 2, 2006
I've implemented quite a number of CRI ( Custom Report Item ).
If I dropped it on a matrix so that I can have multiple CRI output images rendered it works well.
However, I'm trying it out on a table in which I've included a subreport. The subreport is a report that uses the CRI for one record.
I'm seeing only the last output image, the rest of the CRI output image are not shown, instead a red x image is show.
Anyone have any idea why this happens and how I can resolve the problem?
Using the matrix is not an acceptable workaround for me.
View 3 Replies
View Related
Apr 12, 2007
Hello All,
I have 4 subreports that I need to display in the main report. I can pull a sub-report with in the main report howevere the subreport doesn't display any data. It just gives me a blank template like:
Customer name Customer # Address 1 Address2 city
The subreport itself is not empty when I open it otherwise it shows me a list of all the customers however, no data when I call it as a part of main report.
Can anyone suggest me on this one?
Thanks,
Rashi
View 6 Replies
View Related
