Getting Line Number Sorted Out In Matrix
Dec 27, 2007
Hi there,
I have a matrix report that shows a certain columns and rows. I need a row counter to show me the amount of rows in that matrix but it doesn't count properly by using the expression: =Int(RowNumber("matrix1") -1)
I actually don't have any idea on what this expression should be to fix the row count...to illustrate what I get in the report:
______________________________________________________________________________
Chkp | Row | Serial No | Call of Date |Colour| Pillar | AV | Sport | USA | SRA | TAPE |
2019 | 1 | 7006892 | 2003/02/11 | 3644 | 78222 | 3902 | 9182 | | | |
| 3 | 7000123 | 2003/02/11 | 3299 | 17283 | | 9238 | 2793 | | |
| 5 | 7302031 | 2004/01/11 | 3902 | 28932 | | 3233 | 2332 | | |
| 7 | 7273211 | 2004/02/08 | 6727 | 39232 | 8228 | 3293 | | | |
| 8 | 7382728 | 2004/11/11 | 7822 | 32342 | 3902 | | | | |
| 13 | 7342934 | 2004/12/03 | 8323 | 23422 | 2031 | 9212 | 2934 | 2934 | 2982 |
| 18 | 7329491 | 2004/13/02 | 8291 | 92423 | 7922 | 0231 | 2342 | 2355 | 2223 |
| 22 | 7328438 | 2004/12/22 | 8399 | 92311 | | 0289 | 8392 | | 3982 |
As you can see above, the row count numbers are not lining up at all, it starts at 1, then jumps 2 every count then all of a sudden jumps 5 per line...a pattern that is interesting but difficult to understand. The unique value in this matrix is that serial number but even though i group it by the serial number, it still gives me the funny Row count values.
What would be the approach to solving this?
Regards
Mike
View 3 Replies
ADVERTISEMENT
Dec 25, 2007
i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?
View 1 Replies
View Related
Feb 16, 2004
Hi,
How do you generate line numbers in Transact SQL?
Example:
In Sybase, I could use this...
select number(*), customer_name from customer_table
number(*) customer_name
----------------------------------------------------
1 Customer Name 1
2 Customer Name 2
3 Customer Name 3
Thanks,
Geoff
View 2 Replies
View Related
Sep 1, 2006
when we get something like this in QA:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
I only have 3 lines inclusing only one insert. How do I know what value or keyword is wrong from the error message above.
Thanks
View 4 Replies
View Related
Jul 22, 2007
Hello.
I have a query That look like that:
"SELECT COUNT(*),MONTH FROM TABLE GROUP BY MONTH"
The result is:
Jun 2
Feb 2
Mar 3
Apr 0
May 5
What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:
Jun 2
Feb 4
Mar 7
Apr 7
May 13
Any ideas on how to start?
View 3 Replies
View Related
Sep 7, 2006
I have a large query that I am trying to debug in query analyzer.However, the errors I get have no line number or reference to wherethey are failing. How can I find out what line in the query is causinga particular error message to appear?ThanksJohn IveySouth Pike School District
View 2 Replies
View Related
Jul 20, 2005
When you get a server error message referring to a line number in ascript, is there an easy way to jump to the correct line? It's noteasy to count down in a big script or when there is a lot of whitespace.In WordPerfect we had a linenum macro that allowed you to enter theline number and it would jump to the line using the same countingmethod as the interpreter that generated the error message. Is theresomething similar with TSQL, or another way?Kerry
View 2 Replies
View Related
Jul 22, 2007
Hello.
I have a query That look like that:
"SELECT COUNT(*),MONTH FROM TABLE GROUP BY MONTH"
The result is:
Jun 2
Feb 2
Mar 3
Apr 0
May 5
What I want to have is that every month will sum up the previous month and him self so in the end I will be able the create a graph wuth those numbers:
Jun 2
Feb 4
Mar 7
Apr 7
May 13
Any ideas on how to start?
View 1 Replies
View Related
Feb 4, 2004
i have a huge stored proc which has about 8-9 cursors which move data from a few temp tables to the final tables...and also do lots of calculations in between..
anytime there is an error in the stored proc i have an error page which looks like this
**********************
Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------
Error converting data type varchar to bigint. 139 pdiscnum 37 3429 4978 139 139 4979 50.93 189.93 4980 -189.93 0 4981 139 139 4982 -139 0 4986 23 23 4987 0 23 4988 0 23 5121 139 162 5122 -328.93 -166.93 3430 4983 89 89 4984 -89 0 5096 89 89 5097 -178 -89 5135 89 0 5144 139 139 3431 4989 89 89 4990 -89 0 5100 89 89 5101 -178 -89 3432 4991 139 139 4992 4.32 143.32 4993 -143.32 0 5102 139 139 5103 -278 -139 3433 4994 139 139 4995 50.93 189.93 4996 8.64 198.57 4997 -198.57 0 5003 0 0 5123 139 139 5124 -328.93 -189.93 3434 4998 59 59 4999 -59 0 5000 59 59 5001 -59 0 5002 0 0 5041 19 19 5042 0 19 5043 -2.39 16.61 5044 -16.61 0 5045 19 19 5046 -2.39 16.61 5047 -16.61 0 5056 0 0 5084 39 39 5085 -5.85 33.15 5086 -49.76 -16.61 5104 59 42.39 5105 .........
************************************************
a very lengthy page...the numbers are prbly due to the print stmts of the results of some calculations...
but my q is..is there any way to return the xact line number where the error occured in the stored proc instead of these infinite list of numbers...its taking pretty long time to go through the entire stored proc to isolate the error...
thanks
dinakar
View 4 Replies
View Related
Jan 24, 2006
I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderDetails table for OrderID.
I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.
OrderID LineNumber
1 - 1
1 - 2
1 - 3
2 - 1
2 - 2
3 - 1
3 - 2
I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.
Thanking you in advance.
View 3 Replies
View Related
Apr 24, 2007
Hi,
It looks like by default the subtotal number inherits the number format from the main column. Is there a way to override it?
In my case I am displaying percents with a decimal point in a column and sometimes the total comes out to 99.9% instead of 100%. I just want to round of the total to alway show 100%.
Is there a way to do it?
Thanks,
Igor
View 3 Replies
View Related
Mar 21, 2007
HI all !
I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.
At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.
I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!
BErnard Ong
View 1 Replies
View Related
Jul 17, 2007
I am creating a report that uses the Matrix control. I need to display a fixed number of columns (5). In my query, I am returning the top 5 rows of data. However, in some cases there are less than 5 rows of data returned from the dataset. Is there a way to force the number of columns displayed in the matrix control and to populate with some text (such as "n/a") if no data is available?
Thanks!
ads
View 1 Replies
View Related
Jul 27, 2007
Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using
string dsc = Application.StartupPath + "\FCDB07.sdf";
conn = new SqlCeConnection("DataSource = " + dsc);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();
cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]
conn.Close();
Any Idea on What causes this?
TIA
Trophus
View 3 Replies
View Related
Nov 23, 2007
Hey all-
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
Here is the relevent code:
private void scheduleMenu_Click(object sender, EventArgs e)
{
//connect to DB and do our scheduling magic
string message = messageBox.Text; //should rename messageBox...
string phoneNum = phoneNumBox.Text;
string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";
//SqlCeEngine eng = new SqlCeEngine(dataBase);
SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);
conn.Open();
//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";
SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);
cmdPhone.ExecuteNonQuery(); //error occures here...
messageBox.Text = "";
MessageBox.Show("Message Scheduled!");
}
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
View 3 Replies
View Related
Jun 14, 2007
Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).
In other words.....
I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.
Thanks.
View 3 Replies
View Related
Apr 24, 2007
Hi,
If there is no match in the matrix - it displays an empty cell. Is there a way to display some default number instead?
Thanks,
Igor
View 1 Replies
View Related
Dec 1, 2006
Hi,
I want set a specific port number of SQL Server(express) during installation not by Configuration Manager after install.
Is it possible?
View 1 Replies
View Related
May 8, 2008
Can you help me with SQL issue I€™m stuck on?
I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
View 10 Replies
View Related
Jun 12, 2006
hi all,
maybe this is not a serious problem, but I tried for days to come toi a solution without success.
My Problem:
I have have several flatfile sources I need to import into a sql-server 2005 DB.
It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.
I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.
How can I achieve my goal? Please help me...
Thanks in advance .. Bernd
View 11 Replies
View Related
Oct 29, 2007
hii
Everybody
In my report i have a matrix,but i want to show only 10 columns in one page and rest of the data should come in next page?
so is there anyway to control the number of columns in a matrix to be shown in the priview?
please help me with some suggestions.
its orgent
thanks in advance
Mahasweta
View 1 Replies
View Related
Feb 6, 2008
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
View 6 Replies
View Related
Jul 19, 2007
Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?
View 1 Replies
View Related
Mar 22, 2006
Hello everyone,
I'm having an issue with dynamically setting the Send Mail task's to field to decent sized recipient list(approx 15 email addys). The strange thing is that this same package works fine if I'm using a list of say 4 email addys. I have double checked everything I can think of....list is seperated by semicolons, each address looks good....and have ran script tasks to check my variables along the path of the package but so far have not found the reason. The error I get is below:
"The specified string is not in the form required for an e-mail address"
Also of note, these addresses are being populated from a table, that is then setting the To string of the send mail task via a seperate variable...that variable being populated from a foreach loop. Again, all of this works fine with a smaller recipient list. Any ideas?
The only thing I can think of right now is to run the send mail task within the foreach loop...but then I'll be sending a seperate email for each address and would like to avoid that if possible.
Thanks,
Adrian
View 6 Replies
View Related
Jul 13, 2007
Hi
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
Thanks
Anthony
View 1 Replies
View Related
Apr 13, 2008
Hi guys,
The result of my query I am not able to get it sorted by months.Here is my query & the output.
declare @tbl as table(date datetime,amt int)
insert into @tbl
select '2-jan-2008',100 union all
select '15-jan -2008',200 union all
select '20-jan -2008',500 union all
select '12-jan-2008',300 union all
select '02-feb-2008',100 union all
select '09-feb-2008',250 union all
select '03-mar-2008',500 union all
select '05-mar-2008',800
select Months,SumAmt,MaxAmt,MinAmt from
(
select datename(mm,date)as Months,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl
group by datename(mm,date)
)t
The result is
Months SumAmt MaxAmt MinAmt
------------------------------ ----------- ----------- -----------
February 350 250 100
January 1100 500 100
March 1300 800 500
I want to get it sorted month wise i.e Jan,Feb,March.But I cannot find a way
View 2 Replies
View Related
May 14, 2007
Hi,
I am a newbie so this might be a very basic question. I have a table that not sorted. So I write a query to sort the table like this
Select * from custInfo where custID <> '0' order by custID Desc
Now obviously I have a selection of the original table in desc order. Now I want to get the first custId from thid Desc selection. How do I do this.
So if the unsorted table looked like this
001 Martha
005 Steve
002 Mike
003 David
and the sorted selection looks like this
005 Steve
003 David
002 Mike
001 Martha
And then I want to select Steve's custID which is 005. how do i do this.
Thanks in advance.
View 2 Replies
View Related
Mar 17, 2006
If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?
If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?
View 8 Replies
View Related
Feb 27, 2007
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
View 1 Replies
View Related
May 4, 2012
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
View 5 Replies
View Related
Aug 31, 2007
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
View 4 Replies
View Related
Nov 28, 2003
we have a simple table
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby
Jones
Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
thanks in advance -arthur
View 3 Replies
View Related
Jul 18, 2005
I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement?
To clarify, say I have the following data:
Table1: Anglesey, Cardiff, Ceredigion
Table2: London, Dorset, Lancashire
Table3: Antrim, Armagh
Table4: Glasgow, Berwick, Edinburgh
I'd want the data retrieved from all four tables and sorted so that the data retrieved would be:
Anglesey, Antrim, Armagh, Berwick, Cardiff, Ceredigion, Dorset, Edinburgh, Glasgow, Lancashire, London
I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.
View 9 Replies
View Related