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: Using Top N but need to show 'Others' as one

Hi all
I'm generating a column chart, lets say its CustomerID as a category
and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
might give 100 column bars so we filter the category to show, say, Top
20.
Can anyone see a way, convoluted or otherwise, of creating one more
column bar to represent SUM(Sales.Value) for the other 80.
I just want to be sure that I'm not missing something before I dive
into writing code that creates a temporary table to report on which is
the only way I can see.
Cheers
RossAFAIK there is no function for "others". Perhaps you can do this on your
query and then flush the result to the report. Raise your hand if you need
assitance with this.
HTH, Jens Süßmeyer
--
http://www.sqlserver2005.de
--
"Ross" <synergy56@.hotmail.com> schrieb im Newsbeitrag
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||Use two queries and union them together. The first query is your top 10.
The second query uses the first query as a subquery to return anly keys,
then do a NOT IN() against the key selection. This will return all but the
top 10 which you can aggregate into a single valued called "Other"
Basing your your chart on the union of these two queries will give you 11
data points; top 10 and other.
Paul Turley
"Ross" <synergy56@.hotmail.com> wrote in message
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||I did try that, however i'm storing my 'N' for the top N in a parameter, and
i see no way to use a parameter in a query to limit the rows. Likewise i see
no way to add a ranking number in the table itself. anybody?
"Paul Turley" wrote:
> Use two queries and union them together. The first query is your top 10.
> The second query uses the first query as a subquery to return anly keys,
> then do a NOT IN() against the key selection. This will return all but the
> top 10 which you can aggregate into a single valued called "Other"
> Basing your your chart on the union of these two queries will give you 11
> data points; top 10 and other.
> Paul Turley
>
> "Ross" <synergy56@.hotmail.com> wrote in message
> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> > Hi all
> >
> > I'm generating a column chart, lets say its CustomerID as a category
> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> > might give 100 column bars so we filter the category to show, say, Top
> > 20.
> >
> > Can anyone see a way, convoluted or otherwise, of creating one more
> > column bar to represent SUM(Sales.Value) for the other 80.
> >
> > I just want to be sure that I'm not missing something before I dive
> > into writing code that creates a temporary table to report on which is
> > the only way I can see.
> >
> > Cheers
> > Ross
> >
>
>|||You can define a dynamic TopN filter like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = Parameters!Top.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>I did try that, however i'm storing my 'N' for the top N in a parameter,
>and
> i see no way to use a parameter in a query to limit the rows. Likewise i
> see
> no way to add a ranking number in the table itself. anybody?
>
> "Paul Turley" wrote:
>> Use two queries and union them together. The first query is your top 10.
>> The second query uses the first query as a subquery to return anly keys,
>> then do a NOT IN() against the key selection. This will return all but
>> the
>> top 10 which you can aggregate into a single valued called "Other"
>> Basing your your chart on the union of these two queries will give you 11
>> data points; top 10 and other.
>> Paul Turley
>>
>> "Ross" <synergy56@.hotmail.com> wrote in message
>> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> > Hi all
>> >
>> > I'm generating a column chart, lets say its CustomerID as a category
>> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
>> > might give 100 column bars so we filter the category to show, say, Top
>> > 20.
>> >
>> > Can anyone see a way, convoluted or otherwise, of creating one more
>> > column bar to represent SUM(Sales.Value) for the other 80.
>> >
>> > I just want to be sure that I'm not missing something before I dive
>> > into writing code that creates a temporary table to report on which is
>> > the only way I can see.
>> >
>> > Cheers
>> > Ross
>> >
>>|||Ard,
You can use paramters and variables with the SET ROWCOUNT command. You
could also build a string containing your TOP N query and execute dynamic
SQL, but this is not my preference.
Ted
"Ard Goossens" wrote:
> I did try that, however i'm storing my 'N' for the top N in a parameter, and
> i see no way to use a parameter in a query to limit the rows. Likewise i see
> no way to add a ranking number in the table itself. anybody?
> "Paul Turley" wrote:
> > Use two queries and union them together. The first query is your top 10.
> > The second query uses the first query as a subquery to return anly keys,
> > then do a NOT IN() against the key selection. This will return all but the
> > top 10 which you can aggregate into a single valued called "Other"
> >
> > Basing your your chart on the union of these two queries will give you 11
> > data points; top 10 and other.|||Robert
Do you know by any chance how to handle the cases when Top is null? Let's
say you want to give the users the option to select a Top N value or to leave
it null. Thx
"Robert Bruckner [MSFT]" wrote:
> You can define a dynamic TopN filter like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = Parameters!Top.Value
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >and
> > i see no way to use a parameter in a query to limit the rows. Likewise i
> > see
> > no way to add a ranking number in the table itself. anybody?
> >
> >
> >
> > "Paul Turley" wrote:
> >
> >> Use two queries and union them together. The first query is your top 10.
> >> The second query uses the first query as a subquery to return anly keys,
> >> then do a NOT IN() against the key selection. This will return all but
> >> the
> >> top 10 which you can aggregate into a single valued called "Other"
> >>
> >> Basing your your chart on the union of these two queries will give you 11
> >> data points; top 10 and other.
> >>
> >> Paul Turley
> >>
> >>
> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> > Hi all
> >> >
> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> >> > might give 100 column bars so we filter the category to show, say, Top
> >> > 20.
> >> >
> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >
> >> > I just want to be sure that I'm not missing something before I dive
> >> > into writing code that creates a temporary table to report on which is
> >> > the only way I can see.
> >> >
> >> > Cheers
> >> > Ross
> >> >
> >>
> >>
> >>
>
>|||There are two different approaches how to deal with this situation:
1. "Fake Parameter" approach:
* Create a "fake" hidden (non-prompted) parameter PSize to calculate the
dataset size. I.e. the parameter default value would be
=CountRows("ChartDataSetName")
* on the chart, the filter expression would look like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing,
Parameters!PSize.Value, Parameters!Top.Value)
2. "Filter with Duplicates" approach:
Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
Fields!A.Value)
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing, 1,
Parameters!Top.Value)
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marco" <Marco@.discussions.microsoft.com> wrote in message
news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> Robert
> Do you know by any chance how to handle the cases when Top is null? Let's
> say you want to give the users the option to select a Top N value or to
> leave
> it null. Thx
> "Robert Bruckner [MSFT]" wrote:
>> You can define a dynamic TopN filter like this:
>> Filter expression: = Fields!A.Value
>> Filter operator: TopN
>> Filter value: = Parameters!Top.Value
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
>> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>> >I did try that, however i'm storing my 'N' for the top N in a parameter,
>> >and
>> > i see no way to use a parameter in a query to limit the rows. Likewise
>> > i
>> > see
>> > no way to add a ranking number in the table itself. anybody?
>> >
>> >
>> >
>> > "Paul Turley" wrote:
>> >
>> >> Use two queries and union them together. The first query is your top
>> >> 10.
>> >> The second query uses the first query as a subquery to return anly
>> >> keys,
>> >> then do a NOT IN() against the key selection. This will return all but
>> >> the
>> >> top 10 which you can aggregate into a single valued called "Other"
>> >>
>> >> Basing your your chart on the union of these two queries will give you
>> >> 11
>> >> data points; top 10 and other.
>> >>
>> >> Paul Turley
>> >>
>> >>
>> >> "Ross" <synergy56@.hotmail.com> wrote in message
>> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> >> > Hi all
>> >> >
>> >> > I'm generating a column chart, lets say its CustomerID as a category
>> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
>> >> > That
>> >> > might give 100 column bars so we filter the category to show, say,
>> >> > Top
>> >> > 20.
>> >> >
>> >> > Can anyone see a way, convoluted or otherwise, of creating one more
>> >> > column bar to represent SUM(Sales.Value) for the other 80.
>> >> >
>> >> > I just want to be sure that I'm not missing something before I dive
>> >> > into writing code that creates a temporary table to report on which
>> >> > is
>> >> > the only way I can see.
>> >> >
>> >> > Cheers
>> >> > Ross
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Robert this is very helpful
"Robert Bruckner [MSFT]" wrote:
> There are two different approaches how to deal with this situation:
> 1. "Fake Parameter" approach:
> * Create a "fake" hidden (non-prompted) parameter PSize to calculate the
> dataset size. I.e. the parameter default value would be
> =CountRows("ChartDataSetName")
> * on the chart, the filter expression would look like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing,
> Parameters!PSize.Value, Parameters!Top.Value)
> 2. "Filter with Duplicates" approach:
> Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
> Fields!A.Value)
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing, 1,
> Parameters!Top.Value)
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Marco" <Marco@.discussions.microsoft.com> wrote in message
> news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> > Robert
> >
> > Do you know by any chance how to handle the cases when Top is null? Let's
> > say you want to give the users the option to select a Top N value or to
> > leave
> > it null. Thx
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You can define a dynamic TopN filter like this:
> >> Filter expression: = Fields!A.Value
> >> Filter operator: TopN
> >> Filter value: = Parameters!Top.Value
> >>
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> >> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >> >and
> >> > i see no way to use a parameter in a query to limit the rows. Likewise
> >> > i
> >> > see
> >> > no way to add a ranking number in the table itself. anybody?
> >> >
> >> >
> >> >
> >> > "Paul Turley" wrote:
> >> >
> >> >> Use two queries and union them together. The first query is your top
> >> >> 10.
> >> >> The second query uses the first query as a subquery to return anly
> >> >> keys,
> >> >> then do a NOT IN() against the key selection. This will return all but
> >> >> the
> >> >> top 10 which you can aggregate into a single valued called "Other"
> >> >>
> >> >> Basing your your chart on the union of these two queries will give you
> >> >> 11
> >> >> data points; top 10 and other.
> >> >>
> >> >> Paul Turley
> >> >>
> >> >>
> >> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> >> > Hi all
> >> >> >
> >> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
> >> >> > That
> >> >> > might give 100 column bars so we filter the category to show, say,
> >> >> > Top
> >> >> > 20.
> >> >> >
> >> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >> >
> >> >> > I just want to be sure that I'm not missing something before I dive
> >> >> > into writing code that creates a temporary table to report on which
> >> >> > is
> >> >> > the only way I can see.
> >> >> >
> >> >> > Cheers
> >> >> > Ross
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Charting table detail and sum

I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)

Any help would be most appreciated,
Thanks

Sorry, "subtotals" are not directly supported in charts. But I'm not sure if you would really get the desired effect with a subtotal in charts. Since the individual employee groups share the same Y-axis as the total number of employees, you would most likely get many small bars and one long bar showing the total number of employees.
One alternative way is to add a second chart which just shows the total number of employees.

-- Robert|||Just wondering if total chart bar are still not supported ? I need that also...

Charting table detail and sum

I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)

Any help would be most appreciated,
Thanks

Sorry, "subtotals" are not directly supported in charts. But I'm not sure if you would really get the desired effect with a subtotal in charts. Since the individual employee groups share the same Y-axis as the total number of employees, you would most likely get many small bars and one long bar showing the total number of employees.
One alternative way is to add a second chart which just shows the total number of employees.

-- Robert|||Just wondering if total chart bar are still not supported ? I need that also...

chart visibility

Is there a way to may a chart show up if there is no data shwoing up. I
have a pie chart and when there is nothing to sum it doesnt show, I would
either like it to show up or display a message in lieu of that.
ThanksThe chart has a "NoRows" property where you can enter text that will show up
when there is no data
"Ben Watts" wrote:
> Is there a way to may a chart show up if there is no data shwoing up. I
> have a pie chart and when there is nothing to sum it doesnt show, I would
> either like it to show up or display a message in lieu of that.
> Thanks
>
>|||Can you move around and change the format of what shows up when the chart is
blank?
"Kelly" <Kelly@.discussions.microsoft.com> wrote in message
news:0B94CD14-F5C1-44AC-B551-8DAA3B88195B@.microsoft.com...
> The chart has a "NoRows" property where you can enter text that will show
> up
> when there is no data
> "Ben Watts" wrote:
>> Is there a way to may a chart show up if there is no data shwoing up. I
>> have a pie chart and when there is nothing to sum it doesnt show, I
>> would
>> either like it to show up or display a message in lieu of that.
>> Thanks
>>|||Never mind I figured that last part out, but thank you very much, that was
exactly what I needed.
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:O6cUfIiuGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Can you move around and change the format of what shows up when the chart
> is blank?
> "Kelly" <Kelly@.discussions.microsoft.com> wrote in message
> news:0B94CD14-F5C1-44AC-B551-8DAA3B88195B@.microsoft.com...
>> The chart has a "NoRows" property where you can enter text that will
>> show up
>> when there is no data
>> "Ben Watts" wrote:
>> Is there a way to may a chart show up if there is no data shwoing up. I
>> have a pie chart and when there is nothing to sum it doesnt show, I
>> would
>> either like it to show up or display a message in lieu of that.
>> Thanks
>>
>|||You should be able to control the formatting of the message by setting the
Font and TextAlign properties on the chart component.
"Ben Watts" wrote:
> Can you move around and change the format of what shows up when the chart is
> blank?
> "Kelly" <Kelly@.discussions.microsoft.com> wrote in message
> news:0B94CD14-F5C1-44AC-B551-8DAA3B88195B@.microsoft.com...
> > The chart has a "NoRows" property where you can enter text that will show
> > up
> > when there is no data
> >
> > "Ben Watts" wrote:
> >
> >> Is there a way to may a chart show up if there is no data shwoing up. I
> >> have a pie chart and when there is nothing to sum it doesnt show, I
> >> would
> >> either like it to show up or display a message in lieu of that.
> >>
> >> Thanks
> >>
> >>
> >>
>
>

chart on calculated field

Hello

I have a calculated filed in the report, MyPercent:

=Fields!MyTOTAL.Value/Sum(Fields!MyTOTAL.Value)

I want to graph a pie chart on this MyPercent field. I am adding the same formula into value of data, it seems it is not working. Any suggestion?

Thanks,

Yes,

First do you want the chart on the existing report you are viewing where you compute the percentage?

Or

Do you want to call another report (Jump to URL) and then display the chart?

Nevertheless,

If the answer is yes to the first question - why don't you just drag and drop a chart to detail line or group line and display the chart with the calcluated values -- in the initial report that the user views

If not then just pass as parameters to a different report you create and call with the "jump to URL" the values you need to compute the percentage and display the chart within another report

Hope this helps!

Best Regards,

Chart dataset error from sql sum fields

I'm receiving an,
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?