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
Looking for a decent source that has some examples of column formulas aswell as a list of column formula functions that can be used and how to usethem.Hard time finding something online. Any suggestions or references would begreatly appreciated.Fairly new to this.Thanks
View 1 Replies View RelatedI'm building a custom component and UI and am a bit confused on where I
need to create and/or set custom column
properties?
My UI will have a datagrid with three
columns: 1) a check box to select a column for use by the component, 2)
the input column name, and 3) a "differentiator" checkbox that indicates
an extra property about some of the columns that have the first column
checkbox checked (For example, my component may be using five input columns, but
three of those need to be used in a slightly different
way.)
The problem is, I don't understand when or
where I'm supposed to create the custom property for the input
column. SetUsageType is where I've been thinking, but I don't
know if I'm supposed to be creating it for an input column or a virtual
input column. I'd appreciate any guidance.
I am converting the contents of 64 lookup tables from individual tables (each called lookup_xxxxx) into a single LookupReference table. The individual lookup tables are my OLE DB Source objects. I want to derive the variable part of the lookup_xxxxx table name from the OLE DB Source 'OpenRowset' property as a variable and make it into a derived column (which will be the lookup Type column in the output table). For example, extract "SpeciesType" from the input source called 'lookup_SpeciesType' and put it into the derived column.
I cannot find a System variable that refers to the input data source. Does anyone know how I can do this?
Any help much appreciated. Thanks
I am trying to create a hard copy of all the table definitions in the database and have not found a way to do so. The report would have all the columns for a partcular table and each property assigned to the column.
For instance Customers table has CustomerId, FirstName, LastName, Phone, etc. The CustomerId is PK, Identity, Auto Increment, BIGINT, NOT NULL. FirstName is NVARCHAR(30), NULL. LastName is NVARCHAR(30), NULL. Phone is NVARCHAR(20), NULL.
Can anyone point me to the right system function or sp to get this info?
I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties.
FirstName have [First Name] extended propery called M_Caption.
Then I created a view from the table to use in an access database.
My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Id] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT FirstName, LastName
FROM dbo.Person
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Person"
Begin Extent =
Top = 6
Left = 38
Bottom = 99
Right = 189
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
Thanks
through MS SQL Server Management Studio I have a database defined as:
dbname
- Tables
- dbo.xyz
-Columns
Field definition_a
Field definition_b
Field definition_c
...
For each "Field definition" I can define "extended properties"
So the question is, from a stored procedure in C#, how can I read through the "Field definitions"
looking for an extended property of a specific integer value?
/Boyd
I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.
and filestream filegroup at following address will be empty. why?
Right click on table --> properties --> storage --> filestream filegroup
Hey all-
I put togehter a package that opens a flat file, parses the data based on the semi-colon delimeter, and imports the rows into a database table. Thats the fun easy part.
What I cant figure out is how to add a variable that will hold a constant ID value that will be persisted with the same value to all rows inserted to the DB. Making the problem harder, I would like that this value be defined in a properties file or database table of some sort so that I can do a lookup based on the file name / location to find out what value should be used.
Any suggestions? I hope my explanation makes at least some sense - but basically I want to do a look up in a configuration of some sort, pull out a single value, and add it to a data import.
let the fun begin!!
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
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.
is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc? Cheers
View 2 Replies View RelatedHi and thanks for your help
specifying the default database means what. for instance when I add alog in user and I have to choose a default database. Does it make a difference if the Default database is Master or any other user database name. What is the significant to select Master vs any other database.
Thanks,
Ahmed
Hello!
We are having problems with a single SQL Server where the Server properties will not save, and always revert to the default.
It occurs only on those tabs with "configured" and "running" options at the bottom. Other tabs will keep the settings we custom.
The big problem is with the memory and processor tabs which even when we've made only a single change and stopped and started the service, reverts back to a default.
Any clues - we are logged in as "sa" and have not removed the SA priviledges.
Thanks for any help,
Simon
Once upon a time, I went to check the properties of an instance and EM just hung - anyone know why this might happen?
View 1 Replies View RelatedHi all,
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty
@name = 'col_Programs_ID_Description',
@Value = 'The Unique Identifier for a specific Program',
@level1Type = 'Programs',
@level2Type = 'ID'
I get the following error:
Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42
An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Thanks
Mariah
Hi all,
I'm having a problem need your help:
I can see all properties of datafile (FilegroupName, FileID, FileType,
Location, CurrentSize, Space Used) by Enterpise Manager.
Please tell me how do I see all those properties by SQL Query Analyzer.
I want to know actual space used in data file to shrink file.
Thanks in advanced,
TN
Running SQL2000 SP3.
We installed an application named Streamserve. Now, if I go into Enterprise Manager I can expand the databases fine. But when I try to right-click properties Enterprise Manager goes bye bye. No error messages. I tried this on master and same thing. I currently have 2 different machines - a dev and a QA box and both are behaving badly.
Any ideas?
Thanks
Sharon
Hi,
when ever I try to look at the properties of a job, the create new job window appears. what gives?
When I create a new database it asks for information regarding the Data files.
What is it for? Will this file get creted to the location that I write?
How often will it get copied there?