Check For Null Value

Hello,

I have to check for a value of NULL in my field and keep getting a syntax error.

What have I done wrong?

=SUM(IIF(Fields!new_status.value IS NULL, CInt(Fields!Total_NotRec.value), 0))

Is this Excel or Access or something?

|||

Look into the IsDBNull() method (instead of using IS NULL). Use IS NULL within a SQL statement to check for null. Use IsNull() within a SQL statement to return a different value if the field is null. Use IsDBNull() within your VB code to check if a database field is null.

Also, if you're using a DataTable there will be a boolean method for each nullable column called Is[ColumnName]Null().

|||

In SRS, they have a function IsNothing.
When I wrapped the IsNothing function around this, I got a sum of 0.

SUM(IIF(Fields!new_rpcstatus.value IS NULL, CInt(Fields!Total_NotRec.value), 0))

Is there any other way to check for the NULL value?

|||

Hi,

From your description, you are unable to calculate the total value

I'm not sure how do you handle with IsNothing function? Have tried the following expression?

=SUM(IIf(IsNothing(Fields!ANYFIELD.Value),0,Fields!ANYFIELD.Value))

OR

=SUM(IIf(Fields! ANYFIELD.Value is nothing, 0, Fields! ANYFIELD.Value))

If the issue still exists, please use IIF function to check each row's value, to see if it matches the conditions.

Thanks.

Charts for RS 2000

Hi all,

maybe this is the wrong forum or company to post, but I'm looking for an answer, which I can't find at Dundas' Website.

Is there a Version of Dundas Charts for Reporting Services 2000? All I find is the Version for 2005...

And if there is such a version, are there differences concerning the possibility of usinig 2 Y-axis?

One more thing. What would be the arguments for using 2005 instead of 2000?

Replies are very much appreciated! Thanks in advance!

ME

This link should provide an overview on a few the new features in RS 2005 compared to the previous version: http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

The integration of third party components (e.g. as used by the Dundas controls in RS 2005) requires the new CustomReportItem feature specifically added in RS 2005.

-- Robert

|||

Have you looked into Chart FX at http://www.softwarefx.com/SfxSqlProducts/cfxReportingServices/

From their website:
"Chart FX for Reporting Services leverages the charting features from the award winning Chart FX charting engine. Plus it's just as easy to add this powerful technology to your reporting projects.
Here are some features.

Ease of Use & Integration

? Chart Wizard

? Data Wizard

? Property Grid

? Resource Center

? Comprehensive Support

Advanced Features

? Multiple Gallery Types

? Gauge Chart Types

? 2D/3D Rendering

? Per Series Attributes

? Axis Sections

? Multiple Axes

? Logarithmic Axes

? Smart DateTime Axes

? Multiple Panes

? Grid Lines

? Conditional Attributes

? Data Compacting

? Artistic Borders

? Background Images

? Gradients

? Color Palettes

? Titles

? Rich Text Titles

? Point Labels

? Collision Detection

? Data Grid

? Legends "

Good luck.

Charting Issue

1st... sorry about the duplicate post. Was posted under the wrong account.
I have run into a very strange situation and have been unable to locate
the cause of the problem. Running SQL Reporting Services 2000 and
pulling data from an Oracle db for this specific report.
The report is very simple. A bar chart with 2 data sets. One
represented as bars, the other represented as a line. Below the chart
is a table with the text representation of the data being used for the
chart.
On the chart, the data is displayed using the same y-axis scale.
For some reason when I run a report for a specific set of criteria I
get the correct data in the table, but the graph is messed up. The
2nd data point for my line has a value of 39.30 but the line on the
chart draws up to the 120 mark. The strange thing is that the marker
icon shows the value of 39.30.
My data looks something like this:
Bar Line
39.30 39.30
0 39.30
56.50 43.90
17.50 41.40
All of the points on the line show the correct value and are in the
right position except for the 2nd point. It shows the correct value
at the point, but the line shoots up to the 120 mark on the y-axis.
I have tried this running it both on the server and also in DebugLocal
mode on my development system.
I need to get this resolved ASAP for the executive management team.
Any help would be greatly appreciated.
Thanks!Mike
If you see the Value expression for line chart it must
SUM(Fields!ColumnName.Value).
I beleive becuase of the data some where it is summing the value.
--
Thanks & Rate the Postings.
-Ravi-
"Mike H." wrote:
> 1st... sorry about the duplicate post. Was posted under the wrong account.
> I have run into a very strange situation and have been unable to locate
> the cause of the problem. Running SQL Reporting Services 2000 and
> pulling data from an Oracle db for this specific report.
> The report is very simple. A bar chart with 2 data sets. One
> represented as bars, the other represented as a line. Below the chart
> is a table with the text representation of the data being used for the
> chart.
>
> On the chart, the data is displayed using the same y-axis scale.
>
> For some reason when I run a report for a specific set of criteria I
> get the correct data in the table, but the graph is messed up. The
> 2nd data point for my line has a value of 39.30 but the line on the
> chart draws up to the 120 mark. The strange thing is that the marker
> icon shows the value of 39.30.
>
> My data looks something like this:
>
> Bar Line
> 39.30 39.30
> 0 39.30
> 56.50 43.90
> 17.50 41.40
>
> All of the points on the line show the correct value and are in the
> right position except for the 2nd point. It shows the correct value
> at the point, but the line shoots up to the 120 mark on the y-axis.
>
> I have tried this running it both on the server and also in DebugLocal
> mode on my development system.
>
> I need to get this resolved ASAP for the executive management team.
> Any help would be greatly appreciated.
>
> Thanks!|||Thank you!
I have no idea why there was a SUM function in there. Everything looks
right now.
Thanks again!
"Ravi" wrote:
> Mike
> If you see the Value expression for line chart it must
> SUM(Fields!ColumnName.Value).
> I beleive becuase of the data some where it is summing the value.
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "Mike H." wrote:
> > 1st... sorry about the duplicate post. Was posted under the wrong account.
> >
> > I have run into a very strange situation and have been unable to locate
> > the cause of the problem. Running SQL Reporting Services 2000 and
> > pulling data from an Oracle db for this specific report.
> >
> > The report is very simple. A bar chart with 2 data sets. One
> > represented as bars, the other represented as a line. Below the chart
> > is a table with the text representation of the data being used for the
> > chart.
> >
> >
> > On the chart, the data is displayed using the same y-axis scale.
> >
> >
> > For some reason when I run a report for a specific set of criteria I
> > get the correct data in the table, but the graph is messed up. The
> > 2nd data point for my line has a value of 39.30 but the line on the
> > chart draws up to the 120 mark. The strange thing is that the marker
> > icon shows the value of 39.30.
> >
> >
> > My data looks something like this:
> >
> >
> > Bar Line
> > 39.30 39.30
> > 0 39.30
> > 56.50 43.90
> > 17.50 41.40
> >
> >
> > All of the points on the line show the correct value and are in the
> > right position except for the 2nd point. It shows the correct value
> > at the point, but the line shoots up to the 120 mark on the y-axis.
> >
> >
> > I have tried this running it both on the server and also in DebugLocal
> > mode on my development system.
> >
> >
> > I need to get this resolved ASAP for the executive management team.
> > Any help would be greatly appreciated.
> >
> >
> > Thanks!

Chart in Subreport in Table - wrong data is charted

I have a report that contains a single chart. That report works fine by
itself, and as a subreport in many other contexts, but...
When I use that report as a sub-report inside a table, passing all of the
parameters to the sub-report based on values from the current row of the
table...
The chart is labelled correctly (I echo all parameters in the chart title),
but the data that's charted is clearly wrong. The chart always has 3
series, but in any given table, only the first chart is correct. All the
rest "randomly" (but reproducibly) either show all zeros for one or more
(but never all) series, or show data that looks a lot like the rendered
version of the data from the first chart (i.e. the data is the same
magnitude and shape on the printed page even if the real magnitude is quite
different).
I'm sure this is some magic of RS trying to "help" me by "grouping" or
"filtering" or in some other way "fixing" my data, since clearly I didn't
want to simply see the results of my queries charted... but I'm at a loss to
know what to do about it.
Any ideas?
I'm using RS 2005 SP2 on Server 2003 R2 SP2 x64.
-cd"Carl Daniel [VC++ MVP]" <cpdaniel_remove_this_and_nospam@.mvps.org.nospam>
wrote
>I have a report that contains a single chart. That report works fine by
>itself, and as a subreport in many other contexts, but...
> When I use that report as a sub-report inside a table, passing all of the
> parameters to the sub-report based on values from the current row of the
> table...
> The chart is labelled correctly (I echo all parameters in the chart
> title), but the data that's charted is clearly wrong. The chart always
> has 3 series, but in any given table, only the first chart is correct.
> All the rest "randomly" (but reproducibly) either show all zeros for one
> or more (but never all) series, or show data that looks a lot like the
> rendered version of the data from the first chart (i.e. the data is the
> same magnitude and shape on the printed page even if the real magnitude is
> quite different).
> I'm sure this is some magic of RS trying to "help" me by "grouping" or
> "filtering" or in some other way "fixing" my data, since clearly I didn't
> want to simply see the results of my queries charted... but I'm at a loss
> to know what to do about it.
> Any ideas?
> I'm using RS 2005 SP2 on Server 2003 R2 SP2 x64.
PS: I can see in SQL Profiler that all of the correct queries are being
executed with the correct parameters - one outer query by the master report
and one query per row for the sub report, with parameters taken from the
results of the outer query. All queries are OLAP/MDX queries, in case that
makes any difference (I can't imagine how it could, but who knows).
PPS: Yes, I know this would be more efficient if I "simply" built a larger
query and used grouping in a list or table instead of a sub-report, but
that's not the point. The queries are complex, and composing them would be
challenging (at least!); plus, the number of rows from the outer query is
never large.
-cd

Character is not valid

Hi,

I do not know what I did wrong. I tested a simple function in my custom code and get the error saying "Character is not valid". Any help is appreciated!

Here is the function inside my custom code.

Shared Function test() As string

Dim items As Fields
Return items("DataField1").Value;

End Function

I call the function in the body of my report using: Code.test()

and the error says "There is an error on line 18 of custom code: [BC30037] Character is not valid."

Thanks,

Tabbey

Try to remove " ; " after Return items("DataField1").Value|||Also, you cannot refer to dataset items inside of your Report code block. You must pass values into your function.

Something like this:

Shared Function test(fieldvalue as string) As string
test = fieldvalue

End Function

And then call the function in the body of the report:

=Code.test(ReportItems!DatasetField.Value)

|||

I need to build "fields string" in my custom code depending on the parameters users selected.

If I cannot refer to the dataset items inside the custom code, can you think of any other alternative solution?

I construct the fields string in textbox expression, but I get an error if the field did not exist since IIF evaluate the entire expression.

Thanks,

Tabbey

|||Can you create a function with a signature that accepts all the fields you wish to concatenate?

Function addString (field1 as string, field2 as string, field3 as string) as String
if Parameter!UserInput.Value = true then
addString = field1 & field2 & field3
End Function

You could always create a custom data processing extension and handle it there. you could also probably handle this in the logic of your stored procedure.|||

Andy,

That sounds like a good idea. Let me try that out. Thanks!