HELP - I NEED EXTRA MATRIX COLUMNS

I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc … SUBTOTAL (DTD) MTD TOTAL YTD TOTAL Book Currency =sum(Fields!DTD.VALUE) ........ =sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book CCY GroupedData DTD MTD YTD ABC GBP Commission ￡0.01 ￡0.09 -￡0.10 ABC GBP Interest ￡0.02 ￡0.29 ￡0.11 ABC GBP Brokerage ￡0.12 ￡0.06 ￡0.20

When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data Grouped Data CELL 1 Grouped Data CELL 2 Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL =sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

This SUCKS.... I cant believe no one knows this and there are no Microsoft developers monitoring this thread to answer the questions.

|||

Hi there,

Did you try doing subtotals and totals within SQL Server instead of in the matrix? This should get the extra columns you would need.

Alternatively you could create a matrix next to the matrix that may work.

Or see this posting.

http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

cheers,

Andrew

|||

Thanks Andrew -

For anyone who is reading this thread that isn't sure where the Inscope expression goes (like me) it is in the

"Data" bit of the matrix (where you have your =Sum(field!...) bit)

The only thing now is - how do I sort the positioning out. My design layout looks like below - I have a DTD BreakDown and Total (I have used the <position> element in the XML code behind to add "DTD Total" position to the front) - I can get the MTD to be on the same level as the DTD Parent Cell as needed - however The YTD subtotal is one level higher (I tried messing about with alignment - which sorts the problem temp - but on expanding the DTD breakdown the alignment doesnt work)

How can I make the YTD (Text) appear on the same level as DTD and MTD?

YTD DTD MTD Fields!DTDBreakDown DTD Total Field!Book.Value Field!CCY.Value iif(InScope("YTD"),
iif(InScope("MTD"),
Sum(Fields!AmountCCY_DTD.Value),
sum(Fields!AmountCCY_MTD.Value)),
sum(Fields!AmountCCY_YTD.Value))

e.g of how my report looks - I need the YTD to be on the same level as DTD and MTD

YTD (+)DTD MTD DTD Total Book 1 CCY 0 0 0

|||This is not possible in the Matrix report. You need to return you column from the SP or TSQL that you are using and instead of using Matrix create this report from using table report.|||

Are you sure? According to the example given in the URL on Andrew's Post(http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx) says you can. However, its just doesn't explain how.

I need to use a matrix as the DTD breakdown is dynamic - and I'm almost there, the problem I have is cosmetic.

|||If you want to create the report as given in above URL then you can do it. Are you want to do like that ? and steps are clearly added there. Any other problem in that then let me know.