Design Question: Calculated Field
I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.
I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.
Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Calculated Values And Database Design.
Hi everybody,I need some help on calculated values in my database design.I'm creating an accounting / business management application with theusual modules like A/R, A/P, Inventory, etc.I don't know how to handle calculated values in fields like "CustomerBalance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"and other similar.I don't want to create fields in the database to store these valuesbut I want to create them on the fly instead. Another thing I preferto do is use code in my application (vb.net) not T-SQL to do this(stored procedure or views), because this way I'm moving businesslogic into SQL; so far all the logic is located in a Business RulesDLL.My question is how can I do that and at the same time achieve "easy"joins.For example:Every time I retrieve some customer's info I need his balance. Let'ssay if my query is:SELECT CustomerID, CustomerName, Address from CustomersWhere CustomerID = ‘xxxxxx'How can I retrieve the balance, when is calculated from other fieldsin other tables, such as Invoices, Credit Memos and Cash Receipts?I thought to use views and add a join to the above query. Is that goodor bad, performance wise?What other options I have?Looking forward for your answer.Thanks in advanced.Stefanos
View Replies !
View Related
Index In Calculated Field From A Ntext Field
For some reasons I have to use a ntext field for both small strings like "10" and large binalry files. I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc. To trick the sorting I use a calculated field: ORDER BY RSort - where Rsort is: convert(varchar(4), RD.response) as RSort It works but put a high load on the SQL server when the number of responses increases. I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended. What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:
View Replies !
View Related
Calculated Field
HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill
View Replies !
View Related
Calculated Field
I am a newbie to SQL Server using SQL Server 2000. I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)? However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money). The fields that I am basing the calculation on are:- ColumnName Type Size Allow Nulls PurchasePrice money 8 0 LoanSize money 8 0 this is the code for the calculated field ([loansize] / [PurchasePrice] * 100) and this is the description of the calculated field that is forced in the designer ColumnName Type Size Allow Nulls LTV money 8 1 and the designer does not allow me to change the type. I would be grateful of any pointers Regards Tony
View Replies !
View Related
Calculated Field
This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report. What is the simplest way to accomplish this?
View Replies !
View Related
Calculated Field (GPA)
I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO. here is a sample table: Term ID LastName FirstName CRN Subject LetterGrade Calc GPA 20072 1 Doe John 1234 CHEM B 20072 1 Doe John 3214 BIO A 20072 1 Doe John 4321 LAW B 20072 2 Bauer Jack 1234 CHEM A 20072 2 Bauer Jack 3214 BIO C 20072 2 Bauer Jack 5467 FIN B A = 4.0 B = 3.0 C = 2.0 D = 1.0
View Replies !
View Related
Calculated Field
I have the following fields in table A: GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI =================================================== 1000|liability | -10,000.00 | 08 | 2005 =================================================== 1001| asset | 20,000.00 | 08 | 2005 =================================================== 1000|liability | -9,000.00 | 09 | 2005 the fields above have the following datatype: Fields | Datatype =================================== GL_ID | Integer GL_Name_VC | Variable Character Amount | Integer Period_TI | TinyInteger Year_SI | SmallInteger The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Description Amount asset 20,000.00 liability (10,000.00) =========== Net Asset 10,000.00 =========== The above report would list 2 columns as Description & Amount, next it would sort the Description column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability. Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
View Related
Calculated Field In SQL 2000
I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton
View Replies !
View Related
AttribDescription As Calculated Field
Hiin T-SQL,(how) is it possible to concatenate 3 (varchar) fields into one; eitherin a SQL query or through a calculated field (or using a view, ifanybody can explain to me how to use views), according to the followingrules:{first 30 chars of Trim(AttributeVal1)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal2)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal3)}=> define as new field StockItemDescriptionideally I would like SQL Server to do this processing rather thanbuilding all these answer strings on the client side.tiaAxel
View Replies !
View Related
How To Generate Calculated Field
I need to run a data report that will query an Access_Table that has thefollowing Project info:My Form has a command_button with the following code:Private Sub Command1_Click()Set db = New Connectiondb.CursorLocation = adUseClientdb.Open "PROVIDER=MSDataShape;DataPROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &"Access_Table.mdb;"Set adoAge = New RecordsetadoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,IIf([Invoice_Date]<Now()-30 And[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROMCust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimisticSet DataReport1.DataSource = adoAgeDataReport1.ShowEnd SubMy DataReport1 has the following RptTextbox:RptTextbox Datafield1 CustomerName2 OrderNo3 Invoice_Date4 Greaterthan305 Greaterthan60Problem arise when trying to display the above report that has an errormessage that says "Datafield Greaterthan30 not found"Is this an SQL Query limitation where Invoice_Date Datafield can only bequeried once & if we have more than one Datafieldwith the same fieldname (Invoice_Date) within the same Data report,thereport will fail to run? Should there be one moreNew Recordset to hold a second Datafield (Invoice_Date)?can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
View Related
Calculated Field In SQL Server 6.5
I am used to working in MS Access where you can return a value as in: [date1]-[date2]=X It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record. I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause. In Access this would not be a problem. Can you help? Thank you.
View Replies !
View Related
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
View Related
Calculated Field In A Csv File
I have a fairly simple SQL code that includes a calculated field (date difference) in Oracle. I am bring the data in a csv format, and its giving me nothing for that calculation. I checked the results in TOAD and they look fine. I know, i am missing something for the format but have tried everything. I am new to this so any help is appreciated. Thanks select rpt.emc_last_run_date "Report Run Date", (rpt.EMC_ACCEPT_DATE_TIME - rpt.EMC_QUEUED_DATE_TIME * 24 * 60) "Time To Accept(mins)"
View Replies !
View Related
Filtering A Calculated Field
Hi everyone, I created a calculated field (=Fields!RUN_RATE.Value / Fields!STANDARD.Value) this field gives me my % of Standard field. I want to be able to display anything over 200%. I tried filtering it as a group filter, but when it runs it first shows everythin ok, I put my dates (parameter), right when I click to view report, it gives me an error displaying: An error has occured during report processing. The processing of filter expression for the table 'table1' cannot be performed. The comparison failed. Please check the data type returned by filter expression. Any ideas, comments, remarks????? Thanks, Abner
View Replies !
View Related
Calculated Field Issue
Hi I am having a problem with something that was relatively simple in Crystal and i am guessing it is purely my inexperience that is the issue here! Calculated Field =iif(Fields!Fun_Debt_Status.Value <> "debt","", iif(max(Fields!Fun_Days_Overdue.Value)>30,"On Stop", iif(max(Fields!Fun_Days_Overdue.Value)>0,"Overdue", iif(sum(Fields!Fun_OS_Amount.Value)> Fields!CREDIT_LIMIT.Value,"Over Credit Limit","ok")))) Want i am trying to do as you can probably see is create a "Account Status" field depending upon overdue days for payment etc.. My report just produces an error (internal) when i try and run - the calculated field above isn't even on the report. Please let me know what i am doing wrong! I maybe going at it in completely the wrong way altogether, Thanks in Advance!
View Replies !
View Related
Sort A Calculated Field
I have a quick question that i have just a simple calc field. Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
View Replies !
View Related
My Problem With Calculated Field
I have a table for tracking the movement of cash register it has the fields ID Date Amount_Debit Amount_Credit Explanation I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I used this but it gave me an error sum(amount_debit-amount_credit) where id<=id the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show the balance of it can any one help me to get the desired result in the best way possible additional question if possible can I get correct cash balance if I drop the ID field depending on date I use SQL Server 2005 Express
View Replies !
View Related
Using Calculated Field In Where Clause
Dear All; i want to use a calculated field in where clause but this does'nt works: e.g.; select (colA + colB) as calcField from tbl where calcField > 100 what is the right way to do it, as i m getting error; it works like : select (colA + colB) as calcField from tbl where (colA + colB) > 100 but i m using a complex query as i m getting value from a function and then i hav to use that in where clause as well ; Thanx in advance, -- Muhammad Zeeshan
View Replies !
View Related
Re-using A Calculated Sub-select Field In A View?
I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!
View Replies !
View Related
Referencing A Calculated Field In A Query
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005 SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2 This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it. The only way how to avoid the issue in the SQLExpress seems probably to be - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss) - Utilize computed columns if possible (no idea on performance impact here) Am I right or is there any other way how to reference a calculated field? Any suggestion is greatly welcomed! Thanks in advance.
View Replies !
View Related
Create A Calculated Field That Gives Me The Avg 75 Percentile
I'm trying to create a calculated field that gives me the avg 75 percentile. Right now I get this value by doing the following: Create data set: Select top 75 percent <field> from <table> Then I create the following calculated field Avg(Fields!<field>.value,"<data_set_name>") But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value. Is it possible? Thanks!
View Replies !
View Related
Calculated Field Using Two Data Sets
Hi, I'm trying to created a report. Final report looks like this. Total Loans/Lines (#) 13,283 Total Commitments ($ MM) $1,703 Total Outstandings ($ MM) $1,175 A B C D F Bankruptcy 0 $0 $0 0.00% 0.00% Charge Off 0 $0 $0 0.00% 0.00% Source table looks like this; Bankruptcy 0 Charge Off 0 CLTV 131 DSR 102 Exc Total 265 FICO 7 Foreclosure/Repossession Grand Total 13283 Loan Amount 32 Column D = A Bankrupcy(0) / Total Loans/Lines #(13283) But it does not let me to use report expression as its not in the same scope. Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working. Thanks
View Replies !
View Related
Sort A Report On A Calculated Field??
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions" That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
View Replies !
View Related
Views - Filtering On Calculated Field
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period. Then I stuffed the self joins into a couple views for the events I'm interested in... The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time. So how can I speed up the filtering on that calculated column? Or am I going to have to get really creative with unions?
View Replies !
View Related
Calculated Field In Footer...running Total
Hello, How do I add unique values on the report? For example say I have this in my report: Customer: Food Purchased: Amount: Judy Cat Food $12 Sarah Dog Food $13.50 Diane Rabbit Food $17 Jason Dog Food $16 Tammy Dog Food $15 In the footer of the report I want to print a summary box that looks like this: Product: Number Purchased: Total: Cat Food 1 $12 Dog Food 3 $44.50 Rabbit Food 1 $17 How do I do this? Thanks!
View Replies !
View Related
SQL Table, Key, Field Design
i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before. to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job. as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos. i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well? it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know any ideas, suggestions, and help is greatly appreciated.
View Replies !
View Related
Report Model: Drill On Calculated Field In Report Builder
Hi, I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill. Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl. Questions: 1) Can I remove the link from the calculated fields? 2) Can I prevent from users drill to fields that not in the smdl? Thanks, Assaf
View Replies !
View Related
Database Design For Status Field
Hello, I have a database design question. I want to store a status in a table. In the ASP.NET interface for the user this status will be adapted to the language of the user. This is how it would look like: StatusID 1 = "yes" for English, "oui" for French, "ja" for DutchStatusID 2 = "no" for English, "non" for French, "neen" for DutchStatusID 3 = "error" for English, "erreur" for French, "fout" for Dutch I don't want to do the translation in ASP.NET and it should be done in one query. How would it look like in the database design.
View Replies !
View Related
DB Design; &"Field Properties&" Overridable At Various Levels
Hello there, I'm looking for information on how to solve a particular design issue. I'll do my best to explain the issue clearly. I have not been able to find anything through the forum search or google, but that may be because I'm looking for the wrong things. I am by no means a database guru, so any additional pointers would be swell. I guess I should start by explaining what the current application does. I'm tasked with writing a new application and database design with similar functionality. The application simply manages fields and forms those fields appear on. Each field has a set of properties (name, description, min length, max length, and display size for example). Those properties can be overriden at different levels (of which my application must also manage). These levels are Base (default field properties), Interface, System, Field Set, Form, and Instance. Base (Fields) - This is a list of all fields and their default properties. Interface - An Interface has Systems. Field properties can be overridden here. System - A system has Field Sets. Field properties can be overridden here. Field Set - A field set has forms and fields. Field properties for this Field Set's fields can be overriden and the field may appear multiple times. Each multiple is assigned an instance number. Form - A form has fields from the parent field sets's fields. These field properties can be overriden here. Instance - An instance is a particular instance of the same field on a form (i.e. Name appears twice, the first Name is Instance 1 and the second Name is Instance 2). That instance's field properties can be overriden here. When the application attempts to "compile" a form, it looks for the field properties to use. If there is no definition at the Instance, level, it looks for a field at the Form Level. If there is no properties defined, it looks in the Field Set level, and so on up the chain until it reaches the Base. Field properties are carried over completely from one level to another. In other words, if field properties are defined at the Form level, the process can stop looking as all the properties are defined there (they are copied from one of the parent levels and then altered as needed). Here are the table structures and sample data for what I have so far. There are no indexes, yet. Foreign keys are <table>_id columns. set nocount on GO if exists(select * from sysobjects where name = 'interface') drop table interface create table interface ( id bigint identity(1, 1) not null primary key, name varchar(64) not null ) GO insert into interface(name) values('Interface') GO if exists(select * from sysobjects where name = 'system') drop table system create table system ( id bigint identity(1, 1) not null primary key, interface_id bigint not null, name varchar(64) not null ) GO insert into system(interface_id, name) values(1, 'System') GO if exists(select * from sysobjects where name = 'field_set') drop table field_set create table field_set ( id bigint identity(1, 1) not null primary key, system_id bigint not null, name varchar(64) not null, ) GO insert into field_set (system_id, name) values(1, 'Field Set') GO if exists(select * from sysobjects where name = 'field') drop table field create table field ( -- Defines "Base" level id bigint identity(1, 1) not null primary key, name varchar(64) not null, description varchar(255) not null default(''), minlength int not null default(0), maxlength int not null default(0), displaySize int not null default(0) ) GO insert into field (name, minlength, maxlength, displaySize) select 'Name', 0, 35, 35 union all select 'Address', 0, 35, 35 union all select 'City', 0, 64, 35 union all select 'State', 2, 2, 2 union all select 'Zip Code', 4, 4, 4 GO if exists(select * from sysobjects where name = 'field_set_field') drop table field_set_field create table field_set_field ( id bigint identity(1, 1) not null primary key, field_set_id bigint not null, field_id bigint not null, instance int not null default(1) ) GO insert into field_set_field (field_set_id, field_id, instance) select 1, 1, 1 union all -- Name select 1, 1, 2 union all -- Name select 1, 3, 1 union all -- City select 1, 4, 1 -- State GO if exists(select * from sysobjects where name = 'form') drop table form create table form ( id bigint identity(1, 1) not null primary key, name varchar(64) not null ) GO insert into form (name) values('Form 1') GO if exists(select * from sysobjects where name = 'form_field') drop table form_field create table form_field ( id bigint identity(1, 1) not null primary key, form_id bigint not null, field_set_field_id bigint not null, [order] int not null, ) GO insert into form_field (form_id, field_set_field_id, [order]) select 1, 1, 1 union all -- Name select 1, 2, 2 union all -- Name select 1, 3, 3 union all -- City select 1, 4, 4 -- State GO if exists(select * from sysobjects where name = 'field_override') drop table field_override create table field_override( id bigint identity(1, 1) not null primary key, field_id bigint not null, -- Must specify field id interface_id bigint not null, -- Must specify interface at least system_id bigint, -- Is this override at a system level? field_set_id bigint, -- Is this override at a field_set level? form_id bigint, -- Is this override at a form level? instance int, -- Is this a field instance (must also define form_id) -- If a "level id" column is set, all previous column ids must also be set (i.e. if you have field_set_id, -- you must also have system_id and interface_id). These ids must match the defined relationships in the -- interface, system, field_set, and form tables. [level] char(1) not null, -- 'I' (Instance), 'S' (System), 'L' (Field Set), 'F' (Form), or 'I' (Instance) name varchar(64) not null, description varchar(255) not null default(''), minlength int not null default(0), maxlength int not null default(0), displaySize int not null default(0) ) GO insert into field_override (field_id, interface_id, system_id, field_set_id, form_id, instance, [level], name, description, minlength, maxlength, displaySize) select 1, 1, NULL, NULL, NULL, NULL, 'I', 'Name', 'This is the name field in Interface.', 8, 128, 20 union all select 2, 1, 1, NULL, NULL, NULL, 'S', 'Address', 'This is the address field in System.', 0, 35, 35 union all select 3, 1, 1, 1, 1, NULL, 'F', 'City', 'This is the city field on Form 1.', 0, 16, 16 union all select 1, 1, 1, 1, 1, 2, 'I', 'Name', 'This is the second name field on Form 1.', 0, 128, 20 GO set nocount off GO Performance isn't overly important as a small number of users will be used internally by only a few people. I'm looking for maintainability and ease of querying. I'm trying to come up with a way to get SQL Server to tell me what the fields look like at various levels. For example, "Show me all the fields as they would look in interface 'i'" or, most importantly, "Show me what Form 'f' is supposed to look like." I've tried various queries, but I haven't had much success. Unfortunately I do not have those anymore as I saw they weren't working out. For example, for the "Show me form 1" query, I'd like the results: form_id field_id field_set_field_id instance order name description minlength maxlength displaySize ================================================== ================================================== =============================================== 1 1 1 1 1 Name This is the name field in Interface. 8 128 20 1 1 2 2 2 Name This is the second name field on Form 1. 0 128 20 1 3 3 1 3 City This is the city field on Form 1. 0 16 16 1 4 4 1 4 State 2 2 2 I think that's everything. The final question is, I guess, are there better ways of storing/accessing this information in the database, and is it possible to develop the views I'd like? Thank you very much for your time. - Josh R.
View Replies !
View Related
Database Design/query Design
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this: ------- Fixture ------- fix_id fix_date fix_played ---- Team ---- tem_id tem_name ----------- TeamFixture ----------- fix_id tem_id homeorawayteam goals It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field. Fixture contains the details about the fixture like date and fixture id and has it been played Team contains team info like team id, name, associated graphic TeamFixture is the table which links the fixture to it's home and away team. TeamFixture exists to prevent a many to many type relationship. Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View Replies !
View Related
Can't SUM A Calculated Value
I know you can't aggregate a calculated value and I'm having a problem finding a solution I have 3 groups: Course(1), Term(2), Category(3). Course = a particular class (i.e) Algebra; Term = 1 iof 4 terms in our school year; Category = for a class, one may have multiple categories like, participation, homework, quizzes, test, etc. A class can be weighted for a Category like homework and will count 20% of the grade, tests 40% of the grade and so on. For each Category, I calculate the weighted total using the following: =iif(Fields!Weight.Value<>0,sum(Fields!Earned.Value)/sum(Fields!Poss.Value)*Fields!Weight.Value*.01,0) My delema is to sum up all those calculated values in the Term footer. Thanks
View Replies !
View Related
Calculated Member
Hello, I have following problem: A measure in a cube need to be divided by another measure as follows: MEASURE1MEASURE2 Measure 1 (SUM of Money spent by each person) Measure 2 (Amount of Money available for each country per person). Example: USA: 155 Germany:134 France:143) Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134. Any idea how this can be done.
View Replies !
View Related
Calculated Members
This is correct store.currentmember.properties("Store_Manager") I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR Do you can help me?
View Replies !
View Related
Calculated Members
Hi... I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????
View Replies !
View Related
Calculated Member (MDX)
Hi, I need help. I need to do a new calculated member in a cube. This calculated member is named as ITD (Interannual-To-Date) and I don´t how to calculated. It must be something similar to PeriodsToDate. For example, if today is January 18 (2007) I would like to know how many cars had been sold since January 18 (2006) to today. Any idea? Thank U
View Replies !
View Related
HELP !! MDX For Calculated Member
Hi, I am trying to define a calculated member CM1 (avg no of transactions) in a virtual cube -- Cube1 has measure M1 (no of customers) Cube2 has measure M2 (no of transactions) Both cubes have two different time dimensions: dimT1 (time when customer joins the scheme) and dimT2 (time of transaction), respectively. CM1 = M2 / [sum of values of M1 when dimT1.Member <= dimT2.CurrentMember] i.e. To find the Average No of transactions (CM1) on 12-Oct-1998, I have to find No of transactions (M2) on 12-Oct-1998 and divide it by No of Customers who had joined the scheme on or before 12-Oct-1998. Thanks in advance. Regards, Bibhu
View Replies !
View Related
Where To Put Calculated Fields
This doesn't necessarily belong in this forum, but I'm starting here in the hopes of getting some direction. Business users have asked me to "map" a spreadsheet to our Datawarehouse. The spreadsheet contains a lot of calculations. I created a first pass, but it was difficult to map the Analysis Services cube data to the spreadsheet data and in the process I had to hard code a lot of things that will make the spreadsheet less flexible for additional data. So my question is, where is the best place to put calculated fields. In my SQL Statements, in ths SSIS transformations, or in the Analysis Cube? Any help, or pointers to more information, would be greatly appreciated.
View Replies !
View Related
Calculated Fields
I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?
View Replies !
View Related
Calculated Members
I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
View Replies !
View Related
Percentage Not Being Calculated
What's incorrect with my sql so that the pct is not being calculated? I'm returning all zeros. ------------------------------------------------------------------------------------------------ SELECT sa.schoolc sch#, s.schname AS School, Y = sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END), N = sum(CASE WHEN u.logindate IS NULL THEN 1 END), pct = (sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)/ (sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)+sum(CASE WHEN u.logindate IS NULL THEN 1 END))) * 100 FROM users AS u INNER JOIN stugrp_active AS sa ON u.username = sa.suniq INNER JOIN school AS s ON s.schoolc = sa.schoolc WHERE u.username like '3%' AND sa.schoolc >= 300 GROUP BY sa.schoolc, s.schname ORDER BY sa.schoolc
View Replies !
View Related
Calculated Column Or ...
I have a location table with columns for [state tax rate], [local tax rate] and a bit column for each to indicate if it is active. From this I need to calculate a total tax percentage; i.e.(state tax rate * bit) +(local tax rate * bit). If I want to stay at the database with this I can identify three alternatives (I'm sure there are more): 1. Calculated column 2. Table Variable (or Temp. Table) 3. View I'm interested in feedback as to which of these methods is more appropriate or if there is a better way I haven't identified. Thanks
View Replies !
View Related
Calculated Cells
Hi, I'm new to SSAS and MDX and have what I hope is a basic problem: I have a number of measures (all with the same prefix in their name if that is helpful) that I want to use as part of an average calculation. I know I can create individual calculated measures for each one of them, but there are about 100. Is there any way to create a calculated cell that has the functionality to recognize these measures (ie. IF measure like 'prefix%' THEN) and more importantly use something like CurrentMember to apply the average calculation to any of these measures depending on what is selected? In other words, I want to have one calculation that will work for all 100 of my measures vs. 100 individual calculations. I'm using SQL Server 2005. Any help would be appreciated. Thanks, Mike
View Replies !
View Related
|
TRACKER
