Matrix with custom row total - is it possible?

Hell Everybody,
Let's suppose on my report I have a matrix with sales data - regions in
columns, years in rows. Now I need to add an additional total column with,
let's say, total profit.
I tried to accomplish that adding a hidden value. Unfortunately it seems
like I have no control over which total is displayed and which is not.
Theoretically I could place table object next to my matrix, with the same
row and header size but when it comes to pagination results are disastrous.
For any reason renderer breaks my matrix and table at different row.
Sometimes the difference is more than one row, sometimes it does not break
the table but breaks matrix - I am aware of KeepTogether property.
Please advise.
TomaszHi Tomasz,
Thank you for your post.
Have you tried SubTotal column? To add a subtotal to a matrix, add a
subtotal to an individual group within the matrix. Groups do not have
subtotals by default. To add a subtotal to a group, right-click the group
column or row header and then click Subtotal. This will open a new header
for the subtotal. Reporting Services will calculate the subtotal based on
the aggregate in the data cell for the group.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks for the answer. I have tried this. The better problem definition is
this: I have a matrix with two different measures. I want to show one of
measures with no subtotals while for the other one I want subtotals only. So
the result would look like this:
year/regional sales, USA, Canada, Total Profit
2004, $29334.00, $23232.00, ($6552.00)
2005, $534435.00, $387745.00, $223445.00
Notice that data in the last column has nothing to do with data in other
columns - it is a different measure for which region/year details are not
visible - I want subtotals only. In contrast, for the region/year sales
measure I do NOT want to show subtotals (yearly sales in this case).
Thanks,
Tomasz
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:uLCdsGKdGHA.5024@.TK2MSFTNGXA01.phx.gbl...
> Hi Tomasz,
> Thank you for your post.
> Have you tried SubTotal column? To add a subtotal to a matrix, add a
> subtotal to an individual group within the matrix. Groups do not have
> subtotals by default. To add a subtotal to a group, right-click the group
> column or row header and then click Subtotal. This will open a new header
> for the subtotal. Reporting Services will calculate the subtotal based on
> the aggregate in the data cell for the group.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tomasz,
Thanks for the update.
How about hide the column you just want Subtotals?
If possible, would you please provide some test data so I could test on my
side?
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix report, date: 1900-01-01 wrong!! Suppose to be empty, how?

Hi

I am making a report in Visual Studio. I’m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields’ aliases at least. So what I did is that in the second table is:

SELECT ‘ ‘ AS ‘Date’

Right?

Now, in the report the first table gives me the dates in a format:

=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn’t have a date in that field when I run the report it gives me:

1900-01-01

Something I don’t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

Try something like this:

SELECT myDate AS DATE FROM MyTable

UNION ALL

SELECT CASE WHEN myBlankField = ' ' THEN ' ' ELSE ' ' END FROM OtherTable

And choose a field that is NOT a date for myBlankField. This will put blanks in the result set, but you can always just filter those out later.

|||

Thanks for your quick answer, sounds interesting, but I don't know quite where to put your example. Here's the code I have, maybe you know.

SELECT estimatedclosedate AS 'Date', blablabla...

INTO TempTable1

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT ' ' AS 'Date', blablabla... -- This is the date that is troubling me

INTO TempTable2

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT * FROM TempTable1

UNION

SELECT * FROM TempTable2

The TempTable1 gives me Opportunities from a CRM system, the TempTable2 gives me Ongoing Business in the CRM system. Of course, Opportunities have an estimated close date and Ongoing Business has not … so I just want it to show an empty cell in the matrix report.

So, where exactly could I put your suggestion?

|||

try -- select null as 'Date'., xyz........into temptable

Priyank

|||

Worked perfectly!!! Thanks mate!!!|||

can you please mark it as answer...|||

By the way ... do you know anything about my other thread I have here? About putting a tooltip window thing on one of my filters? One of my filters is done that the user can write what ever he wishes to filter on, either exactly or even with a % ... the thing is that I don't want to write on the Prompt: "Tradelane (STO-LAX, or STO%, or %STO) ... it's just to long ... do you happen to know how?

|||Mark it as answer? DONE!!