Export from a Matrix (with nulls) to Excel - Problem/Issue

I was wondering if there a work around within Reporting Services, to fix a
problem with the export from a Matrix (with nulls) to Excel.
When I export to excel from a Matrix with a row that is missing the export
turns all of my rows into text and I get all of the nice green triangles with
the warning message.
Example
Data for 1/1/2007:
Column1
Item1 7
Item3 8
Data for 1/2/2007:
Column1
Item1 5
Item2 16
Item3 10
So the Matrix looks like this:
1/1/2007 1/2/2007
Item1 7 5
Item2 0 16
Item3 8 10
I tried going to the value property and setting
=IIf(IsNothing(Fields!COLUMN1.Value) = true, 0, Fields!COLUMN1.Value)
But it looks like the Null Value is still being sent to the rendering engine.
Is this worked as designed or should I be able to force the value to the
rendering engine.
I fixed the problem within T-SQL by bringing back all values for all days.
So now data the data for 1/1/2007 come back like this:
Column1
Item1 7
Item2 0
Item3 8
But can this be fixed within Reporting Services?
thanks
ReevesOn Aug 3, 4:54 pm, Reeves <Ree...@.discussions.microsoft.com> wrote:
> I was wondering if there a work around within Reporting Services, to fix a
> problem with the export from a Matrix (with nulls) to Excel.
> When I export to excel from a Matrix with a row that is missing the export
> turns all of my rows into text and I get all of the nice green triangles with
> the warning message.
> Example
> Data for 1/1/2007:
> Column1
> Item1 7
> Item3 8
> Data for 1/2/2007:
> Column1
> Item1 5
> Item2 16
> Item3 10
> So the Matrix looks like this:
> 1/1/2007 1/2/2007
> Item1 7 5
> Item2 0 16
> Item3 8 10
> I tried going to the value property and setting
> =IIf(IsNothing(Fields!COLUMN1.Value) = true, 0, Fields!COLUMN1.Value)
> But it looks like the Null Value is still being sent to the rendering engine.
> Is this worked as designed or should I be able to force the value to the
> rendering engine.
> I fixed the problem within T-SQL by bringing back all values for all days.
> So now data the data for 1/1/2007 come back like this:
> Column1
> Item1 7
> Item2 0
> Item3 8
> But can this be fixed within Reporting Services?
> thanks
> Reeves
I had a similar issue in the past and I think I resolved it by casting
both values in the iif statement to integer or decimal. Something like
this should help.
=IIf(IsNothing(Fields!COLUMN1.Value) = true, CInt(0), CInt(Fields!
COLUMN1.Value))
-or-
=IIf(IsNothing(Fields!COLUMN1.Value) = true, CDec(0), CDec(Fields!
COLUMN1.Value))
Also, its possible that the IsNothing is not catching whatever
returned value you are getting in the dataset. Possibly something in
the underlying nonequivalence between VBNull and DBNull (since SSRS
expressions are built in VB.NET) In this case, you may want to use a
case statement in your stored procedure/query that is sourcing the
report to set the value to something recognizable to filter out/set to
zero, etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for the response, but that is exactly what I did. I wanted to fix
the porblem at the SSRS level and not at T-SQL.
thanks
Reeves
"EMartinez" wrote:
> On Aug 3, 4:54 pm, Reeves <Ree...@.discussions.microsoft.com> wrote:
> > I was wondering if there a work around within Reporting Services, to fix a
> > problem with the export from a Matrix (with nulls) to Excel.
> >
> > When I export to excel from a Matrix with a row that is missing the export
> > turns all of my rows into text and I get all of the nice green triangles with
> > the warning message.
> >
> > Example
> >
> > Data for 1/1/2007:
> > Column1
> > Item1 7
> > Item3 8
> >
> > Data for 1/2/2007:
> > Column1
> > Item1 5
> > Item2 16
> > Item3 10
> >
> > So the Matrix looks like this:
> >
> > 1/1/2007 1/2/2007
> > Item1 7 5
> > Item2 0 16
> > Item3 8 10
> >
> > I tried going to the value property and setting
> >
> > =IIf(IsNothing(Fields!COLUMN1.Value) = true, 0, Fields!COLUMN1.Value)
> >
> > But it looks like the Null Value is still being sent to the rendering engine.
> >
> > Is this worked as designed or should I be able to force the value to the
> > rendering engine.
> >
> > I fixed the problem within T-SQL by bringing back all values for all days.
> >
> > So now data the data for 1/1/2007 come back like this:
> > Column1
> > Item1 7
> > Item2 0
> > Item3 8
> >
> > But can this be fixed within Reporting Services?
> >
> > thanks
> > Reeves
>
> I had a similar issue in the past and I think I resolved it by casting
> both values in the iif statement to integer or decimal. Something like
> this should help.
> =IIf(IsNothing(Fields!COLUMN1.Value) = true, CInt(0), CInt(Fields!
> COLUMN1.Value))
> -or-
> =IIf(IsNothing(Fields!COLUMN1.Value) = true, CDec(0), CDec(Fields!
> COLUMN1.Value))
> Also, its possible that the IsNothing is not catching whatever
> returned value you are getting in the dataset. Possibly something in
> the underlying nonequivalence between VBNull and DBNull (since SSRS
> expressions are built in VB.NET) In this case, you may want to use a
> case statement in your stored procedure/query that is sourcing the
> report to set the value to something recognizable to filter out/set to
> zero, etc
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Aug 6, 9:24 am, Reeves Smith
<ReevesSm...@.discussions.microsoft.com> wrote:
> Enrique,
> Thanks for the response, but that is exactly what I did. I wanted to fix
> the porblem at the SSRS level and not at T-SQL.
> thanks
> Reeves
>
> "EMartinez" wrote:
> > On Aug 3, 4:54 pm, Reeves <Ree...@.discussions.microsoft.com> wrote:
> > > I was wondering if there a work around within Reporting Services, to fix a
> > > problem with the export from a Matrix (with nulls) to Excel.
> > > When I export to excel from a Matrix with a row that is missing the export
> > > turns all of my rows into text and I get all of the nice green triangles with
> > > the warning message.
> > > Example
> > > Data for 1/1/2007:
> > > Column1
> > > Item1 7
> > > Item3 8
> > > Data for 1/2/2007:
> > > Column1
> > > Item1 5
> > > Item2 16
> > > Item3 10
> > > So the Matrix looks like this:
> > > 1/1/2007 1/2/2007
> > > Item1 7 5
> > > Item2 0 16
> > > Item3 8 10
> > > I tried going to the value property and setting
> > > =IIf(IsNothing(Fields!COLUMN1.Value) = true, 0, Fields!COLUMN1.Value)
> > > But it looks like the Null Value is still being sent to the rendering engine.
> > > Is this worked as designed or should I be able to force the value to the
> > > rendering engine.
> > > I fixed the problem within T-SQL by bringing back all values for all days.
> > > So now data the data for 1/1/2007 come back like this:
> > > Column1
> > > Item1 7
> > > Item2 0
> > > Item3 8
> > > But can this be fixed within Reporting Services?
> > > thanks
> > > Reeves
> > I had a similar issue in the past and I think I resolved it by casting
> > both values in the iif statement to integer or decimal. Something like
> > this should help.
> > =IIf(IsNothing(Fields!COLUMN1.Value) = true, CInt(0), CInt(Fields!
> > COLUMN1.Value))
> > -or-
> > =IIf(IsNothing(Fields!COLUMN1.Value) = true, CDec(0), CDec(Fields!
> > COLUMN1.Value))
> > Also, its possible that the IsNothing is not catching whatever
> > returned value you are getting in the dataset. Possibly something in
> > the underlying nonequivalence between VBNull and DBNull (since SSRS
> > expressions are built in VB.NET) In this case, you may want to use a
> > case statement in your stored procedure/query that is sourcing the
> > report to set the value to something recognizable to filter out/set to
> > zero, etc
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Enrique's solution above with casting worked perfectly for me in SSRS.
Thank you Enrique!
Eric.

Export Embedded Data regions to Excel problem

I have a report that uses a matrix with an embedded table. There are no problems when you view/print it or export it to pdf. However when you export it to Excel the embedded table does not display. Instead the message 'Data Regions within table/matrix cells are ignored' appears where the table should be.

Is it not possible to view embedded data regions in excel or perhaps some setting I have missed.
I'm using Reporting Services 2000 with Service Pack 2.

thanks
Scott

I found the answer to my problem:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp

Any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.

thanks
Scott

|||Then, did you changed the all reports OR you still living with this problem. I am also facing the same problem but am undecisive about the way I should go.

If you did change the reports can you please tell how did you managed it?

Thnaks
Tanveer

Export Embedded Data regions to Excel problem

I have a report that uses a matrix with an embedded table. There are no problems when you view/print it or export it to pdf. However when you export it to Excel the embedded table does not display. Instead the message 'Data Regions within table/matrix cells are ignored' appears where the table should be.

Is it not possible to view embedded data regions in excel or perhaps some setting I have missed.
I'm using Reporting Services 2000 with Service Pack 2.

thanks
Scott

I found the answer to my problem:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp

Any data region nested inside of a table or matrix data region is not supported. An error is displayed in Excel if this layout is encountered.

thanks
Scott

|||Then, did you changed the all reports OR you still living with this problem. I am also facing the same problem but am undecisive about the way I should go.

If you did change the reports can you please tell how did you managed it?

Thnaks
Tanveer

Expanding/Collapsing Dimensions in a Matrix

Hi MSFT,
I have a report that uses data from an MDX query. The result appears in a
drill down format in a Matrix. The report works fine when we click on +/-
buttons. Now, I am displaying the report in a Report Viewer on a web page. On
the same page I have a button, say "Expand All". On click of this button, the
report should appear completely expanded till the last level in the drill
down.
How can I achieve this effect? I havent got hold of any expressions that may
be useful for expanding all sublevels at one go. Please suggest.
Thanks
RishitThe way to access attachments depends on what you are using as your
newsreader.
Note: Some web-based newsreaders don't support attachments at all. If
you're using one of those, you should switch to a different one. The
simplest approach is to use Outlook Express instead.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"jo st charles" <jostcharles@.discussions.microsoft.com> wrote in message
news:3FAB1231-AE28-4F92-AFE1-5E575E604D92@.microsoft.com...
> Please tell me how I can access the attachment. Thank you.
> "Chris Hays [MSFT]" wrote:
> > Attached is an example (from John Miller) of how to do this in a Table.
The
> > approach is similar for a matrix.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Rishit" <Rishit@.discussions.microsoft.com> wrote in message
> > news:D6A65970-A3BB-4CE5-AF7F-8ED93508DA05@.microsoft.com...
> > > Hi MSFT,
> > > I have a report that uses data from an MDX query. The result appears
in a
> > > drill down format in a Matrix. The report works fine when we click on
+/-
> > > buttons. Now, I am displaying the report in a Report Viewer on a web
page.
> > On
> > > the same page I have a button, say "Expand All". On click of this
button,
> > the
> > > report should appear completely expanded till the last level in the
drill
> > > down.
> > >
> > > How can I achieve this effect? I havent got hold of any expressions
that
> > may
> > > be useful for expanding all sublevels at one go. Please suggest.
> > >
> > > Thanks
> > > Rishit
> >
> >
> >

Expand/collapse matrix cells

Hi folks - I am trying to figure out how to set up the visibility
(expand/collapse) for my grouped matrix. So for example:
A
A1 10
A2 15
Total A 25
B
B1 35
B2 30
Total B 70
I want the user to be able to click and collapse to just see this:
A 25
B 70
Is this possible? I get the table, but am confused on how the matrix
handles grouping and visibility...
-RyanIn a matrix, you would have two row groupings. One for A, B, etc. and an
inner grouping for A1, A2, B1, etc.
1. For the inner row grouping, edit the grouping expression and go to the
Visibility tab of the grouping and sorting dialog. Set initial visibility to
"Hidden". Then, select "Visibility can be toggled by another reportitem" and
select the textbox of the outer row grouping heading.
2. For the inner row grouping, go to its heading textbox properties and set
the "initial appearance of the toggle" to Collapsed.
I also recommend to look at the sample reports that come with RS 2000 / RS
2005. Specifically, check the "Company Sales" sample report - it should
provide what you are looking for.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan M" <ryan.marples@.bluetidemanagement.com> wrote in message
news:1128026677.096400.11570@.f14g2000cwb.googlegroups.com...
> Hi folks - I am trying to figure out how to set up the visibility
> (expand/collapse) for my grouped matrix. So for example:
> A
> A1 10
> A2 15
> Total A 25
> B
> B1 35
> B2 30
> Total B 70
> I want the user to be able to click and collapse to just see this:
> A 25
> B 70
> Is this possible? I get the table, but am confused on how the matrix
> handles grouping and visibility...
> -Ryan
>|||Thanks for the pointer Robert. It turned out what I was doing wrong is
... applying the visibility properties to the textbox that represented
the group, not the group itself. Now I get it, thanks!
Now if I could just figure out how to get column heading for my row
groups...
-Ryan

Expand page header as matrix expands

Is there a way to tie the page header to the matrix so it expands to the width of the matrix as the matrix expands?

Thanks.

Vicki Ecker

Hello,

For physical paginated renderes, page header&footer width = page.Width – margin.Left – margin.Right

If your matrix requires multiple horizontal pages, the header&footer will be repeated for every horizontal page.

HTML renderer & Preview – are not bound horizontally by a page width.

The page header&footer will grow based on the runtime body width.

Thank you,

Nico

Expand

Hi,
I have a report with a matrix. The width of the body is 10in and the reportmargings are .5in. Above the matrix is a textbox with some information regarding the report. It as a width of 10in. The matrix has three groups above the columns where I put some daterelated fields in so I can simulate a date hierarchy. The hierarchy consists of the fields Year, Quarter and Month.
When I preview the report and set the Date level to Month the width of the matrix becomes greater than the width of the page. But the width of the textbox stays on 10in. Can this be dynamically adjusted to the width of the report?
QThere's no general "Grow to fit container" behavior in the current version.
But you can take advantage of the fact that table cells do have this feature
via the following sleazy hack:
1. Add a table to your report with one column, two header rows, no
groupings and no detail rows.
2. Put the textbox in the first cell
3. Put the matrix in the second cell
When the matrix expands, it will force the column to expand.
And the contents of the cells (including the textbox) must expand with it.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:3B04CD11-5C12-4E71-BA95-97E185784A31@.microsoft.com...
> Hi,
> I have a report with a matrix. The width of the body is 10in and the
reportmargings are .5in. Above the matrix is a textbox with some information
regarding the report. It as a width of 10in. The matrix has three groups
above the columns where I put some daterelated fields in so I can simulate a
date hierarchy. The hierarchy consists of the fields Year, Quarter and
Month.
> When I preview the report and set the Date level to Month the width of the
matrix becomes greater than the width of the page. But the width of the
textbox stays on 10in. Can this be dynamically adjusted to the width of the
report?
> Q
>|||Brilliant!...;-)
Q
"Chris Hays [MSFT]" wrote:
> There's no general "Grow to fit container" behavior in the current version.
> But you can take advantage of the fact that table cells do have this feature
> via the following sleazy hack:
> 1. Add a table to your report with one column, two header rows, no
> groupings and no detail rows.
> 2. Put the textbox in the first cell
> 3. Put the matrix in the second cell
> When the matrix expands, it will force the column to expand.
> And the contents of the cells (including the textbox) must expand with it.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:3B04CD11-5C12-4E71-BA95-97E185784A31@.microsoft.com...
> > Hi,
> >
> > I have a report with a matrix. The width of the body is 10in and the
> reportmargings are .5in. Above the matrix is a textbox with some information
> regarding the report. It as a width of 10in. The matrix has three groups
> above the columns where I put some daterelated fields in so I can simulate a
> date hierarchy. The hierarchy consists of the fields Year, Quarter and
> Month.
> >
> > When I preview the report and set the Date level to Month the width of the
> matrix becomes greater than the width of the page. But the width of the
> textbox stays on 10in. Can this be dynamically adjusted to the width of the
> report?
> >
> > Q
> >
> >
>
>