create a "Yesterday", "This Week", "Last Week" calculated member

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Installing SQL Server 2000 SP3a Hi, I wonder if anyone can help me. I have the same issue. When attempting to install SP4 using the command: setup /upgradesp sqlrun INSTANCENAME=MICROSOFT##SSEE /L*v d:\msdelog.log I get the error: "The product is not installed. Check it a appropriate instance of MSDE is installed." Can anyone shed any light on this Thanks Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 2
    - Bookmark in Code not working. I have a report in reporting services that has a bookmark value asigned to each row. In the following Page_Load event for the page, I'm getting the following error on the ReportViewer1.JumpToBookmark("6") statement: "An exception of type 'System.InvalidOperationException' occurred in Microsoft.ReportViewer.WebForms.dll but was not handled in user code Additional information: Some parameters or credentials have not been specified" Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then Dim strReport1 As String = "/ReportDirectory/CurrentWeekSales" ReportViewer1.ServerReport.ReportServerU rl = New System.Uri(CStr(Session.Item("ReportServ er"))) ReportViewer1.ServerReport.ReportPath = strReport1 ReportViewer1.ServerReport.Timeout = 500000 Do While ReportViewer1.ServerReport.IsDrillthroug hReport ReportViewer1.PerformBack() Loop Dim RptParameters1 As ReportParameter() = New ReportParameter(0) {} RptParameters1(0) = New ReportParameter("EBO_EmpKey", CStr(Session.Item("UserKey"))) ReportViewer1.ServerReport.SetParameters (RptParame ters1) ReportViewer1.ServerReport.Refresh() ReportViewer1.JumpToBookmark("6") End If End Sub What am I missing Thanks Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 3
    - Slow Running Insert After Changing OLE DB Destination Data Access Mode The other option for the original poster is to use a lookup in midstream to see and test records before inserting them, and if they are in error, you can redirect those to whereever, while pushing the valid records to an OLE DB Destination using fast load. If at all possible, use the processing logic of SSIS to test your records -- don't let the database do it for you. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 4
    - SQL Server Assertion ERROR I installed the SQL Server 2005 SP2 (CTP) and it seems to have fixed the problem. I dont see this error anymore on my machine. Thanks very much. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 5
    - Management Studio -> Could Not Find Function xp_msver in library xplog70.dll What happens when you manually run xp_msver from a query window Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 6
    - SP2 Calculated Member Issue I have had this calculated Member for over a year, when I applied SP2, I am returned an error. I have looked on MSDN for something that would indicate what the change was, but cannot find it. Here is the MDX: Case When IsEmpty( [Measures].[Loan Amount] ) Then 0 Else ( [Product].[All] * [Channel].[All] * [Date Closing Funded].[All] * [Loan Status].[Status Rollup].&[Closed]* [Date Closing Funded].[MonthName].CurrentMember, [Measures].[Loan Amount]) End Again, this has been working for over a year. Any help would be greatly appreciated! BobP Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 7
    - about database and table protect hello, We wrote an erp,and provide a platform to participator to extend my erp system,so I will give my participator database dictionary,but I only want to give partial database dictionary,I will hide some table and some field, I want they cann't open the database thouth sql server management studio or other tools,only can using our interface to access database,how can I do -- I use sql server 2005 Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 8
    - Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=421936&SiteID=1 Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 9
    - SQL2005 C# connection problem When attempting to connect to a SQL2005 database using Visual C# Express edition I receive the following message: The user instance login flag is not supported on this version of SQL Server. I do not have this problem when using SQLExpress Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 10
    - how to design queries graphically? This is a great product for laying out (refactoring) SQL: http://www.red-gate.com/products/SQL_Refactor/index.htm However it can't create graphical representations of queries. To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text. Chris Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 11
    - ASP: error when trying to insert record Hmmh....is that a question If yes, you will have to post the code from line 49 to see what is happening there. Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 12
    - insert into exec distributedprocname The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 13
    - how to use variable to create index Hi , I would like to create index for a table and that index name must be random generated. How to do this declare @value varchar ( 50 ) set @value = rand () set @value = @value + 'index-name' create index @value on tablename(variables) Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 14
    - SQL CE 3.1 Why is there no option for a char vs nchar data type The app I'm building has char based data, not nchar. My SDF database is twice as large and twice as slow as it needs to be. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 15
    - Disk activity We are about to go to SQL 2005 and that means getting some new Servers and i am wondering if our SAN is adequate. I like to measure over the next few weeks the amount of data that is sent to the SAN and how long its taking to come back....i.e if someone is running a large report how much data is sent and how long it takes to return. This is for 2000 SQL (i like to get all these results to build my migration plan and hardware configuration). I like to see...........18 mb of data , date time and how much read / write etc if possible the sql. Any ideas Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 16
    - Check the finish time of data driven subscription using Sql There is a 'status' field on ExecutionLog that says 'rsSuccess' if the job was successful. Unfortunately, the times don't match and there is no ID on the executionlog to correspond to the subscription. I believe the times are different because when the report is done running, it writes the executionlog, but the email/file delivery has to take place before the subscription is completed. The ExecutionLog.TimeEnd should be fairly close to the Subscriptions.LastRunTime, but not exact. If you run your report every hour, then you can check to see if the two times are within, say, 10 minutes of each other. Jarret Tag: create a "Yesterday", "This Week", "Last Week" calculated member
- Next
  - 1
    - Cluster Query Hi All, I am deciding on high-availibitly for my database server box. My website can get large number of user hits for 4 days in every quater of year. We are also planning to launch some schemes which will help keep are business and servers busy . I am planning to have 2 node cluster. With configuration Windows Server 2003 Standard Edition 64 bit SQL Server 2005 Standard Edition 64 bit 4 GB RAM 8 x 300 GB Ultra 320 SCSI Hard Drives Hardware RAID 0+1 Redundant Controllers Fiber Attached My Question are. 1) What type of clustering I should go for (Active/active vs Active/Clustering) and why 2) Will active/active means load balancing as there will be 2 servers as active and both of them can share the load 3) What are the advantages and disadvantages of clustering type you recommend in question 1. 4) We are also planning to configure transactional replication on these servers as per disaster recovery plan. Will it an overhead to the servers if they are in different geographical locations (My network bandwidth is 100 mbps connection) Please help. Thanks in advance. regards S Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 2
    - Dealing with two databases Hello, Question 1: In my senario i've developed a system which utilizes 2 database, i've writen queries like db1.dbo.table1 join db2.dbo.table2 etc... Now that db2 is getting huge, client wants to shift it to another server. I don't know how to modifiy my queries to cope with such situation. Could somebody plz tell me on how to you write queries involving two databases from different servers. Question 2: I'm maintaining second database (db2) to keep track of records of db1 which have been processed by my software, so that when db1 gets added with more records i can compare db2 table with db1 table to identify which records are new. db1 is not my database and i don't have any control over that, (it's some erp db), is there any way of identifying which rows have been processed. Can the need for db2 be eliminated I'm using SQL Server 2005 Thank You, Sandeep. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 3
    - Unable to connect remotelly to mdf.life on sql server 2005 express edition I have a c# application running on my machine. I want to point to a sql server data base express edition running on other machine. By default, from server explorer Ionly acces data base files of the local machine. I dont have acces to remote servers. I used the file explorer and opened the mdf file on remote server. I tried to acces the mdf file remotely but i got excepcion. My questios are: - Is sql server 2005 express edition supporting remote connection to mdf data base file - In other words, I can remotelty conncect to mdf file from remote machine So, I how can point my application to remote sql server 2005 express edition data base running on other machine Please, Help Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 4
    - same value for different parameter values Yes, but you have to create a cube for your report. The main reason for using the cube is your current requirement is quite suitable for cube structure. If you have enough time, try that. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 5
    - What is wrong with my codes to connect VB.net to sql server 2005 Express database? My previous 'suggestion' was missing the keyword 'AS' in front of string causing the error. Reviewing your original code: Dim DB_Location = " c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Trial1.mdf " (REMOVE -NOT NEEDED) Dim ConnStr as String = "Server=localhost; DataBase= Trial1; Integrated Security =SSPI" Dim sqlConnection As New SqlConnection(ConnStr) 'Use database Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' " Dim cmd As New SqlCommand(strSQL, SQLConnection ) I suggest ConnStr is more understandable than MyConnection , since it is a connnection string and NOT a connection object. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 6
    - Memory allocation error while processing cubes I am seeing the same disk space issue as described in original post. Each partition is about 8GB, total 14 partitions. each partition about 50M rows. The cube was processed before so it exists with about 110GB total used space. Free space about 200GB. When re-processing the same cube, the free space seems to slowly disappear, for example only after processing 2 partitions, the free space went from 200GB to 40GB. This is while the total used space in this cube directory is 120GB. The used space makes sense - 110GB + 8G for 2nd copy of partition that's being processed. The free space gone to neverland is what doesn't make sense! I am processing partitions from BIDS (highlighted all partitions). Processing each partition separately, each with its own transaction. I've seen the same type of Disk space stealing by SSAS server when for example deleting a cube. Somehow when I delete the cube, it does not show that disk space is free, while the files are gone!, - that is until you restart SSAS server. can anyone shed a light on this (win 2003 ent, sql 2005 ent sp2 fin, 32 bit, 16gb ram, 4 cpu) Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 7
    - Problem ovewriting xp_sendmail I am trying to overwrite xp_sendmail by a transact sql stored procedure. I delete the original one and write a new one with the same name but I keep receiving errors which seems to relate to the orinal one as if the security is kept in place even though I deleted the extended procedure. Here are the details of what I did: I created a stored procedure xp_sendmail in master database. I called the procedure and receives the following error message: Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1 SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. I tried deleting the extended stored procedure with the same name from the visual interface of "Microsoft ssql server management studio". The process did not work, I received the following message: "Cannot use SP_DROPEXTENDEDPROC or DBCC DROPEXTENDEDPROC with xp_sendmail because "xp_sendmail" is a procedure. Use DROP PROCEDURE (MSSQL ERROR 3751)" I believe the interface does not do the distinction between my procedure and the orinal extended procedure. I therefore tried the following: drop the stored procedure I wrote. drop once more the extended procedure. I received a different message: "Cannot drop procedure because it does not exists or you do not have the permission (mssql error 3701)" I do refresh, I reopen the management studio, no matter, the extended procedure is still there. Only way I find to delete it from the interface is recreating from transact sql before redropping it. sp_addextendedproc 'xp_sendmail' , [the path of the dll here] Even aftter doing all those and recreating my own procedure, I still see the extended procedure reapearing in the sql server management studio interface and I still receive the security error. It is as if sql server fails to notice I dropped the procedure. I am trying all those with the sa user so I doubt the issue comes from the fact that I lack permission. Is there something that can be done to force sql server to consider my procedure as a separate one that the original one. Why is the extended procedure still appearing and why do I still receive security error after I drop it. I tried calling the procedure without with both the extended procedure and transact sql stored procedure dropped or with the extended one droped and the other one present, still I get no success and still receive the security error. I even tried freeing the dll from memory but it makes no difference: DBCC xp_sendmail ( free ) Thanks for the help. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 8
    - Setting the OLEDB connection properties at runtime I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables: DataSource Name, Name(for a WHERE clause), user and password. For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions: 1. How do I set the connection information for the source connections Do I just set the properties for the connection manager and then it sets for all the sources What property do I set--do I need a script task 2. How do I use the values from the "Name" column in a WHERE clause in each data flow Thanks! Kayda Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 9
    - Strange SMO behavior I am have the following code below on a standalone computer and it worked perfectly. Suddenly, without any significant changes to the code there were no Servers instances found on my local computer. I know there are several server instance on the computer. Why is it acting so unpredictable The same thing happened when I tried SQLDMO. // Get a list of SQL servers available on the networks DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(false); foreach (DataRow drServer in dtSQLServers.Rows) { String ServerName; ServerName = drServer["Server"].ToString(); if (drServer["Instance;] != null && drServer["Instance"].ToString().Length > 0) ServerName += " + drServer["Instance"].ToString(); if (cmbServer.Items.IndexOf(ServerName) < 0) cmbServer.Items.Add(ServerName); } Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 10
    - Conditional Split Hello Group Can somebody guide me on the prefered standards of doing this I have a Colunm in a table having both NULLS and some data Ex: Table 1 Col1 Col2 -------------------------- 1 MSDN 2 Forum 3 NULL 4 NULL 5 Condition 6 Split 7 NULL I want to move the data from this tabel to two different table depending upon the value in Col2 Table2 Col1 Col2 -------------------------- 1 MSDN 2 Forum 5 Condition 6 Split Table 3 Col1 Col2 -------------------------- 3 NULL 4 NULL 7 NULL For doing this I used a simple Conditional Split Task after table 1 First Approach Output Name; Null Data Condition: ISNULL(Col2) I routed the output Null Data to Table3 and the default to Table2. Strangely I see some data in Table3 which is not NULL. That is Table 3 is having a data which is not equal to null in Col2. I have no clue why will it do that. Second approach Output Name: Data, Condition: !(ISNULL(Col2)) I routed the output: Data to Table2 and the default to Table3. Strangely I see some data in Table3 which is not NULL. That is Table 3 is having a data which is not equal to null in Col2. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 11
    - Deploy problem; RS2005 I am not able to deploy report to report server, I have this error: The permissions granted to user <IUSR_MashineName> are insufficient for performing this operation. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 12
    - Setting expressions in dtsConfig Let's try this... Setup three variables: controlCASE, inputString, outputString. Set both to be string variables. In the config file, you'll specify controlCASE and inputString. (I realize that inputString is likely coming from a file inside your data flow, but this should better illustrate what I'm proposing.) In the expression for outputString (set EvaluateAsExpression to true), use this: Code Snippet @[User::controlCASE] == upper("UPPER") UPPER(@[User::inputString]) : LOWER(@[User::inputString] You'll want to then specify either UPPER or some other value (LOWER) in the config file to determine which function to use in the expression. Now you can use the above variable (@[User: utputString]) wherever you need it. OR, you can use the above expression in a derived column, replacing "@[User::inputString]" with the input column name. Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 13
    - need help Installing reporting services I followed the guide at http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#10 and the installation went fine, the problem is that i can't surf to my http://localhost/Reports to test my reportmanager. This is all i get when opening up that url with internet explorer: <%@ Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.HomePage" %> <%@ Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI" Assembly="ReportingServicesWebUserInterface" %> Does anyone have a clue about what might have happend My computer is running WindowsXP SP2 with iss 5.1 Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 14
    - Outputting selected multivalued parameter to textbox Hello everyone I have a multivalued parameter called param_state I am trying to output the values off what is selected to a textbox so the user can see what was selected I have entered the following expression in a text box = "Sales rep for " & Parameters!Param_State.Value I get an error for this and if I enter it as = "Sales rep for " & Parameters!Param_State.Value(0) I get only the first state. Is there a way to show what was selected thanks Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 15
    - Insert delay Hi, I am using Sql 2005 SP1 and merge replication on a database. One of the tables is used for an audit trail and has a dynamic filter applied so that it doesn't replicated every audit trail record to every subscriber. Our sp's tend to insert records in to the audit trail table when someone inserts a new product (for example). The problem is that just recently the insert of new products has been taking >2 seconds, this is relatively slow compared to how it used to be 2 months ago. Using profiler I have found that it is the insert in to the audit trail table that is taking all the time, and this is taking a long time because of something replication is doing. From profiler I have found that the following statement is the culprit. This is something that replication is doing but why it take so long I don't know: select count(*) from [dbo].[MSmerge_repl_view_000CC979122E4C88AF27FE08CDCC84EB_B5F96F71937D4D9A949DEECFE540D0C4] [AUDIT_TRAIL_DETAIL] with (rowlock) where [RowGUID] in (select [AUDIT_TRAIL_DETAIL].[RowGUID] from inserted [AUDIT_TRAIL_HEADER], [dbo].[MSmerge_repl_view_000CC979122E4C88AF27FE08CDCC84EB_B5F96F71937D4D9A949DEECFE540D0C4] [AUDIT_TRAIL_DETAIL] with (rowlock) where (AUDIT_TRAIL_HEADER.ID = AUDIT_TRAIL_DETAIL.FKAuditTrailHeaderID)) The AUDIT_TRAIL_DETAIL table currently has 1.1 million row in it. Can anyone give me any clues as to what I should do help improve the performance once again Should I stop filtering on this table Thanks for your help Graham Tag: create a "Yesterday", "This Week", "Last Week" calculated member
  - 16
    - Determining min(datecolumn) and max(datecolumn) in a subset Hi all, It's kind of hard to explain but I wll try: Is it possible without using a cursor to remember values from preceding rows in the current row I found window functionallity is not implemented in SQL 2005. What approach is then used to solve this For example: EMPLOYEE STARTDATE ENDDATE ILLNESSPERCENTAGE 1 01-01-2005 31-01-2005 80% 1 31-01-2005 05-02-2005 60% 1 05-02-2005 20-02-2005 70% 1 20-12-2006 31-12-2006 100% 1 31-12-2006 10-01-2007 80% 1 10-01-2007 15-01-2007 40% Now I would like to have a resultset which tells me the startdate and enddate for each subset for each row resulting in the following resultset. A subset is based on rows where the same employee has matching startdate and enddate. EMPLOYEE STARTDATE ENDDATE ILLNESSPERCENTAGE FIRSTDATE LASTDATE 1 01-01-2005 31-01-2005 80% 01-01-2005 20-02-2005 1 31-01-2005 05-02-2005 60% 01-01-2005 20-02-2005 1 05-02-2005 20-02-2005 70% 01-01-2005 20-02-2005 1 20-12-2006 31-12-2006 100% 20-12-2006 15-01-2007 1 31-12-2006 10-01-2007 80% 20-12-2006 15-01-2007 1 10-01-2007 15-01-2007 40% 20-12-2006 15-01-2007 Hope it's clear and someone can give me a quick start. Thanks, Marc Tag: create a "Yesterday", "This Week", "Last Week" calculated member

mmman

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:

Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]

But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function

Thanks

Greg

Re: create a "Yesterday", "This Week", "Last Week" calculated member

SteffoS

1.

Yes. In the DataSourceView add a named column called SpecialDates with the value 'Normal'. Then add that column as an attribute in the time dimension, and then create the calculated members on that dimensionattribute. Another possibility is to create a Named Set with the same functionality.

2.
i think calculated members are not returned by .MEMBERS. This will work I think

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty ADDCALCULATEDMEMBERS([DATE TIME].[Date].members) on 1
from [MIA DW]

Re: create a "Yesterday", "This Week", "Last Week" calculated member

mmman

Awesome thanks.