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.
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?
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?
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: Positve 1 thru 2147483647 = 0.0.0.1 - 127.255.255.255 Negative -2147483648 thru -1 = 128.0.0.0 - 255.255.255.255 Conversion positive x/2^24 . (x/2^24)/2^16 . etc . etc negative (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), IPSubnetMask.IP_Start FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID UNION ALL 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), IPSubnetMask.IP_Start+4294967296 FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
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!
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?
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords UUUDUUUDUUUD1230123012301175 ------------0000000000001163 UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords UUDUUUDUUUUU-----------07 UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
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?
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
I have a Stacked Column graph and when we set the Position of the Point Label to be "Top Center", the Point Label always stays in the center of that particular Bar.
On a easier, basic Bar chart, the Point Label setting works correctly.
Has anyone else had a similar issue or know if this is a known issue ?
I'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..
The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed". Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.
So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?
Is there a way to get markers on the top of each graph of a stacked column chart? Also i need to display a label when the mouse points over each of the marker, as well as each partition of the graph. Getting the tooltips to work might b a help but it is not working for me. I got a reply that enabling the mouse over in the report viewer might help, but i dont know how to enable
I have a stacked column with 2 series("phones", "no phones") on the categories, What I am trying to do is to show only one of the series in each category,I used the visibility option in the series properties, and chose show or hide based on an expression using the expression below if(Fields!PENETRATIONSERIES. Value="no phone",FALSE,TRUE) i get no graph , But when I switch the true and false value, the graph show with the two series there.
I need to generate weekly report for data usage for my c drive and d drive.the graph should be likefor a particuluar date, I want to display graph like this. even if I change secondary axis, my c: drive used and free space not coming in single column. it comes like c: drive used space, on top of it d: drive used space and in adjacent column  c: drive free space, on top of it d: drive free space.I don't want to add two category one for date and another for driver names.
I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.
I have an existing stacked chart with line graph as secondary. the value of line graph is finished good qty. now i need to removed the finished good and change it with a target line chart.   May i know on how to work change this to line. The target value is 68%.
I have a report in which I have assigned custom colors with a function to one of my stacked column charts. When I run it in the report builder, the colors are correctly placed, but as soon as I upload the report to the server and view the report from there, the colors get swapped. I have these custom colors applied to other stacked column charts in the report and they work perfectly fine. The only difference between this chart and my other charts is that it contains two category groups.
EDIT: Alright, I have gotten it to display the correct colors by using a function with the colors I wanted in reverse. I have two values, "Upcoming" which is displayed in red, and "Outstanding" which is displayed in blue. If the chart has only upcoming values, it will display in blue rather than red, whereas if I have upcoming and outstanding values, they will display in their correct colors. why this is occurring?
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
The problem is when I check for a value > 00.1 in the having clause the total balance column disappears. But its ok if I use any other comparison operator. I.e < 00.1.
Any ideas.
create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as
Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog]( [StlyeID] [int] ) ON [PRIMARY] with data: 1 2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes]( [SizeID] [int], [SizeName] [nchar](10) ) ON [PRIMARY] with data:
1,Small 2,Medium 3,Large 4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes]( [StyleID] [int], [SizeID] [int] ) ON [PRIMARY]
1,1 1,2 1,3 2,1 2,3 2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT t_Catalog.StyleId, t_AvailSizes.SizeID FROM t_Catalog INNER JOIN t_AvailSizes ON t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
I am trying to develop a report that will show a 100% stacked bar graph for many teachers. Each teacher will have there own graph. The series in the report are the percent of scores given on a test, with each test in a group. Scores are given on a scale of 1-4. Most of the time a teacher will have all fours scores in there data; however it is possible to only have 2 or 3 of the scores. How can I set the legend up so that all scores across all the graphs show the same color for the same data series? Right now I get something like this. First Graph 4: Lime 3: Purple 2: Blue 1: Green Second Graph (no values for 1 or 2) 4: Blue 3: Green
I have a stacked bar with Line marker. that shows the trend contribution. My concerned is the line marker was behind the stacked bar which it should be at the front or before the stacked bar. how to fixed this in SSRS and Report builder. The line marker is the finished good qty while the 3 other legend WIP, FG and Scrap is the Percentage.
How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money
I have a stacked bar chart which i notice a vertical line char. how can i removed this vertical line? I already modified the properties under smart labels under calloutlineanchor, calloutlinestyle, calloutstyle to None but still getting this vertical lines. another things is the data label as you can see in the the first column the 1.8% should be place under the gray color but its already shown before the orange color.