Creating a blank row in a table between positive & negative values

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - System.Security.HostProtectionException:The protected resources (only available with full trust) we All .... here is the code:( a little bit long ... ' The record that holds each row returned by the TVF Partial Public Class ImportRecord Public DateTimeWeather As DateTime Public EffectiveTemperature As Double Sub New ( ByVal dateTimeWeatherData As DateTime, ByVal temp As Double ) Me .DateTimeWeather = dateTimeWeatherData Me .EffectiveTemperature = temp End Sub End Class Partial Public Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction(fillRowMethodName:= "FillRow" , TABLEDEFINITION:= "DATETIMEWEATHERDATA DATETIME,TemperatureEffective FLOAT" )> _ Public Shared Function MEImport( ByVal filename As String ) As IEnumerable Try Dim sfl As SingleFileLoader = New SingleFileLoader(filename) Dim ROWARRAY As ArrayList = New ArrayList() Dim cippa()() As Object = sfl.GetDataIntoArray() Return cippa Catch ex As Exception MsgBox(ex.Message) End Try End Function 'The fill row method that returns the individual columns. Private Shared Sub FillRow( ByVal obj As Object , <Out()> ByRef DateTimeWeather As DateTime, <Out()> ByRef TemperatureEffective As Double ) 'Dim row As Object() = CType(obj, Object()) If obj IsNot Nothing Then Dim row As Object () = CType (obj, Object ()) DateTimeWeather = CType (row(0), DateTime) TemperatureEffective = CType (row(1), Double ) End If End Sub End Class Private Sub SingleFileLoaderHelper() Dim OriginalContext As WindowsImpersonationContext = Nothing Try 'Impersonate the current SQL Security context Dim CallerIdentity As WindowsIdentity = SqlContext.WindowsIdentity If Not CallerIdentity Is Nothing Then OriginalContext = CallerIdentity.Impersonate() fs = New FileStream(fn, FileMode.Open) Else fs = Nothing End If Catch ex As Exception 'If file does not exist or for any problems with opening the file,set filestream to null fs = Nothing Finally 'Revert the impersonation context; note that impersonation is needed only when opening the file. SQL Server will raise an exception if the impersonation is not undone before returning from the function. If Not OriginalContext Is Nothing Then OriginalContext.Undo() End If End Try End Sub ' Public ReadOnly Property Current() Get Return (fs) End Get End Property Public Function GetDataIntoArray() As Object ()() Dim dtValues As New DataTable Dim JaggedArray()() As Object = New Object (30)() {} Try Dim myReader As New System.IO.StreamReader(fn) 'Read the entire text, and set it to a string Using myReader D im line As String Dim unit As String = "" Dim lineNumber As Integer = 0 Dim ss As Integer = 0 ' Read and display the lines from the file until the end of the file is reached. Do Dim WeatherData() As String = Nothing Dim mixedTypes(2) As Object line = myReader.ReadLine() If line IsNot Nothing Then If line.Length <> 0 Then line = line.Replace( """" , "" ) WeatherData = line.Split(","c) Dim gday As DateTime = Date .Parse(WeatherData(0).ToString).ToUniversalTime Dim effectiveTemperature As Double = GetEffectiveTemperature( CType (WeatherData(1), Double ), CType (WeatherData(2), Double )) mixedTypes(0) = gday mixedTypes(1) = effectiveTemperature JaggedArray(ss) = mixedTypes ss = ss + End If End If lineNumber = lineNumber + 1 End If Loop Until line Is Nothing myReader.Close() End Using Catch ex As Exception MsgBox(ex.Message) End Try GetDataIntoArray = JaggedArray End Function Tag: Creating a blank row in a table between positive & negative values
  - 2
    - Ranking by One Field, Displaying Multiple Fields Thank you, Deepak. Here is how it looks right now in my cube. The ORDER fn works for me, but the three calc'd fields are returning errors, and I cannot find a code discrepancy: CODE ADAPTED TO OUR CUBE: with set [LenderRankByLoanVolume] as Order ([Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity] - { [Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity].[Miscellaneous] }, [Measures].[Loan Amount], DESC ) Member [Measures].[Behind 1st $] as iif ( [Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity]. CurrentMember IS [LenderRankByLoanVolume]. Item (0), Null , ( [Measures].[Loan Amount], [LenderRankByLoanVolume]. Item (0) ) - [Measures].[Loan Amount] ), FORMAT_STRING = 'Currency' Member [Measures].[Behind 1st %] as [Measures].[Behind 1st $] / ([Measures].[Loan Amount], [LenderRankByLoanVolume]. Item (0)), FORMAT_STRING = 'Percent' Member [Measures].[Behind 1st Count] as iif ( [Lender Name].[Master - DBA - Deed Stamp].[Lender Master Entity]. CurrentMember is [LenderRankByLoanVolume]. Item (0), Null , ( [Measures].[Loan Count], [LenderRankByLoanVolume]. Item (0) ) - [Measures].[Loan Count] ), FORMAT_STRING = '#,#;(#,#)' SELECT {[Measures].[Loan Amount], [Measures].[Loan Count], [Measures].[Behind 1st $], [Measures].[Behind 1st %], [Measures].[Behind 1st Count]} ON 0, [LenderRankByLoanVolume] ON 1 FROM [Sales_Loans_Cube] ----------------------------------------------------------- Returns Results: Loan Amount Loan Count Behind 1st $ Behind 1st % Behind 1st Count Lender X $1,612,873,402 27,359 #Error #Error #Error Lender D $764,804,224 1,149 #Error #Error #Error Lender A $731,280,531 15,811 #Error #Error #Error Lender Q $708,404,260 27,105 #Error #Error #Error etc… Do you see any code discrepancy Would additional info or code help Returns Messages: Parsing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet Parsing complete Tag: Creating a blank row in a table between positive & negative values
  - 3
    - Receive "Procedure or function expects parameter..." error unexpectedly Sorry, will close this, found where I was going wrong. I knew something easy had to be missing! It was a critical little line of VBA that goes: .CommandType = adCmdStoredProc Sorry for wasting your time! Thanks. Tag: Creating a blank row in a table between positive & negative values
  - 4
    - Restore to a new database failed I did select a new name for the database. What do you mean "make sure the database file does already exist" Or did you mean "doesn't" Tag: Creating a blank row in a table between positive & negative values
  - 5
    - SSIS Execution problems Hi. Yes, the file path is correct. Tag: Creating a blank row in a table between positive & negative values
  - 6
    - "Report is being generated" Thanks for your reply. I think it is not related with SQL server query or system speed. If I take out following line of code it works superfast and spiny also appears. Me.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout) Me.ReportViewer1.ZoomMode = ZoomMode.Percent Me.ReportViewer1.ZoomPercent = 80 I am getting a dataset from the server and then assigning that dataset to RDL file in Windows application at client side so there is no SQL server involved. Spiny does not appear even after taking out Zoom mode and zoom percent. I guess something is wrong with setting DisplayMode.PrintLayout. Tag: Creating a blank row in a table between positive & negative values
  - 7
    - Ideal Event handling Technique Group you DFT into a task flow container and for that container set the event handler. Tag: Creating a blank row in a table between positive & negative values
  - 8
    - order two by two successively, any way? Thanks for the suggestion about the DIFFERENCE function. Though this function is a little bit too "rough" (inaccurate) and it appears to compare the first word of each of the strings, it is just good for this purpose. I have modified your code to the following: Code Snippet DECLARE @MyTable table ( RowID int IDENTITY, MyData varchar(30) ) INSERT INTO @MyTable VALUES ( 'Thisissentence1' ) INSERT INTO @MyTable VALUES ( 'Thatissentence2.' ) INSERT INTO @MyTable VALUES ( 'Thisissentence3.' ) INSERT INTO @MyTable VALUES ( 'Thatissentence4.' ) INSERT INTO @MyTable VALUES ( 'Thelastsentence.' ) SELECT m1.MyData as string1, m2.MyData as string2, DIFFERENCE(m1.MyData,m2.MyData) as diff FROM @MyTable m1 JOIN @MyTable m2 ON m1.RowID <> m2.RowID And the result is: string1 string2 diff ------------------------------ ------------------------------ ----------- Thatissentence2. Thisissentence1 3 Thisissentence3. Thisissentence1 4 Thatissentence4. Thisissentence1 3 Thelastsentence. Thisissentence1 2 Thisissentence1 Thatissentence2. 3 Thisissentence3. Thatissentence2. 3 Thatissentence4. Thatissentence2. 4 Thelastsentence. Thatissentence2. 3 Thisissentence1 Thisissentence3. 4 Thatissentence2. Thisissentence3. 3 Thatissentence4. Thisissentence3. 3 Thelastsentence. Thisissentence3. 2 Thisissentence1 Thatissentence4. 3 Thatissentence2. Thatissentence4. 4 Thisissentence3. Thatissentence4. 3 Thelastsentence. Thatissentence4. 3 Thisissentence1 Thelastsentence. 2 Thatissentence2. Thelastsentence. 3 Thisissentence3. Thelastsentence. 2 Thatissentence4. Thelastsentence. 3 Appearently, there have been redundant comparisions, such as those lines highlighted in green - as by that time, "Thisissentence1" and "Thisissentence3" will have already been taken out of the list for comparison. Those lines that are highlighted in yellow are meaningful. Any way to return these lines only Tag: Creating a blank row in a table between positive & negative values
  - 9
    - From SQL Server 2005 to 2000 While you cannot 'convert' the SQL 2005 database to a SQL 2000 format, you can script out the tables, views, functions, stored procedures, etc., then after checking those scripts for compatibility, you can run the scripts on SQL 2000 and re-create the database. Tag: Creating a blank row in a table between positive & negative values
  - 10
    - Retrieving a datetime with a time of midnight (from a typical datetime) Another way: Code Snippet select dateadd ( d , datediff ( d , 0 ,[PostedDate] ), 0 ) Tag: Creating a blank row in a table between positive & negative values
  - 11
    - Possible to use a Sharepoint List as a Datasource? This is good as I have this exact requirement, but get TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. Failed to execute web request for the specified URL. ------------------------------ ADDITIONAL INFORMATION: Failed to execute web request for the specified URL. (Microsoft.ReportingServices.DataExtensions) ------------------------------ < xml version="1.0" encoding="utf-8" > <soap:Envelope xmlns:soap=" http://schemas.xmlsoap.org/soap/envelope/ " xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance " xmlns:xsd=" http://www.w3.org/2001/XMLSchema "> <soap:Body> <soap:Fault> <faultcode>soap:Server</faultcode> <faultstring>Exception of type Microsoft.SharePoint.SoapServer.SoapServerException was thrown.</faultstring> <detail> <errorstring xmlns=" http://schemas.microsoft.com/sharepoint/soap/">Value cannot be null.</errorstring> </detail> </soap:Fault> </soap:Body> </soap:Envelope> ------------------------------ BUTTONS: OK ------------------------------ Any ideas Tag: Creating a blank row in a table between positive & negative values
  - 12
    - Sybase and SQLServer2005 Connection Problem Did you try specifying the port number explicitly in the odbc settings By default it takes 5000 as port. Tag: Creating a blank row in a table between positive & negative values
  - 13
    - Canned SMSS reports How does one make the canned SMSS reports such as disk usage available from 1.) Local workstation for administrators of SQL can run then on SQL instances from the Object view of Management studio. All we see is the reports tab greyed out. 2.)From a reports server. My goal is to be able to run these reports on all of the SQL servers and not just a local instance. Do those servers need anything installed It was my thought that one should be able to highlight a server / database and run these reports. Have I errorred in my believes in this Yes, I know these seems rather basic but it seems there is a right way to make this occur and a wrong way. MDSN has yet to give up this information to me. If anyone knows the links or answers kindly post in the blog and also email me at brucehelp@yahoo.com . This link covers the discussion but does not have the methods to achieve the installation of the canned reports. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx "Did you know that there is a set of pre-made reports in Management Studio that can help database administrators gather information in many common areas There are reports for CPU, memory, disk, sessions, queries, transactions, and more baked right into Management Studio. You can access these reports in SQL Server 2005 and SQL Server 2005 SP1 by following these steps: " Tag: Creating a blank row in a table between positive & negative values
  - 14
    - Complex Math functions Thank you very much clintz. Tag: Creating a blank row in a table between positive & negative values
  - 15
    - Default parameter = current year DUH! I tried [Date Shipped].[Fiscal Year].&[Year(Now)] and it didn't work.....I didn't know you had to put the thing in quotes if you wanted to use an expression. THANKS! Tag: Creating a blank row in a table between positive & negative values
  - 16
    - "XML Source" xsd relative path Hi JayH, If the package is executed in the SQL Server where should the xsd file be stored I have a Web Service Task for extracting data from a Web service . It writes the web service output in a xml file and after that this xml file is used as a source for a XML Source. Finally the data from the web service is inserted in a SQL Server database. Where in the database then, not in the file system, should I store the xml file and the xsd for it And actually this relative path question came out because the SSIS packages we develop are under source control and if they do not support relative paths it is very difficult for two people to work on the same package under source control. Best regards, pc Tag: Creating a blank row in a table between positive & negative values
- Next
  - 1
    - SQL Server 2005 rejects remote internet connections Which service pack your sql server is Tag: Creating a blank row in a table between positive & negative values
  - 2
    - DTS package this is a very good idea, thank you very much... but how would i count rows in the excel, can u maybe link some example please, or a code snippet many thanks in advance Tag: Creating a blank row in a table between positive & negative values
  - 3
    - Unable to Create New web Site in IIS 5.1 So, what is the solution If XP doesn't allow the GUI, is there anyway to do it from command prompt if there is, then what Tag: Creating a blank row in a table between positive & negative values
  - 4
    - SQL Server 2005 Installation On Windows Vista Home Premium Mike, I have the same problem. I have windows vista home premium installed on my pc but only item that is not available to select as indicated in KB Article 920201 is Windows Authentication...any other hints I installed the express edition and it works but not sql server 2005 thanks Renato Tag: Creating a blank row in a table between positive & negative values
  - 5
    - Exception from HRESULT: 0xC0048004 Hi, there; I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table. Does anybody how to resolve it Thanks Tag: Creating a blank row in a table between positive & negative values
  - 6
    - Changing destination database for SSIS Package Can you help me with the syntax for that Do I have to create an environment variable for connectionstring instead of initialcatalog I've tried this: E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;" And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get: option "Source=(local);Initial" is not valid. Tag: Creating a blank row in a table between positive & negative values
  - 7
    - I4 to I8 in Aggregation I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table. Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold. I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing. I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround Tag: Creating a blank row in a table between positive & negative values
  - 8
    - Pass variables to an OPENQUERY Thanks a lot!. This was helpful. Tag: Creating a blank row in a table between positive & negative values
  - 9
    - Order By ignoring the hyphen character Thanks, Your suggusted workaround did the trick. John Tag: Creating a blank row in a table between positive & negative values
  - 10
    - can't replicate table with column name too long (from sql server 2005 to oracle) I am not sure what you are asking You can use a custom creation script to create a table on your oracle subscriber with a shorter name. If you are interested in looking at the error you should be able to see this error in msrepl_errors. I normally use sp_repltran exectued in the publication database to see how many replicated commands have to be picked up. I use sp_replcounter in the publication database to get an idea of distribution agents health. I also query select * from distribution.dbo.msdistribution_status to check on how a breakdown of how many commands have to go to the subscriber by article. Other than that I query msdistribution_history for more info on the health of the agents. Tag: Creating a blank row in a table between positive & negative values
  - 11
    - Logical equivalent IN operator in a Conditional Split Transformation Thanks Phil, that appears to work. Tag: Creating a blank row in a table between positive & negative values
  - 12
    - SSIS package failing It is the Flat-File provider. Importing from a .csv file into a table. Thanks, Ed Tag: Creating a blank row in a table between positive & negative values
  - 13
    - MDX Dynamic parameter syntax Like I said I'm very new to SSRS and SSAS... Here is my problem. I have a SSRS report that uses a SSAS cube for as it's datasource. I have a drop down parameter box where the user can select a year (2005,2006,2007,etc..) They click the View Report button and the report is generated. On one of the textboxes properties, on the navigation tab I have Jump to Report activated. What I'm trying to do and can't, is to jump to that report passing the contents of the dropdown box. The receiving report is a SSRS report with a datasource being the SSAS. How do I make the receiving report open based on the parameter that was sent All ideas are welcome... Thanks .. Tag: Creating a blank row in a table between positive & negative values
  - 14
    - User Defined Function Help Needed are you using SQL 2000 using the getdate() in UDF will works in SQL 2005 but not in 2000 Tag: Creating a blank row in a table between positive & negative values
  - 15
    - transaction log is full Full recovery model. Tag: Creating a blank row in a table between positive & negative values
  - 16
    - Sub Query in where clause on SS2K5 I've got a problem with a specific query, which when running on the exact database, is super quick on SQL 2000, and takes forever on SQL 2005. The problem seems to arise from the concatenation done in the where statement. select * from [users] where [users].[name] + '|' + convert(nvarchar,[users].[age]) not in (select [listing].[name] + '|' + convert(nvarchar,[listing].[age]) from [listing]) This happens on all MSSQL 2005 servers I've tested it on, with or without service packs. Any ideas Tag: Creating a blank row in a table between positive & negative values

stevensun2007

I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this

Thanks,
Steven

Top

Re: Creating a blank row in a table between positive & negative values

kcon

If what you are trying do is print all the positives and then a blank row and then all the negative use a table with a conditional grouping such as =iif(Fields!myfield.value<0,1,0). The just you a blank group footer row.

Top

Re: Creating a blank row in a table between positive & negative values

stevensun2007

Thanks

Top