My report has two groups, company and error type for each company.
Company1
Functional Error
Data Error
Other Error
My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area.
Total for Functional Error:
Total for Data Error:
Total for Company1:
Please help me in figuring out how to do that.
Thank you in advance for your help.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. Â So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01Â 2015-12-31 AAÂ 2015-01-01Â 2015-03-31Â BBÂ 2016-01-01Â 2016-03-31Â CCÂ 2017-01-01Â 2017-03-31Â DDÂ 2017-01-01Â 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
How can something like this be done in reporting services? I'm having trouble to get the totals, because if I do Sum(Fields!Yes.Value) + Sum(Fields!No.Value) it will just give me the overall total (133).
Please help guys. I tried with the matrix but it always breaks or just doesn't want to give me the subtotals.
Description
Yes
No
Subtotal
Studies
50
22
72
Works
28
33
61
€¦
€¦
€¦
€¦
I want to create a subtotal for my results in my table, for example:
=Fields!Animals.Value =Fields!Result.Value
Dogs 3
Cats 4
bird 6
SubTotal 13
If i use =Sum(Fields!Result.Value) i have a error #Error, i don't know how do ir, Someone can help me?
Thank you a lot!
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
Hello,
I have a matrix with subtotal.
I want to hide or show the subtotal by a parameter.
The header of the subtotal is not a problem since it have the "visibility" property.
The problem is that when I'm clicking on the green rectangle to go to the subtotal area itself - It doesn't have the "visibility" property.
So I don't see the header but I see the subtotal date itself.
Any idea on how to resolve this?
Thanks.
I need to total filtered values each time the filter criteria changes.
So lets say I change the month to JAN then in addition to the subtotals for each category I need to get a Grand Total.
Thanks much.
SELECT Location, SUM(month_est) AS SubTot_Month_Est, SUM(actual) AS SubTot_Actual, mnth, yr
FROM dbo.meetings
GROUP BY Location, mnth, yr
Hi,WITH SQL SERVER QUETY, I have output likePRJ item qty=================P1 I1 10P1 I1 20P1 I2 10P2 I2 10P2 I3 10================I WANT TO MANIPULATE IT LIKE THISPRJ item qty=================P1 I1 30P1 I2 10---------------P1 * 40---------------P2 I2 10P2 I3 10---------------P2 * 20---------------===============PRJ * 60===============SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSETCAN ANYBODY HELPTHANKS IN ADV.T.S.NEGI
View 2 Replies View Related
hi,
i am using SQL server reporting services 2000. in that i am using a matrix and one column group and a one row group. i want to calculate total of row and column group and i insert a subtotal at for row and column group.
butits calculation wrong total just picking first row and first column in total.
Application
ABC
DEF
123
456
Total
stat
17
15
3
6
17
Config
15
0
12
0
15
CSP - IMS
11
10
0
0
11
Portalm
10
8
8
0
10
Total
17
15
3
6
17
can anyone help me in this regards.
Regards,
Faisal Saleem
Hi
I have the following matrix
CA AZ
No surplus 11 5
Surplus 12 10
Zotal 100 50
Totlal 123 65
I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.
thanks in advance
Hi Everyone,
I am trying to get something like this using matrix in reporting services
SE
CP
CG
Total
Qty
Value
Qty
Value
Qty
Value
Qty
Value
GST
Net
7/07/2006
124
233
552
525
141
2544
563
4215
321
4536
8/07/2006
1245
211
11
142
555
1444
1987
1854
124
1978
The problem is, in the total group, the GST value and Net ( Net = Value + GST) can't added into the total group in the matrix.
Can somebody help... Thanks a lot.
Is there a way to make the subtotal column appear on every page when the matrix spans multiple pages?
View 1 Replies View RelatedI'm still new to reporting services so forgive me for the newbie question...
View 9 Replies View RelatedHello,
I want to have a subtotal column AND a subaverage column (or n total columns) like the one in the following example - but without the extra Avg column in the Data area.
How to do that?
2000
Q. 2
April
Avg
May
Avg
June
Avg
Total
Avg
North
4800
4800
5708
5708
5580
5580
16088
5363
South
3238
3238
3637
3637
3527
3527
10402
3467
West
1699
1699
1687
1687
1614
1614
5000
1667
Thank you!
i would like to return total value for each row and column.
can someone send a sample code?
like:
columnA columnB Total
4 5 9
5 1 6
3 2 5
12 8 20
I have a problem about my subtotal.I cannot change the background color based on my conditions.But what makes me think is that the font of my subtotal changes, the same as when my cell data meets my condition,without changing the subtotal's property.
What could be the reason why my subtotal wont change?I have a condition in my background
=iif(Fields!IsHold.Value = True,"Red","White")
Hello,
I have created a report - matrix from column month, day, week, partnergroup, number.
With subtotals per each criteria.
Current report:
month week day partnergroup sum
1 1 2008-01-01 loans 10
1 1 2008-01-01 mortgages 20
total day 30
....
1 1 2008-01-02 loans 40
1 1 2008-01-02 mortgages 0
total day 40
...
... total week 200
I need subtotals for week with rows for each partnergroup. It should be like this:
month week day partnergroup sum
1 1 2008-01-01 loans 10
1 1 2008-01-01 mortgages 20
total day 30
....
1 1 2008-01-02 loans 40
1 1 2008-01-02 mortgages 0
total day 40
...
... total week loans 110
mortgages 90
Could anybody help me ?
Hopefully this is a simple question. We have a report with several subreports that display certain totals by a group. What we want to do now it to show a running total on the main report. Is there a way to pass data from the subreport (the subtotal) to the main report for a Grand Total?
Thanks for any information.
Hi,
I am using a matrix to report, and there is 2 rowgroups and onde column group. It looks like this:
T1 T2 T3 T4
type1 A 9 3 2 4
B 6
type2 A 1 2 3
B 2 4 1 7
But I need to sum only 2 columns of the report (columns T2 and T3):
T1 T2 T3 T4 T2+T3
type1 A 9 3 2 4 5
B 6 6
C
type2 A 1 2 3 2
B 2 4 1 7 5
C 8
And I do not know how to create this sum column, only the default sobtotal column of a matrix.
I tried to do this sum in my datasource, but with no success too.
I also tried to use a table, but the columns must be create automatically from database data and I couldn't do it.
Another issue is: how to make line C appear even with only null or zero numbers?
Any ideas?
I am new with reporting services, so please be clear and detailed.
Thanks a lot!
Hi All,
I wish to have a report with 2 subtotal using matrix control given by reporting service.
CD
DVD
Total Price
Total Qty
2.00
5.00
JOHN
5
25.00
5
JOLIN
5
10.00
5
Total Price
10.00
25.00
Total Qty
5
5
I only can came out total price(subtotal) which provided by matrix control.
Does the matrix control able to come out as table above with 2 subtotal?
Thanks
Kendy
Hello everybody,
I would like to put a subtotal at the top of the report, before the data row. Is it possible, and how to do that ?
Thank you in advance for your precious help.
Regards,
Yen
i hv finished a report and it looks like the follow:
Status Service A Net in service
Install Disconnect
Completed 100 10 90
Canceled 5 2 3
outstanding 20 5 15
However, actually I just want to display 'Net in service' for 'Completed' status only, like below:
Status Service A Net in service
Install Disconnect
Completed 100 10 90
Canceled 5 2 -
outstanding 20 5 -
Is there anyway to do so? Thank you so much for your help!
In a group footer in a table, I am trying to calculate a particular field. On the first report I have the following:
=Sum(IIf(Fields!annualAmt.Value = 0,0,Fields!amtYTD.Value/Fields!annualAmt.Value))
I do get a warning, about attempting to divide by zero, which is sometimes the case, but when I deploy this report, it looks fine, it is formatted as a percentage and the divide by zero cases show up as 0%, just as I want it.
However, in a similar, second report, I used the same code as above, and I get the same warning as above, but when I deploy I get the dreaded '#error' for every single case.
I cannot work out what is different between the two and why one would work and the other not work...
Is is something really simple that I have overlooked? Anyone got any ideas?
Thanks in advance!
I have a big problem, i need to put my subtotal in right: Tabela aninhada, célula 1Tabela aninhada, célula 2 Tabela unida, célula 3 Column value Column value Row valueDataSubTotal by Row
I want my Matrix showning my SubTotal how this:
Column value
Row value
Data
SubTotal Row
SubTotal Column
My Matriz is how this below: And I don't want it.
Column Value
SubTotal Column
Row Value
SubTotal
Data
Thank you a lot!
Dear guru!
How I can define subtotal by page in the table? Is it possible?
Thanks in advanced.
Hi all,
View 10 Replies View RelatedHello,
Can I hardcode matrix subtotal?
For instance, I want to hardcode "100%" in the following Sales report:
2007 Year
Quantity
Phones 21%
Notebooks 30%
Total 100%
Please don't ask me why I need to do that, it's a complex MDX query and the report is much more complex than I depicted.
I just need to statically write 100% in subtotal.
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
Here I have attached excel sheet. I want to do row wise sum, product and then sub total.
View 4 Replies View RelatedHere's a sample matrix:
Men Women Total
Full Professor 36 12 48
Assoc. Professor 16 9 25
Assistant Professor 11 14 25
Total 63 35 98
Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).
Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?
Thanks in advance!
I have a matrix with both row and column subtotaling. The matrix also has alternating row coloring, which is done using a hidden static row group.
In addition to the alternating row coloing, I'm trying to color the subtotal cells. Using the InScope function I can determine whether the cell is in the Row Group or the Column group or neither.
The problem I'm having is with the coloring of the cells in the Row subtotals. My function correctly determines the appropriate color for each cell in the row total, but the column total cell coloring seems to supercede the color I'm setting on the row total.
Included is the RDL for the AdventureWorks CompanySales report, which I've modified to demonstrate my problem. I've added columns which basically indicate the current scope and the color I'm trying to set for each item in the matrix.
If anyone knows what I'm missing and how to correct this, I'd appreciate the help.
Code Snippet
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>25d3314c-0d4f-49cc-9c22-10194e825490</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>0.5in</BottomMargin>
<RightMargin>0.5in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.0625in</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="Title">
<Left>0.0625in</Left>
<Top>0.0625in</Top>
<ZIndex>1</ZIndex>
<Width>3.125in</Width>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>800</FontWeight>
<FontSize>18pt</FontSize>
<Color>DarkSlateBlue</Color>
</Style>
<Height>0.9375in</Height>
<Value>Adventure Works
2002 - 2003
Sales</Value>
</Textbox>
<Matrix Name="SalesMatrix">
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>0.75in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>0.4375in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>0.625in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.0625in</Left>
<RowGroupings>
<RowGrouping>
<Width>1.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="Category">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value>=Fields!ProdCat.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ProdCat.Value</SortExpression>
<Direction>Descending</Direction>
</SortBy>
</Sorting>
<Grouping Name="ProductCategory">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="SubCategory">
<ZIndex>6</ZIndex>
<ToggleImage>
<InitialState>true</InitialState>
</ToggleImage>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value>=Fields!SubCat.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>#0066cc</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
<Sorting>
<SortBy>
<SortExpression>=Fields!SubCat.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="ProductSubcategory">
<GroupExpressions>
<GroupExpression>=Fields!SubCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>0.0625in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="txtAltColor">
<ZIndex>5</ZIndex>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Arial Black</FontFamily>
<FontSize>1pt</FontSize>
<BackgroundColor>=Value</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(RunningValue(Fields!SubCat.Value,CountDistinct,Nothing) Mod 2, "#C0C0C0", "White")</Value>
</Textbox>
</ReportItems>
<Grouping Name="AltColorRowGroup">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="OrderYear">
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value>=Fields!OrderYear.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderYear.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="OrderYear">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.25in</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>Sales</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Subcategory Scope</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Year Scope</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>BG Color from expression</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>Sales</DataSetName>
<Top>1.0625in</Top>
<Width>7.6875in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value />
</Textbox>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Sales">
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>9pt</FontSize>
<Format>C0</Format>
<BackgroundColor>=Code.MatrixColor(InScope("ProductSubcategory"), InScope("OrderYear"), ReportItems!txtAltColor.Value, "#0066CC")</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value>=Sum(Fields!Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>7pt</FontSize>
<Format>C0</Format>
<BackgroundColor>=Code.MatrixColor(InScope("ProductSubcategory"), InScope("OrderYear"), ReportItems!txtAltColor.Value, "#0066CC")</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=InScope("ProductSubcategory")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>7pt</FontSize>
<Format>C0</Format>
<BackgroundColor>=Code.MatrixColor(InScope("ProductSubcategory"), InScope("OrderYear"), ReportItems!txtAltColor.Value, "#0066CC")</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=InScope("OrderYear")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>7pt</FontSize>
<Format>C0</Format>
<BackgroundColor>=Code.MatrixColor(InScope("ProductSubcategory"), InScope("OrderYear"), ReportItems!txtAltColor.Value, "#0066CC")</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Code.MatrixColor(InScope("ProductSubcategory"), InScope("OrderYear"), ReportItems!txtAltColor.Value, "#0066CC")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2.0625in</Height>
<Style>
<BackgroundImage>
<Value>logoback</Value>
<Source>Embedded</Source>
</BackgroundImage>
</Style>
</Body>
<rd:ReportID>312657d4-5053-4f4c-b4fa-c580a92fd43b</rd:ReportID>
<LeftMargin>0.5in</LeftMargin>
<EmbeddedImages>
<EmbeddedImage Name="logoback">
<ImageData>/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCADCAbYDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD3+iiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAoo
ooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAEJxWHdazJ5pWAKFHGSMk1tsNykeoxXIzwvBM0bggg/nQBtadqjTyiGYAMfukd61s1y1hbvPPkZCqCWI+lRC5nQ/LM4x/tGgDrqK5Qahdr0nf8TUo1a8H/LTP1FAHTUVzq61cggsEYfTFb8MnmwpJjG4ZxQA+iiigAoqGW5jicKx5PtUqkMoIOQaLALRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAVDNFC6lpUVgBnLDpU1ZOs3eyMW6Hl+Wx6UAQ2+qM12sSRIIWbaFUYP1qw2hwMxO9xk+1V9FtMk3LDpwv9TW5QBz95YWlmvzTSFyOFGKzURpHCopLHoBW5q1i8xE8fJAwV9azbG9Nm5PlhgepPUUAX7PSUiAluiCeoXsPrWl9qgXjeOPSoYYRcKJZJPMB5AB4qwIIh0jX8qegCxzJLnYwOKfTVREJ2qBn0pJJBEhdug6UgM28x9pb8Kv2oItkz6VQjRrmck9Cck1pM6RJliABVydlYErklJVCXUgOI1z7mq5nupz8u78BWXMi1TfU18gdxSb1/vCsn7LdN1B/FqabO4X+D8qXMyvZx7myCKXNYJ82I87l/SpY76ZOp3D0NHP3B0n0NjilqpBeRykA/Kx7GrY6VSdzNprcKKKKYgooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiijNABRRRQAUUUUAFFGaM0AFFFFABRR
RQAUUUZoAKKM0ZoAKgmuFiO0Dc56KKfPJ5UTOBkjpUVvDtHmPy7cnPahARSed5TSSyeWijJC9awFWS8ugucs54J9K6p0WRCjDKkYIqvbafBayM8anJ4ye1FwGRxz2kaqmHjXtjmrMMyzLleo6j0qTtVW4jMTCePgj7w9RT3AsOQqlj0AzWLcac1yj3MYAck/KO4rUncNaMy9GFSQDECY9KOgHO2V9JZSbWBMefmU9q6KGZJoxJGwKmqWo6aLgGSIYlH/j1V9It7mKZ/MVljxyD3NIDTluo4uCcn0FU2WW5O+Q7Ix60skgSZ1ihLPnljR9lmm+a4k2j0o5uxSj1YNdpEvlwLuPqe9MW1nuG3SEge9Tq9vbnES739uaUi6m6YjWlyt6sfOl8I5LW3g5bGfUmpBcQDgOoqEafnlpCTTX0/j5HP0NUoxIbb3LqurDKsCPY0tY7LJA/dWHcVo2kzTREt1HBPrTcbATMisMEZFUp7BWy0R2n0q/SVDSY1JrYxPs0wcAIc5raQEIAeuKXFLSUbFSm5bhRRRVEBRRRQAUUUUAFFFFABRRRQAUGig0AQ3Evkx5AyxOAPU1FDNK05jkVRhd2RTLxz50SqpbB3ECltn3maZhjtj6VDepdtBHuZQ0hRUKJwSTzUrzlbeOQLy2OPrVXB+xAdGmfn8TVi6A2wr28wflRcHYlMpM4jAzgZY+npUPnzvJIIlQqpxknvToP9W87fxnP4dqitFnADfJscljnOadwsSzzvDbh2A38cDpSmY7oVUDMnJ+lRXY8yaOPtgsabaHzJlPaOMD8TSvqFtLkt1PLCAVVSvv604SyLbu8qgMATgHrTLn57iCPtu3H8Kden9wIx1dgtMQee2IPlG6Q8j0FEs8gn8qJVJ27jmmgbr4DtGn6mq7ySh55oyoXO059vSlcaWpaS4LWrTMAMA/jSRSXL7WKIEPXnnFRzr5dkkScFiFFWLcTAHzNmO22ncTSsTdqhmlKSRqoG
WP6VNVG8fbPGR94K2B7ngU27CirsebvidgOEOB9afNM0USfKDIxxj3qnAmZRFnOH3E59B/jVmb576JOygsam5TSTHQTSvK8cgUFQOVpj3Mu6QoqFEPUmltmGJpj3Y4+gqqvmeUobAjlcZx96i7Gki7JOVtkcD5mxgH3pUmLzMigFF4J9/SoLsM8sUceNwy3Pan2RPkurAZViMjvTuKytcSS5k/eFEUpGcEsetWYn3xK5GCRkiqOHhwjojJI/r6mtADgYpoUkOormp9Su1uJFWYhQ5AGB61e0i6nuZZRLIWAAIpkmvTWAZSD3FOHSkJwCaAKcI32skf90kVNavut19RwaZZjKO395jTWzazFwCYn647GqYFykpFcOoKkEU6pAilSQj90wU9yRUItCxzLKze1W6KLsCNIkjHyqBUgoooAKKKKAGSRpIMOoIoRFjGFXAp9FABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAVRG/2qSQrxtAX3pghkWwZAuZGByM46/wD1quUUrDuUoopXki3psSPpznNSXcTyqgQfxcn0FWqSiwcxBMjfZmSNcnGAKZbGdQqNEFQDGcirfakosFyssTG6eRl+XaFWksoGhRtwwSf0q1iiiwX6Ffy3N6ZGX5AuAfem3SSl4njTeEycZx9KtUUWC5WhjkXzJHUB35wD6VH9nk+yrHtyWfL89s1doosHMVLlZfMiMabgmScnH0qaFpWUmRNp9M5ptxdQ2y7pXxnoO5rOfXVBIjgJHqzYoSC5sdqqSwNJeq5HyKvB96pJrqE4kgIHqrZ/Tirn9o25tzMhLqv3go5H4UNXBOw61gaOSV2GCzcfSo5EuFuXdEDBhgHPSov7ctf+ec35D/GrVreRXis0eRtPIbrRyhzaiGF0sTEg3NjHX1o8lvMgAHyRg5Oe/ap5HWNGdyAqjJqlDq0E8qxIkpZjjoP8aLBzE4jf7XJIV4CgLz1piwutgyY/eEHP1
NSz3UNsm6VgvoB1NZ0muqD+7gLD1Zsf0osFy1FFM0kW+MIsee+c1erHj11C2JICB6q2f8K0be6huV3ROGx1HQihKwN3OYuf+Pqb/fb+daWg/wCum/3RWbc/8fUv++f51paD/r5v90UxG7SEBgQehpskiRIXkYKo6k1mS65ChxFG0nuTtFAGmiLGoVRgClIDDBGQaxl145+a349n/wDrVettSt7k7VYq/wDdbjNADjbNG26CTb/snpVpc4Geveis1tWiW9MTD92ON/v/AIUAadFIrAgEEEGloAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACmSOERnPRRk0+muodWU9CMGgDk7id7idpHPJP5Vf03TUuY/OmJ25wFBxms+5t3tpmjcdOh9RV7TdTW2j8mUHZ1BHOKANCXR7V0IRWQ9iGJ/Q0yx0v7O8jSkOT8q46Yp0ms2qj5Czn0Ax/Om6fqRu52jdAD1XHTHpQBkX1qbS4KfwHlD7UWdybS5Vx93ow9RW/f2n2q2IH+sXlD71zBBBIIII4waANbV74SBYImyuAzEHr6CptKtRb27XMg+Yrkf7tZ2n2n2u5AI/dry3+H410rIGjKEfKRjA9KAOTuJ3uJmlc8k8D0rQ0zTUuIzNMTszgKO9ULm3e2naOTqOh9R61d03U1to/JlB2dQRzigDQk0e0dMIrIfUEn9Dmm6fpptZXkkYM3RcdhSyazaqPkLOfQDH86bYakbuZo3QA9Vx6UAYlz/x9S/75/nWloP8Arpv90fzrNuf+Pqb/AH2/nWloP+um/wB0fzoAg1a5aa7aPOEjOAPeqsHkeaPPD7PROtW9WtWhuml/gkOQfQ+lVIDCJQJwxTvt6igDVR9IkXbtC/UEH86eNGRbpHVz5Q6qev0zTEOjxoG4J/2gxNL/AG0huVATEPQsev1oAtald/ZbU7T87/Kv
+Nc0eeT9at3k7X158oJBO1B7VoTaMrWyiI4lA5z0agClYak9q2x8tF6d1+ldFFKk0QkRgynuK449a3NCZzDMD90MMfXv/SgDYooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKAIZ7aK5XbKm4dvUVnSaFGT8kzL/vDNa9FAGRHoMYP7yZ2+gx/jWhBaw2y7YkC+p7mp6KAEzj2rlr50nvXMS8EgDHc1vagtw9uY7dCzPwTkDAqlpumyRT+bcJtK/dXIPPrQBesbT7JbhP4jyx96t0g6UtAEM9tFcLtlQMO3qKzpNCjJ+SZl/wB4ZrXooAyI9BjB/eTO30GP8a0ILaK2XbEgX37mp6KAM19Ft5HZ2eXLEk8j/Cp7TT4rNmaNnO4YO4j/AAq3RQAx0EilWUFT1B5rOl0SBzmNnj9uorUooAxl0FQfmuCR7Jj+tWl0m3SJkXeCwwXz82P6VfooAoW2lwWswlTcWHA3YwP0q1NCJ4mjYsobuOCKlooAx/7BTdxO23028/n/APWrTggS3iEca4UVLRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUU
UAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAFFFFAH/2Q==</ImageData>
<MIMEType>image/jpeg</MIMEType>
</EmbeddedImage>
</EmbeddedImages>
<DataSets>
<DataSet Name="Sales">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SET DATEFORMAT mdy
SELECT PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,
SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM Production.ProductSubcategory PS INNER JOIN
Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID</CommandText>
<Timeout>30</Timeout>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="ProdCat">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProdCat</DataField>
</Field>
<Field Name="SubCat">
<rd:TypeName>System.String</rd:TypeName>
<DataField>SubCat</DataField>
</Field>
<Field Name="OrderYear">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderYear</DataField>
</Field>
<Field Name="OrderQtr">
<rd:TypeName>System.String</rd:TypeName>
<DataField>OrderQtr</DataField>
</Field>
<Field Name="Sales">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Sales</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code>public function MatrixColor(bRowGroupScope as Boolean, bColGroupScope as Boolean, sAltColor as string, sTotalColor as string) As String
Dim sReturn as String
Select Case True
Case (Not bRowGroupScope And Not bColGroupScope)
sReturn = sTotalColor
Case (bRowGroupScope And Not bColGroupScope)
sReturn = sAltColor
Case (Not bRowGroupScope And bColGroupScope)
sReturn = sTotalColor
Case (bRowGroupScope And bColGroupScope)
sReturn = sAltColor
End Select
MatrixColor = sReturn
end function</Code>
<Width>9.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Description>Adventure Works sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.</Description>
<TopMargin>0.5in</TopMargin>
</Report>
Hello,
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
Thanks to all!
Andrea
I want to divide the subtotal of a matrix. Can I do ?
View 1 Replies View Related