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.