Matrix: Hide null value row

The matrix that i have contains null values and is creating empty rows. I tried grouping the row and then setting the visible property, but that just hides the entire rows. Is there an expression that i would need to ensure that only the null rows are not visible on the matrix?

Thanks for taking the time to read.

The database tables looks like:

Month Sales Product

- - --

August 2007 700.00 Apples

August 2007 400.00 Oranges
September 2007 380.00 Apples
October 2007 1200.00 Oranges
November 2007 NULL NULL
December 2007 NULL NULL
Jan 2008 400.00 Grapefruit

The matrix looks like:

August 2007 Sept 2007 Oct 2007 Nov 2007 Dec 2007 Jan 2008

Apples 700.00 380.00

Oranges 400.00 1200.00

Grapfruit 400.00

Try filtering these records. You can use the filter within the Matrix properties to filter all records where Product Names are NULL

|||

I actually tried filtering in the edit group searching for nulls but it didn't work.

I did a bit of searching and found that i needing to use this: =IsNothing(Fields!productname.Value) in the filter expression for the group.

Thanks for your help.sql

matrix row grouping

HI,
Is it possible to have row grouping for each row in a matrix'
Something like
header1 header2
Task1
Section1
Row1 value1 value1
Section2
Row2 value2 value2
Task2
Section1
Row1 value1 value1
When i use a matrix grouping it is producing the result as
Task1 Section1 Row1 value1 value1
Section2 Row2 value2 value2
Task2 Section1 Row1 value1 value1
What i need to get the similiar look and feel as the previous one....
I could get this design with a table grouping but the columns are populated
dynamically... which is not possible in a table so im shifting to a matrix...
Your help is highly appreciated...
Thanks,Right click the data cell and do 'Add Row'.
You are effectively creating two rows per record.
Chris
CCP wrote:
> HI,
> Is it possible to have row grouping for each row in a matrix'
> Something like
> header1 header2
> Task1
> Section1
> Row1 value1 value1
> Section2
> Row2 value2 value2
> Task2
> Section1
> Row1 value1 value1
> When i use a matrix grouping it is producing the result as
> Task1 Section1 Row1 value1 value1
> Section2 Row2 value2 value2
> Task2 Section1 Row1 value1 value1
> What i need to get the similiar look and feel as the previous
> one.... I could get this design with a table grouping but the
> columns are populated dynamically... which is not possible in a table
> so im shifting to a matrix...
>
> Your help is highly appreciated...
> Thanks,|||Thanks chris for ur reply...
When i add a row like that..its repeating column wise i want it to repeat
row wise...
Thanks,
"Chris McGuigan" wrote:
> Right click the data cell and do 'Add Row'.
> You are effectively creating two rows per record.
> Chris
>
> CCP wrote:
> > HI,
> > Is it possible to have row grouping for each row in a matrix'
> >
> > Something like
> >
> > header1 header2
> > Task1
> > Section1
> > Row1 value1 value1
> > Section2
> > Row2 value2 value2
> > Task2
> > Section1
> > Row1 value1 value1
> >
> > When i use a matrix grouping it is producing the result as
> > Task1 Section1 Row1 value1 value1
> > Section2 Row2 value2 value2
> > Task2 Section1 Row1 value1 value1
> >
> > What i need to get the similiar look and feel as the previous
> > one.... I could get this design with a table grouping but the
> > columns are populated dynamically... which is not possible in a table
> > so im shifting to a matrix...
> >
> >
> > Your help is highly appreciated...
> >
> > Thanks,
>|||I'm with you now!
It's possible but it's a bit of a hack.
Basically you need to squeeze two text boxes into the row header, you
can do this with 'rectangle' controls.
Create a rectangle OUTSIDE the data region, create two textboxes inside
the rectangle, laid out the way you want them. Set their values to the
appropriate fields.
Cut and paste the rectangle into the row group cell on the matrix and
Bingo! it should work.
The downside to this method is it can be quite fiddly to get the
formatting just right but you can do it.
To try and explain it graphically, the structure of your matrix should
be something like this.
+--Matrix--+--+
| | |
+--+--+
|+==Rectangle=========+| |
||+--Textbox1--+|| |
||| ||| |
||+--+|| |
||+--Textbox2--+|| |
||| ||| |
||+--+|| |
|+====================+| |
+--+--+
Chris
CCP wrote:
> Thanks chris for ur reply...
> When i add a row like that..its repeating column wise i want it to
> repeat row wise...
> Thanks,
>
> "Chris McGuigan" wrote:
> > Right click the data cell and do 'Add Row'.
> > You are effectively creating two rows per record.
> >
> > Chris
> >
> >
> >
> > CCP wrote:
> >
> > > HI,
> > > Is it possible to have row grouping for each row in a matrix'
> > >
> > > Something like
> > >
> > > header1 header2
> > > Task1
> > > Section1
> > > Row1 value1 value1
> > > Section2
> > > Row2 value2 value2
> > > Task2
> > > Section1
> > > Row1 value1 value1
> > >
> > > When i use a matrix grouping it is producing the result as
> > > Task1 Section1 Row1 value1 value1
> > > Section2 Row2 value2 value2
> > > Task2 Section1 Row1 value1 value1
> > >
> > > What i need to get the similiar look and feel as the previous
> > > one.... I could get this design with a table grouping but the
> > > columns are populated dynamically... which is not possible in a
> > > table so im shifting to a matrix...
> > >
> > >
> > > Your help is highly appreciated...
> > >
> > > Thanks,
> >
> >|||Thanks Chris,
That worked...
"Chris McGuigan" wrote:
> I'm with you now!
> It's possible but it's a bit of a hack.
> Basically you need to squeeze two text boxes into the row header, you
> can do this with 'rectangle' controls.
> Create a rectangle OUTSIDE the data region, create two textboxes inside
> the rectangle, laid out the way you want them. Set their values to the
> appropriate fields.
> Cut and paste the rectangle into the row group cell on the matrix and
> Bingo! it should work.
> The downside to this method is it can be quite fiddly to get the
> formatting just right but you can do it.
> To try and explain it graphically, the structure of your matrix should
> be something like this.
>
> +--Matrix--+--+
> | | |
> +--+--+
> |+==Rectangle=========+| |
> ||+--Textbox1--+|| |
> ||| ||| |
> ||+--+|| |
> ||+--Textbox2--+|| |
> ||| ||| |
> ||+--+|| |
> |+====================+| |
> +--+--+
> Chris
>
> CCP wrote:
> > Thanks chris for ur reply...
> > When i add a row like that..its repeating column wise i want it to
> > repeat row wise...
> >
> > Thanks,
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > Right click the data cell and do 'Add Row'.
> > > You are effectively creating two rows per record.
> > >
> > > Chris
> > >
> > >
> > >
> > > CCP wrote:
> > >
> > > > HI,
> > > > Is it possible to have row grouping for each row in a matrix'
> > > >
> > > > Something like
> > > >
> > > > header1 header2
> > > > Task1
> > > > Section1
> > > > Row1 value1 value1
> > > > Section2
> > > > Row2 value2 value2
> > > > Task2
> > > > Section1
> > > > Row1 value1 value1
> > > >
> > > > When i use a matrix grouping it is producing the result as
> > > > Task1 Section1 Row1 value1 value1
> > > > Section2 Row2 value2 value2
> > > > Task2 Section1 Row1 value1 value1
> > > >
> > > > What i need to get the similiar look and feel as the previous
> > > > one.... I could get this design with a table grouping but the
> > > > columns are populated dynamically... which is not possible in a
> > > > table so im shifting to a matrix...
> > > >
> > > >
> > > > Your help is highly appreciated...
> > > >
> > > > Thanks,
> > >
> > >
>

Matrix Report Calculation Issue.

Hi There,
I have this matrix report that shows sales for last couple of years and is
grouping on 5 different regions and separate years on different columns, I
want to calculate the % Sales for each region in another column. how do I
accomplish this in Reporting Services? I think the static cell on a matrix
don't allow this, I have been trying to somehow call the single cell name
from a Expression but no luck. I think this could be accomplish with some
code, if it is, can you please give some samples or direct me to a place
where I could research this topic a bit more?
any advices would be really appriaciated.
Thanks very much in advance.
MannyHi Manny:
There is a way to achieve this using "IIF" and "InScope" function. I did it
on a Matrix Report and it will dynamically calculate the percentage based the
Row/Column grouping.
Manny wrote:
>Hi There,
>I have this matrix report that shows sales for last couple of years and is
>grouping on 5 different regions and separate years on different columns, I
>want to calculate the % Sales for each region in another column. how do I
>accomplish this in Reporting Services? I think the static cell on a matrix
>don't allow this, I have been trying to somehow call the single cell name
>from a Expression but no luck. I think this could be accomplish with some
>code, if it is, can you please give some samples or direct me to a place
>where I could research this topic a bit more?
>any advices would be really appriaciated.
>Thanks very much in advance.
>Manny
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1|||Can you give us a sample on how you accomplished this please?
Thanks,
Manny
pmak wrote:
>Hi Manny:
>There is a way to achieve this using "IIF" and "InScope" function. I did it
>on a Matrix Report and it will dynamically calculate the percentage based the
>Row/Column grouping.
>>Hi There,
>[quoted text clipped - 12 lines]
>>Manny|||Here is the example:
IIF(InScope("matrix1_RowGroup1"), IIF(InScope("matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF
(InScope("matrix1_Program"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!
Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup1"),Sum(Fields!Funding.Value,"matrix1_Program")/Sum
(Fields!Funding.Value,"matrix1_ColumnGroup2")),""))IIF(InScope
("matrix1_RowGroup2"), IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF(InScope
("matrix1_RowGroup3"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value,"matrix1_Program")/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup2")),""))
Paul
Manny123 wrote:
>Can you give us a sample on how you accomplished this please?
>Thanks,
>Manny
>>Hi Manny:
>[quoted text clipped - 7 lines]
>>Manny
--
Message posted via http://www.sqlmonster.com

Matrix grouping/visibility

I have tried several combinations, but I can't quite figure out the solution to this one. I have a matrix set up that is giving me the results I want, but I need to do a conditional visibility and still have what I need.

I have a column that is filled with operation numbers and a row that is filled with item numbers. The detail is for work centers and it shows where each of them fall in the grid. I need to say to show all work centers that apply where the workcenter parameter matches. I was able to filter and say only show me this workcenter, but I want to show just those, but then if this workcenter would show up, show me the rest.

BJ

bj

By right click on the detail column and selecting properties. You have access to the visiblility tab, on the tab, you have expression. You can use the expression to control what is display and what is hide.

Hammer.

Matrix grouping

I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:

Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total

Why is it doing this?

Here's a view of my matrix in layout view...

Month_Name

Fatal Crashes =sum(fatal_crashes.value)

Injury Crashes =sum(injury_crashes.value)

Property Damage =sum(Prop_Damage.value)

Total Crashes =sum(Total_crashes.value)

Chicago Crashes =sum(Chicago_crashes.value)

Crashes Located =sum(located_crashes.value)

% Located =sum(percent_located.value)

any help would be greatly appreciated!! THANKS!

Try changing the sorting of the matrix to use the month number if available.

http://msdn2.microsoft.com/en-us/library/aa179319(SQL.80).aspx

If it's not available, you may be able to build an ugly IIF statement to provide the row numbers, or use a function to return them.

cheers,

Andrew

|||

I tried to hardcode in a switch statement such as this one :

=Switch((Fields!Month_Name.Value) = "Jan", 1, Fields!Month_Name.Value = "Feb", 2, Fields!Month_Name.Value = "Mar", 3, Fields!Month_Name.Value = "April", 4, Fields!Month_Name.Value= "May",5, Fields!Month_Name.Value= "Jun",6, Fields!Month_Name.Value= "Jul",7, Fields!Month_Name.Value= "Aug",8, Fields!Month_Name.Value="Sep",9, Fields!Month_Name.Value="Oct",10, Fields!Month_Name.Value="Nov",11, Fields!Month_Name.Value="Dec",12)

But it didnt change anything. I also changed the sort to Fields!Month_Name.key and Fields!Month_Name.Level and nothing changed as well. I wonder if its because i added extra rows to the matrix ...but still it should work, i have added extra columns before and never had this problem.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

matrix control parent child grouping

does anybody know how to properly wokt out a parent child hierarchy in a matrix control?

*i already know that i have to work with the 'edit group' button and then

the 'general' tab and the values 'group on' : field.uniquename and 'parent group' .parentuniquename

*but fot some reason i always have to include 2 extra rows (my hierarchy is on a row)

one with field.uniquename and one with field.parentuniquename, i can make the invisible though but i can't remove them, otherwise it won't work

is this a kind of bug?)

*but finally what i really can't find out is how to properly toggle the visibility

the idea is that the hierarchy is collapsed (i have more then 8 levels, and more then 10000 membders) and that by clicking on a parent , the children appear

(the standard intuitive behavour of parent child controls)

anybody any clues?

on my way i found posting:

Creating report based on parent-child dimension

i think it's about the same subject

matrix column grouping collapse

Hi Folks
Going a little crazy with this one...
Have a SSRS 2K SP1 matrix report which is highly similar to the company
sales report example. As a matter of act, the column headings are identical.
Have no issues with the row groupings expanding and showing initially as
collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
to rendering as collapsed or have any of this functionality.
Having groupings working fine for both row/column, but the collapsed for
columns just doesn't seem to be functional?
thanks
robgot it...
solution was found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
rob
"Rob" wrote:
> Hi Folks
> Going a little crazy with this one...
> Have a SSRS 2K SP1 matrix report which is highly similar to the company
> sales report example. As a matter of act, the column headings are identical.
> Have no issues with the row groupings expanding and showing initially as
> collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
> to rendering as collapsed or have any of this functionality.
> Having groupings working fine for both row/column, but the collapsed for
> columns just doesn't seem to be functional?
> thanks
> rob

matrix column grouping

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name	Sales -			Costs +	Revenue -
Client A +	Europe +	Middle East +	Asia Pacific +	$12,000,000	Products +	Investments +
	$12,000,000	$8,000,000	$4,000,000		$10,000,000	$22,000,000
Client B +	$77,000,000	$16,000,000	$9,000,000	$22,000,000	$8,000,000	$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

...Hmm, I'll assume it's not possible to do this at the moment. Looks like I'll be doing my reports in asp.net for now.|||

There's no built in way of doing this. There are always workarounds but hese tend to be messy.

It will be fairly straight forward if using SQL less straight forward with MDX.

Basically need to consider the data in terms which portion of the report it is to appear on rather than what the actual entity is. Let me clarify. For your example instead thinking of Europe, Middle East and Asia Pacific as regions think of them column grouping level 1. Similarly the products fall into this same category. Hence you can generalise your SQL query for the report to have this 1 column instead of having region and product e.g.

SELECT client
, measure = 'Sales'
, column_level_1 = region
, amount = SUM(sales)
FROM some_table
GROUP BY client
, region

UNION ALL

SELECT client
, measure = 'Revenue'
, column_level_1 = product
, amount = SUM(revenue)
FROM some_table
GROUP BY client
, product

The result is a dataset that looks like:

client measure column_level_1 amount
=============================================
Client A Sales Europe 12000000
Client A Sales Middle East 8000000
Client A Sales Asia Pacific 4000000
Client B Sales Europe 77000000
Client B Sales Middle East 16000000
Client B Sales Asia Pacific 9000000
Client A Revenue Products 10000000
Client A Revenue Investments 22000000
Client B Revenue Products 8000000
Client B Revenue Investments 32000000

|||Thanks Adam. I'm using MDX, I'll give this some thought.|||

hi, can anyone help on this? i am new to MDX and also encounter this problem.
Thanks a lot in advance.

Best regards,

Tommy