How To Create A Matrix Of Values From A Single Table
Aug 28, 2013
I'm a real novice user and I we use SQL Server. I have a table called TableLog that has the 4 columns. The first column is repeated x number of times for each value in the second column. I'd like to see this information put into a matrix where I could tell if there are any missing intersections. In addition, I need to insert a WHERE clause that says "BETWEEN 20090000 AND 20100000" for the first and second columns.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
numbers 2 4 2Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic. Your help is highly appreciated.
i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C e.g A B C Atul Jadhav Atuljadhav Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic
Hi, I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes. So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns So in general how do people implement these types of requirements in relational databases?
I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other
I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.
Thank you very much for your advices and I am looking forward to hearing from you shortly.
Hello. I hope to explain myself well - I want to make a matrix with two rows. Lats say my data is this: I hava a list of months and in every month I have a number of pepole and there age. How can I show this in a matrix? It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
I need to create a table with P-values (from statistical t-test).The table should have the following 3 columns: DF (degree of freedom), X, P-value. P-value can be calculated in Excel using the following function : T.DIST.RT(X,DF)
Is there a built-in function in PL/SQL to calculate the p-value ?How can I calculate it without using an Excel ?[It will be a 17M records table- 0.00<X<17.00, 1<DF<10000) ]
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
Hi, I am using a SQL back end to dynamically populate an asp.net report/page. As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable. My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query. Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause? Any help gratefully appreciated. Thanks. John
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
I'm using Reporting Services 2005. In my report I'm binding data in a matrix by grouping 3 row fields (nested within each other) and 1 column field. Now I get the subtotals alright but I also want to get percentage at row1 only rather than grouping it with either in row or in column. I've tried so many ways but am not able to do it. Can anybody help me on this or is there a way in SSRS 2005 to get over this.
Also please suggest something instantly, as I'm terribly in the need Thanks
I am sure there is a simple answer to this, but I cannot find it at the moment???
I have a simple data table in SQL which gives me Division, PL Measure and Value...
SQL Table
Division PL_Desc Result
A Total Labour Costs 10
A Total Sales (inc Machine Income) 100
B Total Labour Costs 5
B Total Sales (inc Machine Income) 100
C Total Labour Costs 9
C Total Sales (inc Machine Income) 100
I need to report on this and calculate a ratio, so I have pushed this into a Matrix Report but cannot work out how to get the ratio column to work???...
Matrix Report
???????????
Division Total Labour Costs Total Sales (inc Machine Income) New Column = Labour / Sales
I am somewhat new to SRS. I am creatnig a report containing a Matrix. I did this because there are one or more columns included in the data set. I would like to add totals to both the rows and columns.
Here is data samples of the matrix: Columns: C1, C2, C3... Rows: R1, R2, R3, ... There is a total making up the data section and looks like this (I will just use Valx for the value in the data section):
C1 C2 C3 .... R1 Val1 Val2 Val3
R2 Val4 Val5 Val6 R3 Val7... .... . . .
I would like to total each column and have a summary at the bottom (for the C1 this would contain Val1+Val4+Val7) and also like to have an extra column containing totals for each row (for the R1 this would contain Val1+Val2+Val3...).
Is there a way to do this? Am I missing something obvious?
Thank you for any help, it is very apprecaited. Eric
I have an issue in a group for a Matrix report I have created. Currently if a field in a table has null values in it, the grouping does not show in the matrix. Is there a way to force a group to show if there are Null values? I figure there must be a property to toggle on/off that would take care of this but I cannot seem to find it.
I have a matrix report, which has one row group and one column group. The value is a Count of some field coming from the dataset. So the difficulties that I'm having is that when I click on zero(i.e. 0) values from the main report, the detail report doesn't ran and it through an error message : The 'X' parameter is missing a value. When the value is positive(other than zero) then the detail report works fine.
I have a matrix which looks as follows: ----- Actuals | Budget ItemA 20 | 50 ItemB 45 | 30 ItemC 20 | 15
I would like to add a column with the difference between actuals and budget. Actuals/budget is 1 group in the matrix being the dimensionname. How can I calculate the difference (subtract actuals and budget), because if I just use the subtotal function it adds both totals together and I need to have the difference between them?
Hello Friends, I am generating a SSRS report in which I have created report using 3 matrix and each matrix is having three row groups and one column group. So in this report I am getting NULL values for few particular column groups. So i want to hide those null value. I have used expressions in the visibility option for that cell values and its hiding the values but its giving white spaces and the width o the matrix is not shrinking . Can anyone help me out in solving this issue?
The scenario is like this.
column group A B C D E F G H row group1 1 2 Null Null 7 8 10 12 row group2 2 3 null null 8 2 3 3 row group3 5 4 null null 9 3 3 3
So I want to hide these null valued cells. By using the visibility option its hiding it but for COLUMN C & D its giving blankspace and then values for other columns,its not shrinking the NULL cells.
I am using matrix to make a report. I have company name in the row group and user name in the details field. When I run the report I only see the company name once and each user of that company is displayed in a separate row. Then next company name and its users... I wanted to know if there is a way to display company name next to the user name? I know it can be done by using "table" but I can't use table because in this report there are other fields can only be done in a matrix. So, please let me know if there are any settings in matrix which I can use to display my company name next to each user of that company. Please ask me any thing if the question is not clear. Thanks a lot for the help.
Hello I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets
I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.
Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".
I am newbie to reporting services and I need some help. Could any one please let me know how to replace Null value with 0 in the data section of the Matrix. I don't want blanks to be displayed on the report, I want those to be replaced with 0's.
Does anybody knows how to reference a value inside a group in a Matrix. I know it should be possible to use a calculated field, but I can't find a way to calculate a simple percentage!
example: (The Orders Group have "Received" and "Accepted" columns and these are created Dynamically, and I want to add a calculated field (ie. "%Accepted") to the group.
Simple Formula %Accepted = "Accepted" / "Received" i.e. %Accepted = 5/10 :. (50%)
I am developing a SQL database to cover operations that were previouslyhandled in a spreadsheet, and need to create a view or procedure thatpresents data into a matrix format similar to what the users are currentlyworking with. There must be a way I can create this using Transact SQL butI cant figure it at this point. What the users want is for data to bepresented in 7 continuous columns where each column shows records for 1 dayof a week and each record is presented as a 'block'. Any tips or hints frompeople who have achieved something similar would be gratefully accepted.A sample of the format appears below. (Set with tabs. Hope thistranslates)1/2/2004 2/2/2004 3/2/2004 4/2/2004 5/2/20046/2/2004 7/2/2004Person Smith Jones GreenRoom 1A 2B 3cStart 9:00AM 8:00AM 8:00AMEnd 5:00 PM 5:00 PM 5:00 PMPerson Brown WhiteRoom 1D 1DStart 9:00AM 9:00AMEnd 5:00 PM 5:00 PMPerson GreenRoom 1MStart 9:00AMEnd 5:00 PMNotes1. As the diagram shows, there is likely to be a different number of recordsfor each day.2. It is not necessary to have the field headings appear on the left of theview.
