Looking for a decent source that has some examples of column formulas as
well as a list of column formula functions that can be used and how to use
them.
Hard time finding something online. Any suggestions or references would be
greatly appreciated.
Fairly new to this.
Thanks
I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.
Thanks
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
ive come from a crystal background and im having difficulty writing reports.
An example of the problem im having is with saved formulas (crystal calls them formulas)
eg
i would create a forumula called Difference
it would be something like this
=DateDiff("d",Field1, Field2) save that and then it was stored
then in a text box i would reference it like so.
=iif( Difference <= 23,"Too New","Just Right")
is this possible in RS??? as i have reports i need to convert from crystal to RS, and the iif statements are becoming huge, especially when you reference the same formula in the expression multiple times.
bit long winded i know, and i hope this makes sense, but i am witts end with this issue
thanks in advance
Jonny
im moving over from a crystal background, in crystal i was able to create custom formulaes and use them somewhere in the report, can this be done in RS, if so how?
thanks
Jonny
Sorry, I made a mistake. How can I delete this message?
View 3 Replies View RelatedI'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?
---- Jake
I'm creating a report that is designed to be exported to Excel so that the end user can manipulate the data. There are two main columns that I'm concerned with - TimeTaken and OTTimeTaken (for overtime).
Our application does not track OTTimeTaken so it's default will just be 0, but I need the excel file to have a formula that automatically adds the TimeTaken to the OTTimeTaken in a third cell for the total number of hours as that value will be added by the A/R department for invoicing.
I thought adding this would give me what i need:
="=SUM(C" & RowNumber(Nothing) & ": D" & RowNumber(Nothing) & ")"
when exporting to excel, it dumps it in as plain text, requiring that the user double click on the cell in order to "activate" the formula.
Is there a way to have SRS create this formula so that it works in excel rather than requiring the additional user interaction?
Thanks!
Trying to get the blank out of my report and replace with (0). My formula is SUM(Loan Amount). If there is nothing to sum there is a blank space. Would like it to have a (0) in it.
View 1 Replies View RelatedI'm working with a DB design that seems to me to be rather complex.This is a very slimmed down version of what I'm doing, but I believe itis enough to get my question resolved.Here is my layout.These 4 tables are used to generate a questionaire.Survey OrderID========= ==========SurveyID OrderIDOrderID QuestionGrpIDQGrp Questions============= =============QuestionGrpID QuestionIDQuestionID QuestionTextThe following two tables are used to calculate a report that is sent tothe customer.RawData=========================OrderIDQuestionIDValue is string but is Cast as decimal for numeric formulasMetrics==============================================QuestionGroupID | ReportText | Formula | MetID==============================================2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1The OP references are questionIDsNow to calculate the result for the report we programatically parse theformula creating a temp table (table name = Temp & orderID & _ &QuestionID) with OrderID and OPxx as the field names. We create onetable for each question.We then use dynamic SQL again to calculate the result using the aboveformulaSELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERETemp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =Temp5_23.OrderIDThis select is used to create a single table of calculated values.This table is in turn used to tell the customer how they compare toother customers. Percentile, Mean, Median, Std Dev, and a few others. Idon't claim this part of the project, but I'm not sure how I might havedone it, had it been assigned to me.MY PROBLEM!!!Sometimes a 0 is valid data and is the denominator of a devisioncalculation. Since this is so dynamic and it might be difficult todetermine when division is used. I need a way to default divide by 0execptions to NULL. This DB is on a hosted server.Thanks for bearing with me,Greg Kelley
View 2 Replies View RelatedI've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2
4
3
6
0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
Any help will be appreciated.
Re: SQL Server Reporting Services
I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.
Please help! Thanks!
Hi,
I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.
Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:
Code Snippet
=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value
This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.
99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...
I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.
Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;
- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)
- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)
Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.
And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.
Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.
*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...
Thanks in advance for any advice
I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.
View 11 Replies View RelatedHello all, I´m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:
iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,
null,
iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,
Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),
Sum([Time].CurrentMember.Children)
)
)
<<this formula overrides the week aggregations>>
and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how?.
Thanks in advance!
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedHi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedHi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
How to change column size dynamically depends on content of the column.
View 2 Replies View RelatedI 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.Â
I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale  in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int)
As
Begin
Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1'
print @Sel
Exec Sp_executesql @Sel
End
--Execute Mypro 1,4 --->>Here I Executed
Error
Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between 1 and (1+4)-1
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM
Procedure successfully created but giving error while Excuting'.
Please anybody give reply
Thanks
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedWe have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies View RelatedUsing MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Is it possible, if yes then how?