Change Display Of Result Set From Showing As Rows To Column As One Or More
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.
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.
i have 2 columns like Col1====col2 username=======saif====mr@hotmai.com email=======saif@hotmail.com name======saifullah I want to get these records from sql like username email name saif saif@hotmail.com saifullah ________________________________- how can I achive this
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
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE FROM SAMPLE SAMPLE, SDIDATAITEM SDI WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND (SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') ) ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100
last record : as there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID inner join ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
hi i have aques tion about mining model and time series after predicting how can i show the result of the prediction in the c# form and why i cant use the all of the prediction function in the mining model prediction for time series and how i can see the script of the building mining model in the model because i can see just the wizard of the model and how can i undrestand othe about time series formula in the chart thanks alot if answer me
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)
Hi to all, As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.
But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns. Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually? Thanks in advance
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.
The below query should produce all those customers who are in 'orginalerrorfiles' but not in 'hopefullyworks2'. 3 fields will be used as the check to make sure that the rows, that r identical won't be displayed but those that r different will be displayed.
Instead of doing that it just display everything in the table called 'orginalerrorfiles'.
Note 'orginalerrorfiles' consist of 141455 rows 'hopefullyworks2' consist of 134784 rows
select o.card_no ,o.ref_no ,o.tran_val
from OriginalErrorFiles o
where exists (select h.card_no ,h.ref_no ,h.tran_val from hopefullyworks2 h where( h.card_no = o.card_no and h.ref_no=o.ref_no and h.tran_val = o.tran_val ))
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
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...
I have just installed SQL Server 7 on a server. Everything was installed fine without any errors. The only problem occurs when I use Enterprise Manager, I right click on a table in the Northwinds database to open table and return all rows. The form pops up and no rows appear. Nothing in the table form works. I can view the records from Query Analyzer, and can also view them through Enterprise Manager when using a workstation client. This problem only occurs at the server. Any thoughts.
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
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 ???
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.
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')) .......
Hi all, I am using ASP.NET 2003 with SQL Server as database. I have a database of a book store with BookPicture, Author, Title, and Description of the Book. Now when the user searchers for a book with a keyword, how can I display the results which should show: 1. The picture of the book, 2. The at it right, Title of Book, 3. The author,4. The descritionThen the image of "Add to cart" Each search result must be separated by a box like the cell of a table. Is it possible to be done? Thanking you in advance Tomy
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
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
Is it possile to definea custom format for the 'display format' property of a measure so that the results are shown in Thousands or Millions or Billions e.g
100,000 can be shown either as 100T OR 1,000,000 can beshown as 10M OR 10,000,000 1B
Sorry if I got my numbers wrong but I hope you get the idea?
