Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lilian
Starting Member
5 Posts |
 Posted - 2007-11-15 : 21:51:51
|
| 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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-11-21 : 01:50:06
|
| I guess you have to do manipulation in the data part of your report. Depending on whether you are using a storedproc or adhoc queries for your dataset, you need to add a temp table on it and store the minimum value of unique column for each repeating ID. Then in final select left join with this table and set a bit field for each matching records ( this way you will distinguish distinct values).ie,for the tableArea 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 use a temp table #temp(ID,UniqueID) where UniqueID can be a column in table with distinct values ( may be its pk)and populate as followsSELECT ID,MIN({dist col}) as 'Uniq_Col'FROM AreaGROUP BY IDthen in final select left join on this temp table on unique col and set bit as CASE WHEN #temp.Uniq_Col IS NOT NULL...In report, then useRunningValue(val(Fields!Amt.Value)* Cint(Fields!{Bit col}.Value), Count, "Area")and you will get running value for distinct records.Hope this should do the trick for you |
 |
|
|
gonzrec
Starting Member
1 Post |
Posted - 2010-04-28 : 13:07:40
|
| Have you figured out a solution to this problem? I'm having the same issue! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-30 : 02:46:24
|
| have you tried what i suggested?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
RunningValue Problem