Subtotal In Query
Jul 20, 2005
Hi,
WITH SQL SERVER QUETY, I have output like
PRJ item qty
=================
P1 I1 10
P1 I1 20
P1 I2 10
P2 I2 10
P2 I3 10
================
I WANT TO MANIPULATE IT LIKE THIS
PRJ item qty
=================
P1 I1 30
P1 I2 10
---------------
P1 * 40
---------------
P2 I2 10
P2 I3 10
---------------
P2 * 20
---------------
===============
PRJ * 60
===============
SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSET
CAN ANYBODY HELP
THANKS IN ADV.
T.S.NEGI
View 2 Replies
ADVERTISEMENT
Apr 1, 2008
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.
View 3 Replies
View Related
Feb 17, 2014
i've following data in a recursive query:
Par_IdIDABCDEFLevel
Null022646000100022646
02264602242632000002264622426
02264602253232100002264622532
02264602254082000002264622540
02264602255751100002264622557
[code]....
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
View 6 Replies
View Related
Apr 25, 2008
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
€¦
€¦
€¦
€¦
View 1 Replies
View Related
Nov 7, 2007
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!
View 12 Replies
View Related
May 28, 2008
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.
View 7 Replies
View Related
Jun 9, 2006
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
View 3 Replies
View Related
Jul 17, 2007
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
View 5 Replies
View Related
Mar 13, 2007
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
View 1 Replies
View Related
Jul 23, 2007
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.
View 2 Replies
View Related
Dec 7, 2007
Is there a way to make the subtotal column appear on every page when the matrix spans multiple pages?
View 1 Replies
View Related
Nov 10, 2005
I'm still new to reporting services so forgive me for the newbie question...
View 9 Replies
View Related
Jun 21, 2006
Hello,
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!
View 10 Replies
View Related
Apr 21, 2008
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
View 3 Replies
View Related
Oct 23, 2007
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")
View 2 Replies
View Related
Jan 21, 2008
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 ?
View 8 Replies
View Related
Jul 23, 2007
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.
View 3 Replies
View Related
Oct 25, 2007
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!
View 1 Replies
View Related
Sep 21, 2006
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
View 7 Replies
View Related
Apr 16, 2008
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
View 4 Replies
View Related
Jul 4, 2007
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!
View 3 Replies
View Related
May 16, 2007
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!
View 6 Replies
View Related
Oct 23, 2007
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!
View 1 Replies
View Related
Jul 4, 2006
Dear guru!
How I can define subtotal by page in the table? Is it possible?
Thanks in advanced.
View 6 Replies
View Related
Jun 3, 2007
Hello,
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.
View 4 Replies
View Related
Mar 12, 2014
Here I have attached excel sheet. I want to do row wise sum, product and then sub total.
View 4 Replies
View Related
Jan 30, 2007
Here'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!
View 16 Replies
View Related
May 29, 2007
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>
View 3 Replies
View Related
Feb 21, 2007
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
View 4 Replies
View Related
Jan 12, 2007
I want to divide the subtotal of a matrix. Can I do ?
View 1 Replies
View Related
Jun 27, 2007
Hi,
I am using the matrix element. in the data part i m using the aggregate function CountDistinct. and when i m taking the subtotal for this value across rows the total is coming wrong. while in the same matrix i have other data values also which uses functions SUM, for these the Subtotal is coming correct.
Can somebody tell me why this is happening with CountDistinct function.
Thanks in advance.
View 4 Replies
View Related
Feb 4, 2008
I have a table with a Labor Category Group containing 5 categories (1,2, 3, 4 and 5). I need to add a subtotal for categories 1, 2 and 3. I know how to add the subtotal for all 5 but not for a subset of them. How do I do this?
Thanks
Dean
View 1 Replies
View Related
