Display A Group In A Report That Has No Data Rows In The Result Set
Dec 18, 2007
Hello
I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1)status2 0 $0
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.
Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.
As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic.
Thanks
Stewart
View 1 Replies
ADVERTISEMENT
May 13, 2014
I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.
View 2 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Sep 23, 1999
How to get result from Query by group of (n) rows ?
- Is cursors the better way (and the only) ?
-Is there something as ROWNUM in Oracle or LIMIT in MySql ?
Example plz !
Thanks.
Alex
View 3 Replies
View Related
May 8, 2007
I have a report that is built using the report builder and the report model that was created.
With the fields that are displayed in the report, it could be possible for the database to have multiple rows with the same values for all the fields (that are displayed in the report). The PK will be different, but this is not displayed in the report as the PK won't make sense in the report.
So what happens is that the report displays just 1 record even though the database has multiple records because all the fields (that are displayed in the report) are identical. Is there a way to make the report display all the rows irrespective of duplicates?
View 1 Replies
View Related
Jun 7, 2007
I have a report that displays many duplicate rows where all fields in several rows are exact duplicates. I would like to know how to make it so that the entire row does not show up if it is an exact duplicate of another row. Any ideas? Thanks.
View 8 Replies
View Related
Jan 12, 2007
I have a dataset that when run returns 270 rows. The table using the dataset in the report only prints the first row. I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working. For now I just need the report to print all the returned rows. Help!!
Thanks!
Terry
View 1 Replies
View Related
Feb 5, 2008
Hi guys n gals !
I am having a few problems manipulating the results of my data reader,To gather the data I need my code is:
// database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]);
// sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open();
// execute SqlDataReader dr = rs.ExecuteReader();
// send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind();
Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is:
// prevent html public string StripHtml(string data) { // grab the data string theData = data;
// replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; }
But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
View 2 Replies
View Related
Jan 31, 2008
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection
parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))
RunDB2Sp("FABLE.MAP", parameter, dsData)
If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")
End If
Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()
End Try
End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()
CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Thanks
View 3 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Mar 11, 2008
This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1
Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:
As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:
http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon
I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.
Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter
'Consider using Me._adapter that is used already
CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()
'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then
MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If
Try
Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try
'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Dim rows As Integer
rows = 0
Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)
CN.Open()
rows = CMD.ExecuteNonQuery
If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Thanks for reviewing my code.
Technozoide
View 1 Replies
View Related
Dec 14, 2007
hai iam new to ssrs, please help me.
i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at
Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.
thanks to advanced
Jacks v
View 1 Replies
View Related
Aug 9, 2007
Hello,
I am fairly new to SQL 2005 and Reporting Services.
We are trying to create a report that will display sales data based on group membership from Active Directory.
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.
Please let me know if anyone has a good way to tackle this.
Thanks,
Justin
View 6 Replies
View Related
Mar 14, 2008
I have a table with only one column as
Emp_Name
A
B
C
D
E
I want to display this data as
Emp_Name
A, B, C, D, E
Is this possible? I am using SQL server 2005.
Any help is appreciated.
Regards,
Vinesh
View 2 Replies
View Related
Nov 3, 2015
My stored procedure give me top 3 activities. My SSRS report design, I am able to display first activity when I select the Activity field from the Stored procedure, how to display 3 activities for each category.. Also I need a solution to sort the categories based on the requirement below.
View 2 Replies
View Related
Jul 2, 2007
Hi Folks,
I am stuck in forming a query.
My age wise employee count sample data (department wise) is as shown below.
Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7
I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.
Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.
Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36
Thanking you in anticipation.
Jabez.
View 4 Replies
View Related
Jun 7, 2007
Hello,
I have the following situation :
I have a reportmodel where i have the a datetime value (dd/mm/yyyy hh:mms), a salespersonid , a salespersondescription, and an amountsold.
i would like to display a report where i can see per hour (start worktime is 8 am and end is 8 pm) which salesperson sells the most. the objectif of this report is to see in what period of the day the sales are highest.
Can anybody provide me some tips how to accomplish this.
Vinnie
View 1 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
May 28, 2015
I have sales data in SSAS cube and mapping data in RDBMS table. I want to apply join to get result into SSRS report.
Here we should get data of yesterday from time dimension of cube.
Time is in [Time].[FiscalYearHierarchy].[Fiscal Day].&[2015-05-28T00:00:00] format.
View 4 Replies
View Related
May 28, 2015
We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.
Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department
If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data
--===== Create the test table with
SET ANSI_NULLS ON
[Code] .....
View 3 Replies
View Related
Feb 15, 2006
I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query.
I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired
/* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
/* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
View 2 Replies
View Related
Apr 8, 2008
Hi all
If we don't specify any Order by clause, what is the default order of displaying the data .
Will SQL Server show the data in a consistent order?
Thanks.
View 6 Replies
View Related
Feb 7, 2006
I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
eg.
component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan
C58B0BDD tape drive 2 3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year component_id component_description total_qty_used
2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
View 9 Replies
View Related
Mar 27, 2008
Hi,
I have a query to run, but the data in the tables are stored horizontally. I want the query to output the result vertically.
e.g. if row 1 contains the following data:
custA,3-april2008,mango's,123,456,78,10
Then i want it to output as follows:
custA,3-april2008,mango's,123
custA,3-april2008,mango's,456
custA,3-april2008,mango's,78
custA,3-april2008,mango's,10
hope I'm clear, and would appreciate if someone could help me.
Thanks
View 3 Replies
View Related
Jun 17, 2014
I have an issue while display the result in the required order. How to get the required output.
Code :
USE tempdb
GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,
[Code] ....
Present output :
fileExtension FormatID Description fileExtension versionFormatTypeId
txt 1 Text txt 1
html 2 HTML html 1
xml 3 XML xml 1
pdf 4 PDF pdf 1
xls 5 Excel xls 1
doc 6 Word doc 1
Required output:
fileExtensionFormatIDDescriptionfileExtensionversionFormatTypeId
html2HTMLhtml1
xls5Excelxls1
doc6Worddoc1
pdf4PDFpdf1
txt1Texttxt1
xml3XMLxml1
View 5 Replies
View Related
Mar 21, 2007
Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.
Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'
Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918
Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...
View 7 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
Nov 6, 2007
Question: I have searched here and on Microsofts site already but it seems that all solutions require already either some ASP2 knowledge or MSSQL knowledge .. I am quite new in both but need to realise this for a project.
I have installed a MSSQL 2005 server running MSSQL2005 Standard in mixed authentication mode. Services running using a domain account created for this purpose.
I have then created a simple database called test with a table called testtable
All I need to achieve now is a simple ASP2 page, which is located on the DC IIS, which connects to the SQL server (in same domain but different server) using WINDOWS authentication and returns the result of
SELECT * FROM TESTTABLE
I try now for a couple of days so I hope someone could help me with this .. I really need a step by step guide what I need to do on the SQL server side (so a specific user can connect to this particular database) and on the IIS side ..
I know it is always painful to help someone with little knowledge but I am getting desperate.
Thanks a lot guys
View 1 Replies
View Related
Mar 26, 2004
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??
SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE
WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......
View 1 Replies
View Related
Mar 29, 2007
hi
i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field.
a sample of the database looks something like this:
ID date_value data1 24 march info11 25 march info2
my statement looks like this:select max(date_value), data from table_name where data is not null group by id
this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
what am i doing wrong?
TIA!
View 1 Replies
View Related
Mar 31, 2008
I've got a report linking to a SSAS cube. It has 2 row groups Region and Country. The Country group is a subset of the Region group and works fine, it collapses and expands ok.
Now, due to a boss who doesn't like the way this looks, I have to find another way to display it. I was wondering if it is possible to have a report parameter that could be selected and the appropriate column grouping be displayed depending on the value.
i.e. User selects Report Parameter value 'Region' and the Region row group becomes visible, the Country group becomes invisible. The exact opposite happening if the user selects Country.
I can't find any obvious way to do this. Any ideas?
Rob
View 1 Replies
View Related
May 30, 2008
Hi, how are you? I have a table which contains the following columns:
SFINet SFICom SFUbit SFUroot
------ ------ ------ -------
1 ________ 2 ______3 _______4
is there any way to group by the first 3 letters:
SFI: 3 (sum of two columns: SFINet and SFICom)
SFU: 7 (sum of two columnts: SFUbit and SFUroot)
Thank you I appreciate your help!
View 3 Replies
View Related
Apr 30, 2015
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â
View 3 Replies
View Related
