I have created a calculated data field for one of my data set. I use it for a table in my report.
But when I try to preview it, visual studio crashes and I get the following error message.
"An error occured during local report processing. An internal error occured on the report server.see the error log for more details."
I have not installd servers on my machine(only the sql tools). So I can not see error logs either.
Hi people My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
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.
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?
I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,break_tue, lunch_tue, dinner_tue, ent_tue, .....etcI want to output the sum of the four monday columns as well as the sum ofthe four tuesday columns.I did this withSELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,break_tue+lunch_tue+dinner_tue+ent_tue AS tue_totFROM expense_reportI now want to add mon_tot and tue_tot AS total, but SQL is telling me thatit can not find mon_tot. Is there a way to do this? Thanks!DarrenMCP
Time columns go up to Dec 07. But I did not show all the columns.
The values are calculated as follows. Field value for Jan07 = (No of Loans pass due in Jan07 / Total No of loans disbursed in Jan07) Repayment due date is 5th of every month. if smbody does not pay on 5th, its considedred as pass due.
Source data tables look like this.
LoanTable(Disbursed date, userID, Amount, Status)
CreditTier table (Credit Tier, Rate) - Seems like no relationship with Loan table
we can identify pass due loans from status field in loan table. Status appears as 'Deliquency'.
I have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom
Hi This may seem amazing and a stupid question but:
Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute ,ApplicationName ,COUNT(*) as Requests FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute WHERE EventClass IN (10,12) GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null. Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?
Help! I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
use Northwind Go select dbo.Orders.OrderID ,Cast(dbo.Orders.OrderDate As DATE)Order_Date , dbo.Customers.CustomerID , dbo.Customers.CompanyName , dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:
Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:
=First(Fields!MyField.Value, "dataset_B")
OR
=First(Fields!MyField.Value, "dataset_C")
This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.
Any advice you have for me would be greatly appreciated!
I am new to SQL Server and am migrating another database
In my original database I have a default(constant) type field and a calculated field both of which call the same user defined function: GetMyUID()
My Function GetMyUID() returns the current date, time and users initials, i.e. "20080522T09:31:15.250LSG"
When a record is first created both fields have identical values
As the record is updated over time my constant field stays constant and my calculated field reflects the time the record was last updated and the initials of that person. So my first field is called 'Created' and my second is called 'Updated'
I would have thought that something like this would be a pretty bog standard and very straightforward requirement in any database
However in SQL I am getting error messages about the return value being non deterministic
I searched the web and found advice that to sort the problem I need to use WITH SCHEMABINDING in my function definition
Unfortunately I am still getting the same 'non deterministic' error
I wonder if (in the quest to not have an overlong field) by looking up the persons initials from a 'STAFF' file rather than leaving the username in full tacked on to the end that this is causing the problem?
I can't imagine that what I am trying to achieve is rocket science but unfortunately have not been able to find any resource on the web that solves this issue for me
In desperation I turn to you
Please help (preferably by letting me have a few lines of code that return the current date/time followed by the username lookup of a Username's initials, here is a snippet of my code...
I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan', COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb', COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar', COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.
This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?
I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.
So it reports for example how much we billed in May 2015, 2014 and 2013.
I have got the table to display this when reporting on one dataset by grouping by year
The datasets both look similar to this
SELECT Practice.ibvSalesByJob.JobBilledExVAT ,Practice.ibvSalesByJob.[Year] FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ...
The calculated field is
=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13") The field I am looking to add together is below =Sum(Fields!JobBilledExVAT.Value)
If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.
However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.
I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.
I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever.
Setup: Windows Server 2003 R2 - Enterprise - SP2 - 32 Bit SQL Server 2014 Express - 32 Bit
Problem: I have a calculated field on a PO table that adds up item prices on an Item table to get the total PO value. This works as expected until there are at least 10 rows in the PO table. From the 10 row on the calculated field stops working and only shows 0.
I have experienced this before and it seems like calculated fields break on the 10th row of a table and onward.
My PO table CREATE TABLE [dbo].[PO]( [ID] [int] IDENTITY(1,1) NOT NULL, [Quote_Number] [varchar](max) NULL, [Customer] [varchar](max) NULL, [CustomerPO] [varchar](max) NULL, [PO_Received_Date] [datetime] NULL, [Total_PO_Value] [decimal](18, 2) NULL,
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
We have 2 servers when you try to change something in a transfer for sql to a text file, it crashes and kicks you out of enterprise manager. I have seen this before. MS recommends applying sp4 or MS03-031: Cumulative Security Patch for SQL Server (815495) This is on a cluster. Has anyone seen this before
When we migrated from SQL 6.5 to 7.0 our users that had DBO privileges moved fine. When we went in and added a new user and tried to give them the same priviledges as the old user, the user looks different and does not have the same priviliges. When the user tries to run the SETUSER stored procedure it tell the user that was newly setup that they do not have the rights to perform the operation. Any help with be greatly appreciated.
We are network admins at a college, with no database experience at all. We use an Altiris product which uses SQL to maintain a database. Our database is OK, but the transaction log has grown to 24 Gb and has caused a slowdown. When we try to back up the database in preparation for shrinking it to the recommended 100 Mb, the server seems to start the backup but crashes (restarts) after a few minutes. Has the situation got out of hand i.e. with files so big, is our server never going to be able to work with it? Or are we missing something obvious?
Any information on resolving the situation gratefully received, thanks in advance.
I am using the DTS wizard to import tables from an AS/400. Some of the tables in the job are quite large and Sql Server either hangs up or completely crashes. My current configuration is WIN NT, SP4.0 SQL Server 7.0 no Service pack. Any suggestions?
I've jumped over from the sister site xtremevbtalk.com to ask the question:
Has anyone ever seen an issue where when installing MSDE, during the installation the pc reboots of its own accord?
After the MDAC is installed as part of the installation the pc reboots and upon boot up, MSDE is not in the program files directory. Instead it's sitting in the C: root. This occurs for a straight MSDE install or with an Instancename install.
There does exist a Microsoft SQL Server folder in the Program Files but it is incomplete. It's missing the Binn folder. Also, there is no reference to MSDE in the Add/Remove programs tool but ther MSDE service does show up in the Services. However I cannot start these as they are pointing to the correct location.
To date, I have tried removing the instance and installation from the registry. I have turned off all AntiVirus software. I have ensured I am using an Admin user profile. I have tried a blank sa passowrd for the installation. all of this is to no avail.
I am seriously stuck with this now and I am in dire need of a resolution so if anybody has any ideas or has seen this one before, I love some help.
I can open a single connection to the database engine successfully but if I try to open another connection ('New Query' or 'query with current connection') Management Studio just goes away without any errors.
I am utilizing a remote desktop connection to the server but if I open a connection from my desktop through Management Studio I can establish multiple connections (database engine, query, etc...) with no problems. There are no errors in the sql logs, event viewer or windows log.
Has anyone seen this? Any suggestions would be appreciated.
