InScope For Two ColumnGroups
Aug 7, 2007
Hello!
I have the following structure
Task | User | Value 1 | ..... | Value N | InScope Task | InScope User
--------------------------------------------------------------------------------------------------------------
Task1 User1 0 5 true false (!)
User2 5 3 true false (!)
Subtotal 5 8 true false
Task2 User1 1 5 true false (!)
User2 4 5 true false (!)
Subtotal 5 10 true false
Total 10 18 false false
The last two columns show in which row group the rows are. I have two groups "Task" and "User". I need to define for my purposes if the value are belong to user rowgroup. Unfortunatley =InScope("User") always return false. How I can define if the row value belongs to "User" row group??
View 2 Replies
May 16, 2008
In my report I have 3 row groups and two column groups as follows, where measure is number of hours for a given project.
2008/05 2008/06 ........
Demand | Allocated Demand | Allocated
- Project 1 1500 1000
- Department1 500 400
- Employee1 200
- Employee2 200
- Department2 1000 600
- Employee3 300
- Employee4 300
+ Project 2 1200 1000
-------------------------------------------------------------------------------------------------------------------------
Grand Total 2700 2000
My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides.
Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column.
Thanks in advance.
View 2 Replies
View Related
Apr 20, 2007
Hello,
I got a matrix with two group columns, G1 and G2
I have totals for the innermost group G2
Article Size Stock Sales S/S
Shirt 31 1 2 2
33 2 1 0.5
...
Total 3 3 (I dont want a value here)
So i did =IIF(INSCOPE("G2"),Sale/Stock,nothing)
The problem is that all the size lines are considered outside the G2 group so i get nothing in all rows insted of only have nothing on the total.
I have installed SP2 of SSRS 2005
If i use Inscope("G1") it works, but i need to work with the G2
Is this a bug? Or am i doing something wrong?
Thank you
View 3 Replies
View Related
Jun 15, 2015
I have a simple matrix. The row group is schoolname. The column group is tweek. and the data field is thours (sum(thours)). on the right side of my matrix, I want to display the Average hours for all the week columns. SSRS can do a sum very simply, but when I use the avg function, I get erroneous results. Do I somehow use the inscope function in the data field ?? I just don't know. and I don't understand how to use the inscope function.
View 2 Replies
View Related
Jan 22, 2008
Hello All:
I have the following data:
2000 2001
Child Teen Adult Child Teen Adult
Region1 25 40 35 33 55 12
Region2 50 10 40 20 10 70
Total 75 50 75 53 65 82
and I need the following data
2000
Child Teen Adult Child%
Region1 25 40 35 25/(25+40+35)
Region2 50 10 40 50/(50+10+40)
Total 75 50 75 75/(75+50+75)
I was able to get the Child% column and Total row, except for the cell (75/(75+50+75) ) using InScope() operator.
Can any one help me in this regard.
Thanks,
Vishnu
View 4 Replies
View Related
May 30, 2006
Hi guys,
i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).
I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:
=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)
Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:
Month / Year
Sales
Capacity
% Capacity
Avg $/h
February
2006
3842
7706
49.86%
2.86
2007
0
0
0.00%
0
March
2006
4949
8692
56.94%
3.33
2007
0
0
0.00%
0
April
2006
5160
8154
63.28%
3.58
2007
0
0
0.00%
0
May
2006
3309
8348
39.64%
2.22
2007
0
0
0.00%
0
Total
17259
32900
52.46%
0-8*28,28,31,31,30,30,31,31,28
If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).
Here is the custom code used for all this:
Dim numDays()
Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String
Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)
thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function
Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer
lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)
TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function
public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function
sluggy
View 5 Replies
View Related
Sep 11, 2007
Hi, I have a matrix with 2 row groups and 1 column group.
CGroup1 Val1
CGroup1 Val2
Total
- RGroup1 Val1
RGroup2 Val1
In
In
Out
RGroup2 Val2
In
In
Out
- RGroup1 Val2
RGroup2 Val3
In
In
Out
RGroup2 Val4
In
In
Out
Total
Out
Out
Out
I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:
=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?
Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?
View 7 Replies
View Related
