Bug with InScope() and some custom code

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

This has nothing to do with InScope().

The AddDays function is used within a IIF function call. IIF (like any other VB function call) evaluates all arguments before the function is invoked. Hence, the AddDays function is invoked in all cases.

Suggestion:
1. change the expression in the matrix cell to:
=Code.MyCalculation(InScope("matrix2_Calendar_Year"))

2. add a custom code function MyCalculation which uses IF - ELSE blocks to call the other custom code functions. Only in the case of using the conditional IF statement (instead of the IIF function) you will achieve the desired effect.

-- Robert

|||

Doh, thanks Robert, i should have known that I last did VB a few years ago, i've obviously forgotten a bit :)

sluggy

|||

No problem. I'm glad I could help resolving your issue and there is no bug in InScope :)

-- Robert

Bug when a table is adjacent to a matrix

I am trying to create a report that has a table adjacent to a matrix, with
the tops of each aligned together. I am trying to create a report where the
rows of the table and the matrix "line up" with each other. I have included
an example below where I have selected the customerID, orderID, and shipper
name from the rows of the Northwind orders table. I'd like to put the
customerID and orderID in a table that is adjacent to a matrix where each
column is a particular shipper. I'd like each row of this combination to
show the customerId, orderID with the shipper name in the appropriate
column.
HOWEVER -- When I render this report in HTML, the matrix terminates and
continues to the next page before the table does, causing the two to get out
of sync on the second page. I can't figure out a way to make the table and
the matrix stay aligned from the first page onto the second.
The report below should duplicate this behavior (I assume that you have the
Northwind DB installed).
Thanks,
Michael Carr
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>0.25in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CustomerID_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>1.875in</Width>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
</Table>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID, OrderID</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.375in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CompanyName_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CompanyName_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.25in</Width>
<Left>2in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_CustomerID">
<GroupExpressions>
<GroupExpression>=Fields!CustomerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>0.25in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT
OrderID,
Orders.CustomerID,
Shippers.CompanyName FROM Orders
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>0.25in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
<BottomMargin>0.25in</BottomMargin>
<Language>en-US</Language>
</Report>This looks like a bug to me. I've let the dev team know. Thanks.
Possible workaround: Add explicit page breaks every N rows by adding an
outer grouping by =Ceiling(RowNumber(Nothing)/20)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:%23hRajEjiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> I am trying to create a report that has a table adjacent to a matrix, with
> the tops of each aligned together. I am trying to create a report where
the
> rows of the table and the matrix "line up" with each other. I have
included
> an example below where I have selected the customerID, orderID, and
shipper
> name from the rows of the Northwind orders table. I'd like to put the
> customerID and orderID in a table that is adjacent to a matrix where each
> column is a particular shipper. I'd like each row of this combination to
> show the customerId, orderID with the shipper name in the appropriate
> column.
> HOWEVER -- When I render this report in HTML, the matrix terminates and
> continues to the next page before the table does, causing the two to get
out
> of sync on the second page. I can't figure out a way to make the table and
> the matrix stay aligned from the first page onto the second.
> The report below should duplicate this behavior (I assume that you have
the
> Northwind DB installed).
> Thanks,
> Michael Carr
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>0.25in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.5in</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Order ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>CustomerID_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="OrderID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>OrderID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>1.875in</Width>
> <TableColumns>
> <TableColumn>
> <Width>1in</Width>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID, OrderID</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CompanyName.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.25in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>1.375in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>DataSet1</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_CompanyName">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="CompanyName_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>CompanyName_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>3.25in</Width>
> <Left>2in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_CustomerID">
> <GroupExpressions>
> <GroupExpression>=Fields!CustomerID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>0.875in</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_OrderID">
> <GroupExpressions>
> <GroupExpression>=Fields!OrderID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>3in</Height>
> </Body>
> <TopMargin>0.25in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>8in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT
> OrderID,
> Orders.CustomerID,
> Shippers.CompanyName FROM Orders
> INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>0.25in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
> <BottomMargin>0.25in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>

Browsing a cube in ssrs Matrix

When we browse the cube in management studio we are able to see the correct aggregates where as in the report from reporting services we are just getting the sums instead of running aggregates especially in the intersection cells of columns and rows. ( where ever the aggregrate function is difference/division. )

Is there a setting in reporting services which would make the report return the running aggregates like in the management studio.

thanks in advance

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

|||

--

|||

PedroCGD wrote:

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

Can you eloborate please.

Even when I use the matrix and the wizard still it dosent give me the running aggegates it is just giving me the sum in the intersections cells.

Any thots ?

Borders & Styles

Hi,
I am making a Matrix report & having a tough time getting the borders to
work.
On the column header the side borders are appearing but the top borders
phew, I dont seem to be getting them.
Please help, ThanksThe Borders don't appear on the report when do a preview but when I save it
as Excel they are there in Excel.
Any thoughts ?
Thanks
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:uwPwkIdCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am making a Matrix report & having a tough time getting the borders to
> work.
> On the column header the side borders are appearing but the top borders
> phew, I dont seem to be getting them.
> Please help, Thanks
>