Hi,
I am trying to add spaces to a string value in a cell but when I run the report the value is trimmed.
Any ideas?
Here is the expression I am using:
=Fields!strVal.Value & " "
Thanks,
Igor
In SQL Reporting I want the user to pass a parameter to the report; the parameter is a employee number. Easy right, you write a where statement like where table_name.employee_number =@employee_number. When the user runs the report they€™re asked to type in the number.
My problem is the dB stores the employee number with leading spaces padding the number out to 9, so if the user types in 1 they get jack, but if they type in €˜ 1€™ they get the right employee. I can€™t roll it out that way
I€™d do something in the where clause so the user could just type in 1 & it would auto pad out to 9 spaces. I€™ve tried using the €˜LIKE€™ clause & I€™ve tried using concatenate but so far neither works well.
I'd appreciate any advice
Thanks -- Vince
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
Hi!
What I'd like to do is:
UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @precomputed_id_1)
WHERE table1_ID = @precomputed_id_2
Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.
I'd like to do something like (PSEUDOcode):
WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@textptr_initialised_to_point_at_target_c ell))
But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...
Any hints or pointers HUGELY appreciated... THANX
Hi,
I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.
I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?
Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.
Any help would be much appreciated. Thanks!
HI,I HAVE AN EXCEL SHEET WITH SOME DATA, I WANT TO IMPORT THAT DATA (CELLBY CELL WITH MANIPULATION) INTO THE SQL SERVER TABLES BY USING STOREDPROCEDURE(IF POSSIBLE).IF ANYBODY HAVE DONE SIMILER TYPE OF JOB OR KNOWING ABOUT IT, PLS. LETME KNOW.THANKS IN ADV.T.S.NEGI
View 4 Replies View RelatedI need help with a simple query. We have 86 entries with the City of O'Fallon in our db. How do I do this with the apostrophe in O'Fallon? Below is just to give an idea of what I want. Thanks.
SELECT *
FROM Organization
WHERE City=O'Fallon
Hello All, i am trying to create a normal ASP.NET application using VS2005. Yesterday i was adding tables.. entering data from within "View Table Data" tab, but today i am unable to do any data insertion or updating except by entering the insert or update statement by hand. When updating data of existing record or trying to add new data, it reports that "Cell is readonly!". I did not modify any settings or configuration and actually did nothing to set it to readonly! I am quite confused to be honest and writing sql statements by hand is kinda time consuming for me if i want to update a single field. What can i do to re-enable data modifications from VS2005 without reporting readonly? Thanks,Rakan
View 1 Replies View RelatedSECURITY USING CELL-SECURITY:
From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?
Hi,
I updated my db system from access to sql 2005.
But, i can not add new recors to database.
I'm receiving this error:
"""Invalid value for cell (row 3914, column 2).
The changed value in this cell was not recognized as valid.
.Net Framework Data Type: Int32
Error Message: Input str?ng was not in a correct format.
Type a value appropriate for the data type or press ESC to cancel the change."""
It can not open new id number. DataType was Auto Number in Access. Which can I select in SQL 2005?
Thank you.
hi all
im really have a problem in my project.
i have server and client side each side contain SQL Server DB.
and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.
now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).
my problem :
u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).
so i do this to know changed cell range:
when run the program saved all excel tabel into SQL server table.
and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.
and rise event to send this row to client by socket over internet to update the row in client side too.
but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.
this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)
and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)
thank u.
AL-Khateeb
Hi,
In SQL 2000 I used to be able to open a table in Enterprise Manager and make changes or Update with Query Analyser. Now when I attempt this in SQL 2005 I am told that the Cell is Read Only.
How do I update this column as I cannot find a read-only or allow updates property anywhere?
Please help,
A very frustrated user
HI All,
I changed the join type and did some modifications to the query in the query designer. Then I executed the query. Resutls are shown in query designer. But it says Cell is read only. Therefore when I change the tap from data to preview , I can not view the report.
This happens only when I use generic query builder.
Does anybody know how to change the read only option ?
Thanks
I am trying to implement row-security in SQL 2005 but i make a query to make a view
anyone can help me plz,
I have data like these in the report page:
Item_ID code_ID
xxx1 x1
xxx2 x2
xxx3 x3
xxx4 x3
xxx5 x3
xxx6 x2
I want get summary data like these:
code_ID x1 : 1 (items)
code_ID x2 : 2 (items)
code_ID x3 : 3 (items)
what should I do to get summarized data like that?
I've tried to use "CountDistinct" but the result always 6 that indicate how many of the data.
thank you.
Please how can remove spaces on a date field and a text field. THanks.
View 1 Replies View RelatedI'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]);
SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable();
DataRow dr;
//Adding the columns to the datatabledt.Columns.Add("CategoryIdIn");
dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items)
{
dr = dt.NewRow();
dr[0] = item.Cells[0].Text;
dr[1] = item.Cells[1].Text;
dt.Rows.Add(dr);
}//ForEach
da.Fill(dt);
gd_freq.DataSource = dt;
gd_freq.DataBind();
Hi!
Anyone knows how to select
only certain data from one data field?
Lets say i have this field which captures
Member Name and ID.
The ID is in parantheses "()".
I only want the Member name.
How do I select this from the table?
E.G.:
Field: Data
MName: John Doe (123)
I need to select only "John Doe".
Any help is deeply appreciated.
Thank you!!
Data
10 13 15 3 26
10 13 20 35 29
10 13 5 26 2
10 15 27 34 33
Query
Declare @num1 char(5)
Set @num1 = (SELECT ltrim(Substring(ltrim(number2), 8, CHARINDEX(' ', ltrim(number2)))) FROM Num2played)
SELECT @num1 FROM Num2played
Error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any suggestions or fix?
Thank you.
=========================================
The data above is in one colum. I would like to rearange the
numbers in order. e.g. from "10 13 15 3 26" to "3 10 13 15 26".
Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.
View 1 Replies View Related I'm looking at a system where formulas have been added into fields in a table and I need to look at the field to see what formula to use when selecting eg: eg this + this, this / this etc.Here's a basic table I have knocked up to try different things...
CREATE TABLE #HeaderOrder(
[HeaderCode] [varchar](10) NOT NULL,
[HeaderCode2] [varchar](10) NOT NULL,
[FormulaCode] [varchar](10) NOT NULL
[code]...
hi,
I have the following statement:
SET @L_SQLCMD='DECLARE TMPRecord CURSOR STATIC FOR '+
'select TOP 1 CATCODE From IMS.CATEGORY WHERE YEAR = 2007 and catcode <> ""'
exec @L_SQLCMD
The ms sql not accept the "" for !empty value is there a way to work around
Best Regards
I am trying to edit a long text cell in Enterprise Manager but when I select"<long text>" and try to delete it, I get an error saying "cannot edit thiscell".Could anyone tell me how to edit this cell?Thanks, Amanda
View 2 Replies View RelatedI have created a report that uses a fairly complex IIF statement to sum the data. That part works just fine. Now I need to format the font color so negative values show in red. Is there a way to reference the individual cell (by its textbox name maybe?) in the Expression Editor?
For example, if tb_Orders was the name of the textbox that sums the data, I'd like to write something like:
=switch(tb_Orders < 0, "Red")
I could just copy the IIF statement but I'd rather keep things simple. This way, if I need to change the formulas, I don't have to change all of the conditional formatting as well.
Is this possible?
Rob
Hi,
I'm currently working on a report which contains a number of matrix' with subtotals. Each matrix calculates a STDEVP which works just fine and the total shows the values I want.
However, I would like to change the font color of each cell based on the value compared to the total.
For example:
A 4.5
B 7.8
C 3.9
D 5.2
Total 5.7
With the values of A and C being less than the Total, I want to change their color to "Green" and the others to "Red".
I was hopeing I would be able to do something like this.
=Iif(ReportItems!matrix1_std.value < xxxxxxxxx,"Green","Red")
Where ReportItems!matrix1_std.value is the values of A-D and xxxxxxxxx would be the "Total"
Is this in some way possible??
I found many expamples for changing the color etc of the totals cell but nothing which tells me how to change the layout of a cell based on the total value.
Many thanks in advance
Rob
Hi,
I need to realize multicolumn cell in table for grouping fields, but i don't know how to do that. Help me please.
1col. 2 col. 3 col.
long text grouping field
first second third
data data data
Thank you.
HI,
Can I come to know Column number of particular cell.The way we get rownumber().
-Thanks,
Digs
In the relevant documentation on Cell Properties I read:
" Only the CELL_ORDINAL, FORMATTED_VALUE, and VALUE cell properties are required. All cell properties, intrinsic or provider-specific, are defined in the PROPERTIES schema rowset, including their data types and provider support. For more information about the PROPERTIES schema rowset, see MDSCHEMA_PROPERTIES Rowset."
So it seems that for each cube, the MDSCHEMA_PROPERTIES rowset would at least need to return CELL_ORDINAL, FORMATTED_VALUE and VALUE would need to be in the MDSCHEMA_PROPERTIES rowset.
However, when I retrieve the MDSCHEMA_PROPERTIES Rowset via XML/A, with a request like this:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="...I can't post links yet..." SOAP-ENV:encodingStyle="...I can't post links yet...">
 <SOAP-ENV:Body>
 <Discover xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="...I can't post links yet...">
  <RequestType>MDSCHEMA_PROPERTIES</RequestType>
[Code] ...
I only get a list of properties that apply to the levels. CELL_ORDINAL, FORMATTED_VALUE and VALUE are not there.
The rowset columns for PROPERTY_TYPE is always either 1 or 5 (which according to MDSCHEMA_PROPERTIES documentation indicates these are all member properties, not cell properties)
DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_UNIQUE_NAME are always filled, also indicating that these are member properties, not cell properties.
So the question is: Why don't I see any cell properties in the rowset?Is the documentation on Cell properties I cited wrong? Is there another way to retrieve a list or rowset of available cell properties via XML/A?
Hi,
I have the following problem, i want the number format to appear depending on the language that the IE is on once you are using the reports on the web.
The thing is that my reports have language = default.
Format of the numbers are N0 or ###,###,###
The result is
123,234,456 with pt language ou en-us language, and i would like to be 123.234.456 in pt and 123,234,456 on en.
What am i doing wrong?
Thank you
Hi,
A few months back i saw a sample rds file where there was a kind of line graph in a table cell
that indicated how much percent a sales person had to the complete sale figure.
But i cannot find the link anymore.
I would like to have such a linge graph next to all my agents that indicates a value againts a total value.
Does anybody has any pointers where i could find that info ?
Greetings
Vinnie
I have been digging a little more into Analysis Services and how it stores Writeback data.
The way i see it, everytime a Analysis Services shall writeback data it does a cube UPDATE cell where it takes the difference between the original value and the value that should be stored. The differrence is then stored in the writeback table.
When lets say 1000 is inserted in a new cell, that has no original value, there is nothing to make a difference to. Therefore the stored value will be 1000 in the writeback table.
The problem comes if a user will store Zero in a new cell (Let's say people should register absence from work and types zero for a cetain day). Since there is nothing to calculate a difference from, Analysis Services then decides to ignore that value.
My question is if someone knows a workaround for this or if it's something that Microsoft will solve in a SP