I am developing a SQL database to cover operations that were previously
handled in a spreadsheet, and need to create a view or procedure that
presents data into a matrix format similar to what the users are currently
working with. There must be a way I can create this using Transact SQL but
I cant figure it at this point. What the users want is for data to be
presented in 7 continuous columns where each column shows records for 1 day
of a week and each record is presented as a 'block'. Any tips or hints from
people who have achieved something similar would be gratefully accepted.
A sample of the format appears below. (Set with tabs. Hope this
translates)
1/2/2004 2/2/2004 3/2/2004 4/2/2004 5/2/2004
6/2/2004 7/2/2004
Person Smith Jones Green
Room 1A 2B 3c
Start 9:00AM 8:00AM 8:00AM
End 5:00 PM 5:00 PM 5:00 PM
Person Brown White
Room 1D 1D
Start 9:00AM 9:00AM
End 5:00 PM 5:00 PM
Person Green
Room 1M
Start 9:00AM
End 5:00 PM
Notes
1. As the diagram shows, there is likely to be a different number of records
for each day.
2. It is not necessary to have the field headings appear on the left of the
view.
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 have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :) Thanks
ORIGINAL DATA EventID StartDate EndDate 1 1/1/08 1/1/08 2 20/1/08 22/1/08
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 used SSRS 2005 to create a matrix and need to add a % of variance column. Is this possible? I hope I'm missing something simple. Basically the report should look like this:
Item CurVol PriorVol % of Var CurSales PriorSales % of Var
abc 100 90 11% 1250 990 26%
cde 96 128 -25% 192 243 -21%
Dataset looks this this:
Select item, vol, sales, 'Current' AS Per
FROM Table WHERE (invoice_date >= @curStartDate) AND (invoice_date < @curEndDate + 1) UNION
Select item, vol, sales, 'Prior' AS Per
FROM Table WHERE (invoice_date >= @priorStartDate) AND (invoice_date < @priorEndDate + 1)
The column group on the matrix is using the Per column because the accounting periods cross over calendar months.
i'm trying to create a drill down matrix in report designer because the Chat System, i've a char in the upper region of the RDL and i want to put in the lower region a Matrix with drill down capabilities, but i don't know how to do it.
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006
and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas, Thanks in advance, Aldo.
Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?
I need to find a way of encrypting and decrypting data from SQL server 2000. I need to do this as transparently as possible, which is why I need to do this if at all possible before my web application encounters the data.I know this is possible without 3rd party applications using SQL server 2005, as I have a working implementation already; however I need to do this with SQL server 2000 as upgrading is not an option. Using a 3rd party product to encrypt/decrypt is also not possible.Any help much appreciated.Matt Rose
But while I use matrix to build this report, I got the result like:
M1
M2
Project1
Info1
Info3
Project2
Info4
Info5
Project3
Info6
The report will not show the multiple records on the row group "Project" like item "Info2".
After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:
M1
M2
Project1
RowNumer
Info1
RowNumer
Info1
RowNumer
Info3
Project2
RowNumer
Info4
RowNumer
Info5
Project3
RowNumer
Info6
It would be much appreciated if anyone could give me some hints to complete the report I need.
I have three coloumn in Salary Table Emp ID, Emp Salary , Sequence a1 1000 1 a1 2000 2 a1 2000 3 a2 4000 1 a2 5000 2 a2 5000 3 a2 6000 4
Now I have to calculate the count on salary if the previous salary is different then count +1 else is previous salary same then add +0. so output be EmpID and Updation in Salary a1 = 2 and for a2 =3
Can anyone help me with the query or storeprocedure i can achieve this output counting according to previous data.
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
I have a stored procedure that give the (sample) data as below.
Country Category State/Province Year Month quantity Canada cat1 AB 2008 1 10 Canada cat2 BC 2008 2 10 Canada cat3 AB 2008 3 10 Canada cat4 SK 2008 1 10 US cat1 BB 2008 1 20 US cat3 GJ 2008 2 15 US cat4 CO 2008 2 15
When I configure the matrix I EXPECT TO SEE THE FOLLOWING:
AB BC SK 2008 2008 2008 1 3 2 1 ________________________________________________________________________
Canada cat1 10 cat2 10 cat3 10
cat4 10
BB GJ CO 2008 2008 2008 1 2 2 ________________________________________________________________________
US cat1 20 cat3 15 cat4 15
BUT I AM GETTING THE FOLLOWING RESULT:
AB BC SK BB GJ CO 2008 2008 2008 2008 2008 2008 1 3 2 1 1 2 ________________________________________________________________________
Canada cat1 10 cat2 10 cat3 10
cat4 10
BB GJ CO AB BC SK 2008 2008 2008 2008 2008 2008 1 2 2 1 3 2 1 ________________________________________________________________________
US cat1 20 cat3 15 cat4 15
In Crystal it automatically displays as above. How do I do this in SRS? Matrix is not showing the state relevant to the country properly. It is showing all the states.
I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this.. EXEC sp_executesql @Statement,N'@param1 varchar(3),@param2 varchar(1)',@param1,@param2,GO As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement". DECLARE @Statement varbinary(16)SELECT @Statement = TEXTPTR(Statement)FROM table1 READTEXT table1.statement @Statement 0 16566 So far so good, the issue is how to convert @Statment varbinary to nText to get it passed in sp_executesql. Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called. I would appreciate if any body respond to this.
I need to know how to add in the subtotal and the grandtotal. Im using a matrix and SSRS 2000.
Besides that, is that a way to add in the header name for the ROW group in a matrix? Im currently using a normal table, aligned to the same size as the matrix, above the matrix and display the header. Is there a better way?
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing, ( (Fields!Amount.Value) /Previous(sum(Fields!Amount.Value)) ) )
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category") should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
I am using SQL Server 2005. And the Two Servers are Linked Servers
What Script can i use to accomplish this task. Thanks
I have a matrix which lets you drill-down by company divisions and then branches within those divisions, and the data columns show how many employees are required for each divisionranch, and how many they actually have. For example, Division 1 might have 9 / 9 for Actual / Required, but when you drill it down to Branches A and B, they may have 5 / 4 and 4 / 5 respectively. Therefore Branch A has surplus employees and Branch B has a shortfall, but at the Divisional level it looks like there are the correct number of employees.
Naturaly my customer is a bit concerned that this could be misleading data. Is there any way that I can check at the Divisional level (i.e. collapsed) whether a Branch at the expanded level has a surplus or shortage of employees? I'm not sure whether this could be resolved by redesigning the report or if it's even possible. Any ideas would be great!
I have a matrix within a list and would like to display a field value in the page footer on each page. In the page footer, if I reference a textbox from the matrix the value is always blank? I realize this is most likely a scoping issue but how can I accomplish this?
My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
Regards Praveen John +91-9895074288 "Frankly, my dear, I don’t give a damn"
Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:
Region Country City $ales
North America
USA
Chicago 4 MM
LA 10 MM
NYK 6 MM
Canada
Toronto 4 MM
while the matrix would display as:
North America USA Chicago 4MM
LA
NYK
Canada Toronto ...
Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?
