I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
hey there I have a series of finacial reports. the girls that work on them want color and the boss wants them to print in black and white. the color back grounds print grayscale so I would like to add a link of some sort that changed the background to transparent when they want to print a report.
hu guys, I am trying to change the colour of the background of each row in a table depending on the month it is. I have tried putting the expression =IIF(Datepart("month",Fields!AppDate.Value) = Datepart("month",Now) ,"Green","Red") in the backgroundcolor forthe table row, but it doesn' work.
do you have any suggestions ? or can see why this isn't working?
and a matrix that has row groups; item,type,origin,unit column groups : supplier and price as detail.
this has been achieved
example:
freshegg bestdeal freshmarket Eggs small lo ea 1 2 2
now i want minimum price to be highlighted.
the minimum price on that row to be for example red. I am currently trying in color properties set it like this:
=IIf(Fields!price.Value = Min(Fields!price.Value, "matrix1_unit"),"DarkRed","Black") where matrix1_unit is the last row group, but this is not working right,
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
I have an SSRS report that I want to use conditional formatting on. When the value of the cell = 0, I want the background yellow, and the font red. For the background, I have:
=IIF(Fields!reads.Value = 0, "Yellow","White")
For the font color, I have
=IIF(Fields!reads.Value = 0,"Red", "Black")
The issue is that the background one is working properly, while the font one is not. If the value = 0, the font is invisible. The value is present in the report (I can export to excel and see it just fine). If I change both the background and font to the defaults, the value is STILL invisible.
I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap€¦ My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
I have a column that contains either a test score or the state abbreviation where the student passed a particular test. I want the text to be red between 100-159, green greater than or equal to 160 and black otherwise.
This is what I've tried to take care of the scores:
=IIf(Fields!Score.Value <= 159 and Fields!Score.Value > 100,"Red", "Green")
Two issues: I want all other scores (< 100) to be Black and I'm not sure how to deal with the state abbreviations. I'm assuming this is why I receive this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜Score€™ contains an error: Input string was not in a correct format. Preview complete -- 0 errors, 1 warnings
I am trying to format a background color based on the field's value. But the expression always returns the false result. Here is the expression I am using
Both fill the background color blue when the textbox clearly contains RSB. I'm not sure why it cannot find the value. The field data type is Char(4). The textbox values are all uppercase.
This all started when I was trying to use a report parameter for conditional formatting and I realized that it wasn't working. I broke down the expression by directly trying a value.
I have a report that when the user drills down I need to change the formatting to include borders and/or colours. I tried adding the following statement to top border of the element without luck. Since I don't know when the detail is the last line of the drop down, I am uncertain how to try adding it there.
I do not want the underline to show when there is no expanded drill down.
=iif((Previous(Level) > 0, "Dotted", "None")
g1 name1 total1
g1 name2 total2
d po#3a total3a
d po#3b total3b <<-- want to add underline between total3b and total3
I am creating a report that the requirements need different background colors based on the row or column as shown below:
Community Total # of Respondents Resident's Overall Satisfaction Rating Quality of Repair
May '07 41 3.6 5.0
April '07 14 1.8 3.0
Q2 '07 55 2.7 4.0
March '07 36 3.6 3.0
February '07 28 4.0 1.2
January '07 22 2.2 4.0
Q1 '07 86 3.3 2.7
YTD '07 141 3.0 3.2
December '06 33 3.8 4.2
November '06 27 2.6 5.0
October '06 42 1.8 3.0
Q4 '06 102 2.7 4.1
September '06 58 4.0 2.2
August '06 84 2.0 1.6
July '06 52 3.2 3.4
Q3 '06 194 3.1 2.4
June '06 40 2.4 4.2
May '06 41 3.6 5.0
April '06 14 1.8 3.0
Q2 '06 95 2.6 4.1
March '06 67 N/A 3.8
February '06 38 N/A 2.8
January '06 N/A 3.8 N/A
Q1 '06 105 3.8 3.3
YTD '06 496 2.9 3.5
Rolling 12 Month Average 477 2.9 3.3' I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?
Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).
I have tried approaching it in two ways. One was with a case statement as follows:
CASE
WHEN TargetPer < 83 THEN 'Red'
WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'
WHEN TargetPer >= 90 THEN 'Green'
END AS BGColor
TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:
=Fields!BGColor.Value
Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)
The other approach i have used is the following expression to set the background colour
=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))
For this i still have no yellows just reds and greens!
I am using a matrix report that has subgroups on the columns for table!
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion ( @targetVersion varchar(30), @product varchar(50) ) AS SET NOCOUNT ON; SELECT * FROM MyTable WHERE (product = @product) AND CASE WHEN @targetVersion='' THEN (targetVersion='') ELSE (targetVersion LIKE @targetVersion + '%') END GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line: WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode): if @targerVersion is blank search for records where the targetVersion column = blank else search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this: =Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL 3.0 :0 11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example: Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif( isnothing(Fields!Asthma.Value) ,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1")) ,(iif(mid(Fields!Asthma.Value,1,3)>1.99 ,"DarkRed" ,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
I have a chart with series that needs conditional colour for three conditions. One condition must be transparent. I have tried the various IFF or SWITCH expressions below and some work fine in SSDT but when I deploy them to the browser the formatting is lost. This simple IFF expression works. The series is transparent as required both in SSDT (Visual Studio BIDS) and IE11 and Firefox browsers but I need two other conditions.Â
Both of the expressions below work in SSDT (Visual Studio BIDS) as expected but when deployed to the browser none of this formatting is rendered and all series are in blues even the ‘transparent’ series. No red at all.
I have tried deleting various files RDL and DATA. I have tried uploading the file from report manager in IE and I have tried editing the file in report manager.
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields: 1. MatchID (key) 2. MemberID (Unique ID of member from MemberList table) 3. ResidentID (Unqiue ID of matching member from CityList table). 4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)
In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.
Basically, I would like to implement logic that would work like this:
SELECT Date, Store#, Item#, CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales) ELSE %Total Sales END
FROM (some tables and joins) GROUP BY Date, Store#, Item#
However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
My Query: SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
I have an issue in trying to format rows base on conditions. Below is a replication of the tables and the select statement.
CREATE TABLE #CompareVal (CompareValID INT Not Null , ValName NVARCHAR(75) Null , Vehicle INT Null , Driver INT Null ); GO
[Code] ....
First issue, James and Jane does not have a driver available and that should show "No Driver available"I am to compare values in VehicleAvailable and DriverAvailable to the first row - (Group Value Standard row) so that when a value is less than the value in first row, it should be Gold, if equal to, Blue and if greater than then, Red.
The first row is to be Black. In other for me to be able to compare, I added columns like so:
SELECT #CompanyName , [Description] , ISNULL(CAST(VehicleAvalible AS NVARCHAR(30)),'N/A') AS VehicleAvalible , ISNULL(CAST(DriverAvailable AS NVARCHAR(30)),'No Driver available') AS DriverAvailable , 0 AS TotalVehicles , 0 AS TotalDrivers
[Code] ...
And my expression for "VehicleAvailable" column is :
=Switch(Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Black" , Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Black" , Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Black"
[Code]....
This doesn't work as I am comparing integer against text value. How do I format to get result like the below image?
