I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
Hello, I have a problem formating a line in a diagram in SSRS 2005. I want to change the color of the line depending on a value on the x-axis.
I always get an exception that the bordercolor expression for the diagram Object 'Bestandsentwicklung.DataPoint' contains an error. The entry string has the wrong format.
The function is the following: =IIF(Parameters!CurrentWeek.Value.ToString > cdbl(Fields!DimZeit_WocheTag_Woche_MEMBER_KEY.Value.ToString),"Firebrick","Blue") whereby the parameter currentweek should return a number and the field DimZeit_WocheTag_Woche_MEMBER_KEY, too.
Can anyone help me? How do I have to convert the values to solve the problem?
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.
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?
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example). I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
My source table has two columns... Policynum and PolicyStartdate and data looks like.. . Policynum PolicyStartdate 123G 01/01/2012 456D 02/16/2012 789A 01/21/2012 163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum ='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.
Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.
Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.
- Sales Volume: Information about sold articles to a customer incl. Selling date - Survey: irregular answered survey questions about customers incl. date of answer and three Dimensions: - Customer - Date - Survey Answer: Information about possible Answer values (e.g. Yes / No)
We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.
For example: Selected Time period: Jan - Jul 2015 Sales Volume Customer X - Jan - Jul 2015: 1000 Litres Sales Volume Customer Y - Jan - Jul 2015: 500 Litres
Surveys answered:
15th Jan 15: Customer X, Survey Question A: Yes 2nd Mar 15: Customer X, Survey Question A: No 20th Apr 15: Customer X, Survey Question A: Yes
10th Feb 15: Customer Y, Survey Question A: Yes 20th Jul 15: Customer Y, Survey Question A: No
Latest survey answer (Jan-Jul) Customer X, Question A: Yes Latest survey answer (Jan-Jul) Customer Y, Question A: No
Excel Pivot should show something like this:
Question | Latest Answer: Yes | No |
------------------------------------------------------------------------------------ A | 1000 Litres | 500 Litres |
What I need to be able to do is attach additional records for ExpenseYear 9, 10, 11 and 12 using most of the data from the record that has ExpenseYear 8. IE, I need to append additional records that look like this....
Only thing that needs to change in these records is the ExpenseYear and incrimenting the LRDate (I can manipulate the date to do what I need to). I also need to be able to control the ExpenseYear maximum (I can do that with a formula with other data that I am pulling back in the recordset).
I haev tried to use Recursive queries to do this and even tried to use full joins (which I've never used before) and still can't get this thing to work. I woudl like to learn from this and not just use a while loop to push in the extra data. Something more elegant and set based (preferably) would be in order, I believe. If it can't be done, then I'm open to that, too. Thought I'd try for a set based solution first.
If someone could help me out with this I would really appreciate it.
Many Thanks Scott
PS - One more thing I forgot. In some parts of my dataset I already have data to the ExpenseYear that I need (I"m not missing the data to haev to carry it forward) so the query would have to take that into account as well.
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion,
if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth.
If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using.
Select
laa.PlanId,
laa.LoanId, ( 1-5 values are stored)
los.Name
From
LoansAttriApplied laa
Inner Join LoanOptions los on laa.LoanId = los.LoanId
Where
PlanId = @PlanId
So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get
can I display the data in A,B or 1,2,3 instead of
1
2
3
Can someone please give me some insight into this.
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual Region1 Doe10, John X 2003 20400 5100 0 Select Region1 Doe10, John X 2003 0 0 3987 Select Region1 Doe11, John X 2003 29645 7411.25 0 Select Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer, @StatusSent As Integer, @SelectedRegion As NVARCHAR (50) Output
AS SELECT Region.CountryID, Employee.RegionID, Employee.StatusID, Employee.SHARP, CASE When Employee.SHARP = 1 THEN "X" ELSE "" END AS SHARPresult, Employee.LastName, Employee.FirstName, Employee.LastName + ', ' + FirstName AS FullName, Employee.EmployeeID, Region.RegionName, ProducerRevenue.RevenueTypeID, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS AnnGoal, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE()) ELSE 0 END AS YTDGoal, CASE When ProducerRevenue.RevenueTypeID = 2 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS Actual, ProducerRevenue.YearID FROM Employee LEFT OUTER JOIN ProducerRevenue ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND ProducerRevenue.StatusID = 1 AND ProducerRevenue.RevenueTypeID <= 2 LEFT OUTER JOIN Region ON Employee.RegionID = Region.RegionID WHERE Employee.StatusID = @StatusSent AND Employee.RegionID = @RIDsent AND Employee.RoleID = 1 GROUP BY Region.CountryID, Employee.RegionID, Region.RegionName, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID ORDER BY Region.CountryID, Employee.RegionID, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID
I am working on a report which displays profit and loss discrepancy between two systems. So if there is discrepancy, in other words, the dataset returns value, then I want reporting serive to deliever the report to intended users. I am currently using standard edition with no data-driven subscription support.
I do not know what is the workaround for this implementation. Tks for advise in advance.
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;* 8:00 AM Get to work* Independance daysome of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time.. I've tried this...select Event = (CONVERT(CHAR(8),start,8) +' ' + title) from Calendar where CONVERT(CHAR(8),start,8) <> '12:00:00'It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time? Thanks for any help.Mark
i have this: \FILInternalBuisnessArea*.* \FILInternalBuisnessArea \FILInternalBuisnessArea*.qvw \FILInternalBuisnessArea empfileDB.mdb \FILInternalPublic Area*.* \FILInternalPublic Area \FILInternalPublic Area*.xls \FILInternalPublic Areaacro*.doc \FILInternalPublic Areaeconomi*.xls \FILInternalPublic AreaeconomiGroup01FileToSave*.xls \FILInternalPublic AreaeconomiGroup01Unit01FileToSave*.xls
And i want to strip away whatever is after the last '' so the output should look like this \FILInternalBuisnessArea \FILInternalBuisnessArea \FILInternalBuisnessArea \FILInternalBuisnessArea \FILInternalPublic Area \FILInternalPublic Area \FILInternalPublic Area \FILInternalPublic Area \FILInternalPublic Areaeconomi \FILInternalPublic AreaeconomiGroup01 \FILInternalPublic AreaeconomiGroup01unit01
