Reporting Services :: SSRS - Summing Up Positive And Negative Values Alone From Single Column?

Sep 18, 2015

MS SQL Server 2008 R2

I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.

View 8 Replies


T-SQL (SS2K8) :: Summing - Adding A Negative And A Positive

Mar 20, 2014

I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1

This works fine. (Learnt it here from a previous post.)

I have another query that brings in a result from the same table but as a postive. So that works fine too.

Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.

My question. Is it actually possible to achieve what I'm trying to do?

Output example below - narrow result for testing.

idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2

The 3.80 is correct, it is a positive.

The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6

View 9 Replies View Related

Reporting Services :: Make Bar Chart Color Red If Number Is Negative And Green If It Is Positive?

May 27, 2015

I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart.  The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.

Is there any way to put the bar chart on the right axis and keep the area chart on the left axis?  My goal is to increase the size of the bar chart in relation to the area chart.

Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?

View 4 Replies View Related

Reporting Services :: Export Excel Formula For Summing Column Values

Jun 22, 2009

In SQL server Reporting service we need to export excel formula for summing column values. scenario : After generating report we are exporting report to excel file using report viewer.when user will modify a column value we need to calculate(update) automatically sum of the column values.Basically we are setting excel formula.

View 3 Replies View Related

Positive Negative Values In SUM

Apr 25, 2008


I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.

Ex: My Input Values are


OutPut goes like This

Column1 Column2
-186.5499257 null
-141.8985222 null
-95.3846883 null
-71.1359768 null
-69.39071172 -564.36
30.44750844 null
40.33666032 null
53.81527872 null
98.2967252 null
112.948667 335.84

View 5 Replies View Related

Creating A Blank Row In A Table Between Positive && Negative Values

Oct 29, 2007

I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?


View 3 Replies View Related

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

Jul 27, 2007

I've a stacked column chart (vertical) both with positive and negative value.

I want an horizontal line only for the value = 0.

I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.

Can I show the gridlines only for the value 0?

View 1 Replies View Related

Reporting Services :: How To Get Distinct Values Of Sharepoint Column Using SSRS

May 13, 2009

I have integrated sharepoint list data to SQL Server reporting services. I am using the below to query sharepoint list data using sql reporting services.

<Query>   <SoapAction></SoapAction>  
<Method Namespace="" Name="GetListItems">     
<Parameters> <Parameter Name="listName">       

[Code] .....

By using this query, I am getting a dataset which includes all the columns of sharepoint list. Among these columns, I wanted to display only 2 columns (i.e Region and Sales type) using chart. I have created a Region parameter but when I click preview, the drop down box is giving me all the repeatative values of region like


I wanted to display only distinct values of Region parameter so that whenever end user select region from the parameter drop down, it will display the respective value of Sales type column.Also when I select only RG1 parameter, it is giving me a chart including the sales type of all the Regions. (it should display me only the sales type of RG1) How can I link these 2 columns so that they will display the values respectively.  

View 36 Replies View Related

Reporting Services :: Bubble Map With Negative Values

Jul 29, 2015

I have a bubble map which contains negative values for the data field.

If I Preview a bubble map report within Visual Studio, I can get the expected result.

However, once I deploy this report I cannot get any data back from the report (see screenshot below)

Once Deployed and viewed in IE:

Why this is occurring?

Also, the data field options in the Layer Wizard are SUM and COUNT. Can these be changed?

View 3 Replies View Related

Reporting Services :: SSRS Is Not Subtracting Negative Numbers It Adds Them

Apr 20, 2015

Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84

It should be like this:

Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5

How can I get SSRS to add and subtracted numbers.

View 10 Replies View Related

Reporting Services :: Show Empty Column Values In SSAS / SSRS?

May 6, 2014

In My cube I have :

A Fact table <<Company Revenue>>
2 Dimension <<Month.Dim>> , <<Company>>

I have 4 company say (A,B,C,D) but i have record for only company A & D that too only for December month .

 When I create a SSRS report out of the cube i want a matrix where all the company names to be displayed in the rows & all the months in the columns and company's revenue in data

What my report is displaying :

Company  |   December
A                 |   20000
D                 |   3900  

what is needed :

Company  |   January  |  February | March |  ........... | December
A                 |                    |                  |              |     |  20000
B                |                    |                  |              |     |  
C                 |                    |                  |              |     | 
D                 |                    |                  |              |          |   3900  
How do i achieve this either in SSAS or SSAS.

View 5 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related

Reporting Services :: Selecting Multiple Parameters Values For Comma Separated Values In SSRS?

Jun 17, 2012

I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.

View 5 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

QA Returning Negative #s As Positive

Mar 30, 2004

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.

View 4 Replies View Related

Reporting Services :: Migrate Single SSRS Report Folder

Jul 30, 2015

I have old sql server 2008 with reporting service and new one 2012 version and I need to move single report folder from old server to new server. Is there any tool or script can make that possible.

View 5 Replies View Related

Multi Value For Single Parameter (reporting Services SSRS 2005)

May 1, 2008


I'm using SSRS 2005 which connects to SQL Server 2005. I need to pass Multi valus for single parameter...

E.g. Command type : Text
SELECT * FROM COUNTRY where CountryId IN ( @CountryID)

If i tried to preview using single parameter, it shows the record without any problem. However when i tried to put multi values, it gives error message?? I read somewhere which says, need to set to multivalue. But, i could not find the place to set the multi value option...

How to solve it?? Your suggestion/recommendations greatly appreciated.


View 5 Replies View Related

How Do You Convert A Number From Negative To Positive?

Jan 18, 2008

I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number? One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive. Does anyone know how this can be achieved?

View 5 Replies View Related

How Do I Delibrately Make A Positive Value A Negative Value

Feb 5, 2007

I want to convert a positive value to a negative value.

How do I do it...

View 3 Replies View Related

Treat Data Differently For Positive Vs Negative #

Oct 11, 2005

Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
1 thru 2147483647 = -
-2147483648 thru -1 = -
x/2^24 . (x/2^24)/2^16 . etc . etc
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID

View 2 Replies View Related

Group By Positive Or Negative Number In Table

May 7, 2008

Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:

Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

If you need any more information, please let me know and I will provide what I can. Thanks for your answers!

View 3 Replies View Related

Reporting Services :: How To Export 2 Datasets To 2 Excel Sheets In Single Report SSRS

Jun 21, 2015

I have two different dataset in one Report, Each Dataset result is binded to a different Table component.

When I export as Excel ,I am getting all this in One Excel sheet.

I need this in separate excel, as dataset1 in excel1 and dataset2 in excel2.

View 3 Replies View Related

Reporting Services :: SSRS - How To Sum String Values

Nov 24, 2015

The data source is Fetch XML - so all the values are string.I need to sum the values but getting an error:

Warning  1      [rsAggregateOfNonNumericData] The Value expression for the textrun ‘Textbox25.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric.  Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.I tried converting it to INT but it returns a large number which does not match what I'm expecting.

View 10 Replies View Related

Reporting Services :: Round Up Values In SSRS?

Oct 21, 2015

I want to round 0.356 to 1? How can i do a round up in SSRS reporting services. 

I am using this code:

The Sum of the Price_Call here is  0,3965 ---> I want to round it to one!

= Round (     Sum(Fields!Price_Call.Value), MidpointRounding.AwayFromZero   ) --- > This is always giving me 0

View 4 Replies View Related

Reporting Services :: Multiple Values For Filtering In SSRS?

Oct 3, 2012

I'm building a report that has a column in the table called countries of origin, that column can contain data like "USA","China" ( normal countries) but some rows can also contain "USA, China, Australia" ( multiple counties in one row).

I'm trying to build a filter to filter my results :

-I have a data set created with a list of all world countries

-I've Build a Parameter containing a drop down list with all countries

--- My problem is when I want to create the filter in my main data set , I can so it , but when I search for example for "USA" , I don't get the lines where there are multiple countries and "USA" is one of them...

There must be an expression in the filter that will be able to search through all the values in the string and return me my correct results.

View 19 Replies View Related

Reporting Services :: Unable To Add SSRS Field Values?

Aug 4, 2015

I am having SQL query which has inner join table from another Database. The below highlighted query shows its values.

/*Trend and Capacity Report*/
SELECT MEA.DisplayName AS Cloud, MEB.DisplayName AS VM, OS.PropertyValue AS OS, CONVERT(varchar(17),PD.DateTime,113) AS Date, 
VM3.VirtualCPUCount AS VCPU
FROM Perf.vPerfDaily AS PD
INNER JOIN vPerformanceRuleInstance AS PRI ON PD.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN vPerformanceRule AS PR ON PRI.RuleRowId = PR.RuleRowId 


My request is to calculate total capacity of all available Virtual CPUs, Memory and Storage from 4 separate clouds:  'cloud1','cloud2','cloud3','cloud4'

Whenever I select =Sum(Fields!VCPU.Value) expression in Report Properties and assign it to a variable and assign that variable to a Text Box field I get the value as VCPU * (Number of VMs) which is not correct.

I am looking to get the value of VCPU from all 4 clouds above.

View 9 Replies View Related

Reporting Services :: Passing Values To SSRS Bookmark?

Jun 5, 2015

I am using 2 charts in my SSRS Report. Requirement is to load the Right side chart based on the click event from left chart.

I can achieve it using go to report option. But the issue is total page getting refreshed while using this option. I feel bookmark option will be the best. But I don't think I can pass a value to Bookmarks.

View 2 Replies View Related

Reporting Services :: Getting Total Of Group Values In SSRS

Jun 26, 2015

I am trying to build a report using SSRS.I need to retrieve a distinct value from my initial query and place this in the header or group row.The detail lines are hidden but may be expanded(shown).I then have another group row in which I require the sum of all distinct values from my previous group row.To explain pictorially I have the following:

-Scroll right to see the pink squares.Note that the total in the pink square is the sum of the individual var3 values distinct so 10 even when var3 =4 in euro currency.
-I have tried using sum(var5) but this will give the incorrect value as it sums all detail lines not just the distinct values.
-I have tried using another dataset but when referenced the grouping behaviour expected is non existent so where I expect 40 I get 80 i.e. sum disregarding values of var1 and var2 although these are the grouping variables.
-I need it to be dynamic so I am unable to use IIF(...).

If I could get var4 or var6 to be the totals I require I could work with that.I have also tried the following custom code:

Dim public unitLimit as Double
Public Function Getcpvalue(ByVal limit_amount1 AS Double) AS Double
unitLimit = unitLimit+ limit_amount1
return limit_amount1
End Function


View 2 Replies View Related

Reporting Services :: SSRS 2008 - Trying To Sum Lookup Values

Feb 13, 2012

I have 2 datasets, dataseta is a list of tickets and their relevant owner and databsetb is a list of owners and a days worked first against them.I have a table with a detail grouping on ticketid and a normal grouping on owner which works well... all straight forward to this point. In this table on the owner grouping level I also have a lookup to datasetb which pulls back the days worked per owner which works fine as well. But what I now need to do is total the days worked over all owners, which sounds simple but doesn't work. This is the expression on the days worked for each owner -

=lookup (Fields!owner. Value, Fields!owner.Value, Fields!Days_worked.Value,"datasetb")

If I add a sum around this then it uses the figure above but multiplies it by the amount of tickets in the detail grouping.

View 6 Replies View Related

Reporting Services :: SSRS 2008 R2 - Create A Single Vertical Stripline At Specific Date Value Along X-axis?

Jun 5, 2012

I'm struggling with trying to display a single vertical stripline at a date value along my x-axis. 

I'm using the following non-default settings within the stripline editor:

bordercolor = tomato
borderstyle = solid
borderwidth  = 1pt
intervaloffset = Fields!uidate.Value.ToOADate()

where uidate is a calculated field in my dataset that always has the value of 05/29/2012  (using formatdatetime("05/29/2012",dateformat.ShortDate))

Everything else is set to Auto, with stripwidth=0

I'm trying to display a single vertical line at the x-axis value of 05/29/2012

I have previously tried setting the interval offset to "05/29/2012" but that didn't work. I've also tried setting the value of a hidden text box on the report to "05/29/2012" and setting the interval offset to the value of that text box, but that gave me a "text box not declared" error.

How I can simply display a vertical stripline on my line graph at the x-axis value of 05/29/2012 ?

View 2 Replies View Related

Reporting Services :: Add A Variance Column Into SSRS Report Where Column Headers Are Non-static?

May 13, 2015

I am creating a report in SSRS which has the following criteria:

- Row 1 (parent) is 'Product'

- Row 2 (child) is  'Feed'

- Columns are date.  I have 5 dates showing at any one time across the top.  The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.   

 - Data is the number of records.

I have a sub total on the Product and the report is collapsed on Product as default.

What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates.  So the difference between the date the user selected (@date parameter) and the day before that. 

View 6 Replies View Related

Reporting Services :: How To Extract Only Numeric Values From A String In SSRS

May 29, 2015

I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these  multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.

=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",   ",","),",")
The value will look like this
02-Senior Mange
25-Associate Trainee

This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()


Result will be   01

But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.

View 5 Replies View Related

Reporting Services :: Ssrs 2012 Change Parameter Values

Apr 22, 2015

In an existing SSRS 2012 report, I have a requirement for a user to be able to select by multiple school(s) and/or multiple grade(s). This is fine except certain schools like elementary have grade levels of KG to 06, Middle school has grades of 06 to 09 and high school has grades of 10 to 12. Thus for example, if a user has initially selected grades 11 and 12 and then they select an elementary school that has grades KG through 06, the 'grade level' selection would need to change.In other words,  I am thinking of initially having the available and default values for the parameter called 'Grade' set to KG through 12. However if a school is selected that only has specific grade levels like elementary school, how can I override the original grade level and only allow the user to select grades that the particular school contains?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved