Hi Guys,
Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.
My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.
I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.
SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END
This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?
I need to add a textbox in the report which would display the total number of rows in the report.I need to do this in reports which have either tabular layout or a matrix layout .
Thanks in advance
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies View Related
Hi,
A very simple question but I cannot find how, please share with me if you know how to display Field Name or Field Title in Matrix at Rows ?
Thanks
best regards,
Tanipar
Hi,
I have a matrix report...the column results
are as follows
Con Std , Con Access, SF Std, SF Acc, Broadband, Pay TV
how would i make the columns appear in the above order when displaying as it is default alphetically sorted...I have tried putting numbers at the front which work till I get to the number 10 which alphetically sorted is next to 1 not 9?
is there a better way off sorting matrix column which have no specific criteria to sort from?
thanks
Hi all,
I have three textboxes in a form view
1- Qty textbox
2- price textbox
3-TotalPrice textbox which value is the result of multiplying the previous two values
and then I want to Bind() the third value to the totalprice field in the database
how can I do that??
(without making the database field as a computed column)
thanks for any help
I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.
The report is somehow treating the Month like character data instead of numeric, how do I fix this?
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.
Option 1: Presently implemented:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text)
Although ithis does what is needed it generates the following inner exception
ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} System.Data.SqlTypes.SqlDateTimeDayTicks
<error: an exception of type: {System.Data.SqlTypes.SqlNullValueException} occurred> Integer IsNull True Boolean
Option 2:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.DBNull.Value, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Option 3:
Dim dtNull As System.Data.SqlTypes.INullable
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", dtNull, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Any advice would be greatly appreciated!
Thanks
Craig
CTARP2
I have the folliwing two fields, want tp concatenate:
=Fields!sequenceno.Value & =Fields!LogType.Value
Thank you very much for the information.
I have a matrix report that has two columns, and one of the colums has the following expression for background color:
=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")
Basically if the percent is greater than 10 highlight the field, for some reason i have some fields that dont show up yellow, see below:
http://duhaas.googlepages.com/percent.Jpg
What I need to do is to calculate the Average time in the subtotals of a matrix containing max-time values.
Amount
last sold at
Item A
5
18:00
Item B
10
19:00
Total
15
18:30
In my understandig I have to create a function that counts all max-time values and calculates their average.
The return value would depend on the filed-type. If it´s a normal field, the max-time value would be returned,
else the calculated average. My first problem is to figure out, if the actual filed is a normal or a subtotal field.
Does anybody know a way to solve that?
Regards
LG
Does ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View 8 Replies View RelatedDear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View 2 Replies View RelatedHow can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?
fields!textbox1.value doesn't work..what does?
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
Thanks,
Firoz Ansari
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 Replies View RelatedHi,
How can I rearrange the fields in a table in SQL 7? If it is possible, will the existing data be lost?
Thank you for any help.
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View 13 Replies View Related
Hi there
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
Can someone help please.
Please ask if I haven't explained myself.
thanks
Dianne
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
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.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
Hi!
I dont know if i will explain this correctly, but my problem is with reporting service.
I'm supposed to Sum value in one textbox and than that sum use it in sum in other textbox.
Something like this:
Sum(Fields!Abc.Value/(Fields!dfg.Value+Sum(Fields!abc.Value)),"matrix1_RowGroup1")*100
I get error msg 'The Value expression for the textbox 'textbox49' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.'
Something like this wont work either Sum(Fields!abc.Value/ReportItems("textbox56").Value)*100
Error The Value expression for the textbox 'textbox55' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
So, pls help if you know how to reference textbox in other in body of report.
Thx.
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
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.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....
Thanks for any help.
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
View 1 Replies View Related