Matrix data region - a specai kind of conditional formatting

Matrix data region - a specai kind of conditional formatting - I think

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbuchanan - 20 Jun 2008 03:41 GMT

When I used the wizard to help me make my matrix report the wizard chose to
use
=First(Fields!data.Value)
This was likely because the data is not an aggregate. Each item of data
applies directly to the corresponding entity.

The problem is that the "First( )" as shown in the illustratation below is
deceptive in the actual report. The reason the data shown in the rolled up
hierarchy is deceptive is that it appears to be a rollup value applying to
the region.
Explaination:
For the Northwest region shown below - to the reader the values shown below
the columns is misunderstood to apply to the Northwest region. It doesn't.
Rather it applies to the first value for that column of the hidden members.
If the Midwest region were collapsed the first colum would have 20% and the
second column 68%.

I need to have these columns empty when an entity is not visible.
How can this this be done?

By the way, the vales, though shown as percent are for other reasons are
actually text.

-----------------------
|
column1 | column 2 |

|---------------------------------------------------------------------
| [-] Division | [+] Northwest | 29% | 83%
|
| -------------------------------------------------------
| | [-] Midwest | Detroit | 20% |
|
| --------------------------------------
| | | Chicago | 15%
| |
| --------------------------------------
| | | Cleaveland | 47%
| 68% |
|----------------------------------------------------------------------

Thank you,
Doug

Reply to this Message

Charles Wang [MSFT] - 20 Jun 2008 11:01 GMT

Hi Doug,
Generally to set field value, we can use <Expression... >. You may check
whether it helps if you use IIF function to set the fields values. However
I am not sure if I totally understand your meaning, I appreciate if you can
elaborate your requirements more detailed. Also may you answer me the
following questions:
1. Why Midwest should have 20% for 1st column and 68% for 2nd column?
2. What is the meaning of your 1st column and 2nd column?
3. You said "I need to have these columns empty when an entity is not
visible.", however could you please let me know what the columns are?

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

dbuchanan - 20 Jun 2008 12:44 GMT

Charles,

> 1. Why Midwest should have 20% for 1st column and 68% for 2nd column?

My exampke did not render very well. - I sent a sample text file to you at
the Microsoft address - the one were you sent me a courtesy note (I holpe
that is okay). It needs to be viewed properly to be understood. be sure your
notepad is set to a mono-text like "Courier New" and set to no word wrap.
You should see two columns to the right.
Midwest is a region in which Detroit, Chicago and Cleaveland are members.
Therefore Column1 of 20% and Column2 of empty belong to Detroit not Midwest

> 2. What is the meaning of your 1st column and 2nd column?

The columns represent two requirements and the percentage are the extent to
which that requirement has been met. When a column is empty it means the
member has not recognized the requitement and submitted their level of
adherence.

> 3. You said "I need to have these columns empty when an entity is not
> visible.", however could you please let me know what the columns are?

I need the values empty when the hierarchy is rolled up (membeers not
visible) because values do not apply to regions only to members within
regions.

If you do not receive emil through

Thanks,
Doug

Reply to this Message

Charles Wang [MSFT] - 23 Jun 2008 13:43 GMT

Hi Doug,
Thank you for letting me know your requirement more clearly.

I am performing research on it now and may need more time to get back to
you. I appreciate your patience.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

Charles Wang [MSFT] - 24 Jun 2008 14:02 GMT

Hi Doug,
After my communications with our product team, I think that I can make a
conclusion now.

It is hard to implement this requirement unless you add another column into
your dataset. The new column is a redundant column which fields only record
the percentage values you want. In this case, you can set the field value
to "=First(Fields!NewColumnName.Value)".

I had thought that I can create a custom aggregate function (even CLR) such
as FirstNotNull(), however after my consulting our product team, I was
informed that it was impossible to implement this function in SQL Server
2000/2005/2008 SSRS. The news was unfortunate for us. Anyway the product
team has received some feedbacks for this feature and I think that they
will add it the their dev plan in future.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message

dbuchanan - 26 Jun 2008 18:36 GMT

Charles,

> It is hard to implement this requirement unless you add another column
> into
> your dataset. The new column is a redundant column which fields only
> record
> the percentage values you want. In this case, you can set the field value
> to "=First(Fields!NewColumnName.Value)".

Could you describe this with a little more clarity. I am not sure I
understand what you mean.

By the way I sent an illustration of the problem in the form of a text file
back on 6/20 and I am sending again today. It may help your undersant the
problem. You may consider editing it to communicate your explaination(?)

Thanks,
Doug

Reply to this Message

Charles Wang [MSFT] - 27 Jun 2008 10:15 GMT

Hi Doug,
Thank you for your response.

I am sorry. It seemed that I had some misunderstanding your requirement. My
last suggestion should not work even if you used a table region since you
need to capture collapsing/expanding events which are not possible now per
our internal discussions. I will try to further consult our product team,
however since the process may need a long time, if this issue is urgent to
your business, I recommend that you contact Microsoft Customer Support
Services (CSS) via telephone so that a dedicated Support Professional can
assist you in a more efficient manner. Please be advised that contacting
phone support will be a charged call.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message