Raster Catalog: column

Showing posts with label column. Show all posts

Friday, March 30, 2012

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@.DocHandle, '/XMLDATA/COLUMNS/Column',3)

WITH (Header varchar(50) '@.name',

Val varchar(50) 'text()')

EXEC sp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

sql

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXECsp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT*

FROMOPENXML(@.DocHandle,'/XMLDATA/COLUMNS/Column',3)

WITH(Header varchar(50)'@.name',

Val varchar(50)'text()')

EXECsp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

Reading a flat file

I get the following error when reading a flat file : [Credit Information 1 [1]] Error: Data conversion failed. The data conversion for column "AccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I did check all the mappings, and everything seems to be fine, the field is read in as a string. I also check for any strange characters that can possibly cause this error but the value of the field only contains a person's name and spaces at the end.

Does anyone have any ideas what might be the cause of the error?What is the source data type of the AccountName field?

What is the data type of the mapped field in SSIS?

What code page are you working with? 1252?sql

Wednesday, March 28, 2012

Read xml from sql server column(xml data type) and return as XmlDocument in C#

Hi Everyone:

I would appreciate it if someone can help me this problem as I am a c# beginner. I am writing a C# method that is supposed to read in XML from SQL Server 2005 Database. The table I am reading from has a column which is set to XML data type and has a well formed XML document. I would like to load that xml into a xml document in my c# and return back as a xmldocument in my method. Here is my method so far, as you can see i am utilizing the Enterprise Library Data Access Block for db access. I woud appreciate if you can provide me with some help. Thanks.

Note: What i am doing right now maybe completely off track from what I am trying to achieve. here is the code I have so far:

private const string SQLGETCNFS = "SELECT EntityDefinitionXML FROM EntityDefinition WHERE EntityID = ";

/// <summary>
/// Method retrieves XML Entity Definition from the configuration database
/// </summary>
/// <param name="entityID"></param>
/// <returns>XmlDocument Type</returns>
public XmlDocument GetCustomerCNSFData(string entityID)
{
//connect to config database
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = SQLGETCNFS + entityID.ToString();

IDataReader reader = db.ExecuteReader(CommandType.Text, sqlCommand);

while (reader.Read())
{
SqlXml sx = reader.GetSqlXml(1);
XmlReader xr = sx.CreateReader();
xr.Read();
}
return null;
}

You can create a new instance of XmlDocument and then call the Load method and pass the XmlReader that you created from SqlXml.

Regards,

Galex Yen

Read XML > 8k in sql TEXT column?

Hi,
I have a SQL2k database that holds XML in a TEXT column because it is
greater than 8k, I need to extract the value from a couple of fields in the
XML. How can I do this? Examples would be great; I'm new to world of XML.
BTW I'm don't have control of the database design so I can't change the
structure of the db to hold the data in a more senisble way.
Thanks,
Paul.Hello Paul,
Thank you for posting.
Regarding on the read XML data from multiple columns in SQLServer database,
are you using ADO.NET components to access the database table? Based on my
understanding, if we can make sure the content order of those columns in
the database table, we can just use ADO.NET datareader or dataadapter to
query the records out, and then combine the text in those columns together
to construct a complete text stream.
Please let me know if you have any detailed question or concerns here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Steven,
Thanks for the reply, I'm not using ADO.NET.
All the data is in a single SQL2k Text column but it is greater than 8k in
size. What I'm looking to do get data out of a couple of elements and write
them back to two sql columns using a stored procedure.
The basic issue is because I can't delclare a variable of type text in the
procedure. How can I use the sp_xml_preparedocument and OPENXML commands in
a
procedure with a large text column with more than 8k of XML.
Thanks,
Paul.
"Steven Cheng[MSFT]" wrote:

> Hello Paul,
> Thank you for posting.
> Regarding on the read XML data from multiple columns in SQLServer database
,
> are you using ADO.NET components to access the database table? Based on my
> understanding, if we can make sure the content order of those columns in
> the database table, we can just use ADO.NET datareader or dataadapter to
> query the records out, and then combine the text in those columns together
> to construct a complete text stream.
> Please let me know if you have any detailed question or concerns here.
> Regards,
>
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Thanks for your response Paul,
So you're going to done the multiple text column string concatenate at
server-side through T-SQL. Based on my research, I'm afraid this is not
supported in SQL 2000 since the datatype are limited to varchar or ntext
which has 8000 limitation. And we can not have local variable that have
larger value return from concatenate of such columns. So we may consider
done it at upstream level(in data access component, ADO or ADO.NET).
BTW, if it is possible to upgrate to SQL 2005, there has built-in sql xml
type and CLR code supported which may help resolve such issue.
Sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 26, 2012

read only cell

I try to insert a value to a column and it gives me, "the cell is read
only" and won't allow me to type in the value. I am using MS SQL 2005. Can
anyone please tell me how to turn off the read only property? Thanks.I figured out that the column identity value is to true. How do I turn it to
false? It won't allow me to do it in SQL Server Management Studio. Thanks.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>I try to insert a value to a column and it gives me, "the cell is read
>only" and won't allow me to type in the value. I am using MS SQL 2005. Can
>anyone please tell me how to turn off the read only property? Thanks.
>|||No sweat. I got it. Sorry.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I figured out that the column identity value is to true. How do I turn it
>to false? It won't allow me to do it in SQL Server Management Studio.
>Thanks.
>
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005. Can
>>anyone please tell me how to turn off the read only property? Thanks.
>|||" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:%23zgzYx3mHHA.596@.TK2MSFTNGP06.phx.gbl...
> No sweat. I got it. Sorry.
>
Hmm, I'm a little concerned.
Generally editing data using SMS isn't that great of an idea.
And turning off an Identity column is also generally a bad idea.
You may want to think about how you're doing whatever you're doing.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I figured out that the column identity value is to true. How do I turn it
>>to false? It won't allow me to do it in SQL Server Management Studio.
>>Thanks.
>>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005.
>>Can anyone please tell me how to turn off the read only property? Thanks.
>>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for your concern. The identity field is wrong in the first place. So
don't worry.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23F%23MEu5mHHA.1388@.TK2MSFTNGP05.phx.gbl...
>" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>news:%23zgzYx3mHHA.596@.TK2MSFTNGP06.phx.gbl...
>> No sweat. I got it. Sorry.
> Hmm, I'm a little concerned.
> Generally editing data using SMS isn't that great of an idea.
> And turning off an Identity column is also generally a bad idea.
> You may want to think about how you're doing whatever you're doing.
>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:uaMf2m3mHHA.4772@.TK2MSFTNGP05.phx.gbl...
>>I figured out that the column identity value is to true. How do I turn it
>>to false? It won't allow me to do it in SQL Server Management Studio.
>>Thanks.
>>
>> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
>> news:exl6Eg3mHHA.4552@.TK2MSFTNGP05.phx.gbl...
>>I try to insert a value to a column and it gives me, "the cell is read
>>only" and won't allow me to type in the value. I am using MS SQL 2005.
>>Can anyone please tell me how to turn off the read only property?
>>Thanks.
>>
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>sql

Wednesday, March 21, 2012

Read and write a Constraint or Default Value

Okay, maybe I'm getting ahead of myself.

Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.

I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

Tables have Check Constraints, Columns do not.

Open up the Master database and take a look inside.

You are looking for the INFORMATION_SCHEMA views.

In particular, you want this one: INFORMATION_SCHEMA.CHECK_CONSTRAINTS and INFORMATION_SCHEMA.COLUMNS

|||

If I open dbo.tbl1 in my database and right click to modify, I can put a Default Value or Binding of 1000000.00(or whatever) in a column I've named "Gen_ourlim". When I do that, there is, in the folder "Constraints" under dbo.tbl1, something created called DF_tbl1_Gen_ourlim. When I drop that Default Value from column "Gen_ourlim" the DF_tbl1_Gen_ourlim goes away in the Constraint folder. That's why I called the column having a constraint I suppose. In any case, how can I read that Default Value and write it to a web page?

|||

Did you query INFORMATION_SCHEMA.COLUMNS?

The default value is a column in that view, as is the catalog name, the schema name, the table name and the column name. Plus lots of other goodies about a column.

Querying data and placing it on a web page is an entirely different discussion and belongs in one of the web forums.

|||

Thanks David,

Got it. At least I was able to query it:

USE <path to database>

SELECT Column_Name, Column_Default

frominformation_schema.columnswhere table_name='tbl1'and Column_name='Gen_ourlim'

|||

Then you need to mark this thread as resolved, mark answers as appropriate, and start a new thread in a nore appropriate forum on how to put data on a web page.

(But the internet is chock full of how-to articles on that, and any beginner asp.net book will tell you how also. Best to start doing it and ask specific questions when you get stuck.)

|||

Will do David. Thanks again. Using the query in VWD is the easy part. Put the Select in a stored procedure in your Database Explorer as such:

ALTER PROCEDURE dbo.GetDefaultValue

AS
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME ='tbl1') AND (COLUMN_NAME = 'Gen_ourlim')
RETURN

Put a SqlDataSource on your page along with a DataView and then configure it to use the stored procedure as the Select in the SqlDataSource. There's a nice little video tutorial on doing this at

http://www.asp.net/learn/sql-videos/video-114.aspx

sql

Tuesday, March 20, 2012

Re: How to encrypt a column in SQL Server

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.To make the control show asterisks, set its password attribute.

To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.

-PatP|||we did this one for her already

http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.

I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!

-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.

-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.

Okay?

Re: How do you encrypt a password column?

Hi all,
How do you encrypt a password column in SQL Server 2000 table?
I would like to store something like *** in the column.
Thanks much!do you submit the same question every couple of weeks to drive me insane?|||I'd check here (http://www.dbforums.com/showthread.php?t=1099654), or here (http://www.dbforums.com/showthread.php?t=1095162) for a couple of answers.

-PatP|||I beleive it's an agent that wakes up once a week...|||Try this:

create function EncryptPassword(@.PasswordString varchar(20))
returns char(10)
as
begin
declare @.NewPassword varchar(20)
set @.NewPassword = isnull(nullif(@.PasswordString, @.PasswordString), '**********')
return @.NewPassword
end|||Here is an even stronger algorithm. It uses a single-pass one-way encryption hash that is very secure. It is impossible to decrypt. As far as I know, this method has never been cracked:

drop function EncryptPassword
go
create function EncryptPassword(@.PasswordString varchar(20))
returns char(10)
as
begin
declare @.ASCIICounter as integer
set @.PasswordString = upper(@.PasswordString)
set @.ASCIICounter = ascii('A')

while @.ASCIICounter <= ascii('Z')
begin
set @.PasswordString = replace(@.PasswordString, char(@.ASCIICounter), '*')
set @.ASCIICounter = @.ASCIICounter + 1
end

return @.PasswordString
end|||Hi,

To those who replied with help I appreciated always.
To those who replied with criticisms don't reply at all. Who are you to judge other people. Either help or don't.
Thanks!|||Now look here. I posted some help when you asked this question 2 times ago. . In fact several fine solutions were suggested to your problem each time you posted this.

I like helping people, but it gets a little fustrating answering the same question from the same person all of the time.

If you do not understand the answers, say so. We will explain further but don't keep posting the same question. It's annoying.|||You may have seen the post more than once someone may not.
The possible solutions may not be the one One is seeking for.
Its probably annoying or frustrating what you think is the problem. No one should be publicly humiliated.|||YOU (alicejwz) need to quit posting the same d@.mn question over and over again.

Very knowledgeable, very intelligent, very patient, and very generous people have already given you the ONLY ANSWER YOU WILL GET.

SEVERAL TIMES!

And frankly, it is INSULTING that you just keep posting the same question hoping somebody new will give you a better answer.

You think the people on this forum aren't smart enough to know the answer to such a simple question? Then go find some other forum to pester.

Bleh!|||Hi,

To those who replied with help I appreciated always.
To those who replied with criticisms don't reply at all. Who are you to judge other people. Either help or don't.
Thanks!

Ummm...do you know the difference?

And lighten up Francis|||YOU (alicejwz) need to quit posting the same d@.mn question over and over again.

Very knowledgeable, very intelligent, very patient, and very generous people have already given you the ONLY ANSWER YOU WILL GET.

SEVERAL TIMES!

And frankly, it is INSULTING that you just keep posting the same question hoping somebody new will give you a better answer.

You think the people on this forum aren't smart enough to know the answer to such a simple question? Then go find some other forum to pester.

Bleh!

Yo dude...who replied with help?

EDIT: And where's Karolyn Been?

I think that's her login|||You may have seen the post more than once someone may not.
The possible solutions may not be the one One is seeking for.
Its probably annoying or frustrating what you think is the problem. No one should be publicly humiliated.If you just want to bring the thread back to the top of the list, then "bump" it. Add a new posting at the end and simply comment that you are still looking for another answer, or even just the word "bump" by itself. That way the folks who've already responded will easily recognize both that they've already answered the question, and that you weren't satisfied with the answers you've gotten so far.

The people who reply to questions are a scarce resource... Well over 90% of the people that visit will never post, and another 9% will only post questions. The remainder is all that we've got to provide answers, so anything that irritates them is a danger to the forum itself. I'm not trying to tell you to bow and scrape to the posters, but please consider that they volunteer to help with problems... If you want that resource available quickly, easily, and for free, you need to husband it carefully!

Nobody is trying to humiliate you (yet), you'll know much more clearly when that happens! The repeated posting of the same question is like repeatedly asking the same question at a party... It is often ignored the first time it is repeated, sets people to whispering after that, and gets a rather stern lecture if it goes on too long. Right now you're well into the whispering stage with this question.

-PatP|||The possible solutions may not be the one One is seeking for.

I posted something like that on the Pagan forum! (It seemed to be the right place for it.)

:)|||I posted something like that on the Pagan forum! (It seemed to be the right place for it.)

:)

Do you mean the Oracle forum?|||If you just want to bring the thread back to the top of the list, then "bump" it. Add a new posting at the end and simply comment that you are still looking for another answer, or even just the word "bump" by itself. That way the folks who've already responded will easily recognize both that they've already answered the question, and that you weren't satisfied with the answers you've gotten so far.be very careful when doing this

if you capriciously just "bump" for no stated reason, your bumping post might be deleted by a moderator

in other words, your thread will be left alone, but the last "bump" will be removed|||be very careful when doing this

if you capriciously just "bump" for no stated reason, your bumping post might be deleted by a moderator

in other words, your thread will be left alone, but the last "bump" will be removed

Why is this?

You got a rule book?|||'cause Sheriff Rudy says so, that's why.

Though up there I guess they call them "Mounties".

Re: How can you encrypt a column in SQL Server?

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.

-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.

this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.

just an alternative.

Re: help please

Dear all,

I have 3 column: date, time, stocks name, price

I have 2 questions:

1. what is the command (or query languange) to get the
the first and/or last observations for any given day (i know it can be done in
aggregate query, in Ms acces but can it be done in SQL server query as well?)?
e.g. I want to get the first and last price of the day for any particular stocks

2. how to calculate return with the following formula:
return=log P(t)-log P(t-1), where P(t) is price at
time t say 10 am and P(t-1) is price at one period
previous t say 9 am?

Regards

CharlyThe min and max functions will tell you the price ranges

eg select max(pricecolumn) from tablename where date='20040517'

select min(pricecolumn) from tablename where date='20040517'

If you want to be more selective look at the date/time setting you are using in your data and tailor the where command to select at that particular time

eg where date='2003-02-28 10:00:00.000'

Look at books online for the log function, and use selective where clauses for the times, ie where date='2004-05-17 10:00:00.000'|||select max(pricecolumn) from tablename where date='20040517'
broup by [stocks name]

Monday, March 12, 2012

RDLC Reporting Service - setting up query parameters - is this possible?

Hi, I have my RDLC report, called on a ReportViewer, which receives a parameter (id of a column to filter the data), and the report receives this parameter very well, however, I want to use the value of this parameter to a query parameter of the dataset. On VS2005 we can asociate data sets to a report, but not a report parameter to a query parameter...is there any way to make this work?

I've heard that this is not possible using client report and report viewer, as print button. If I use a server report, will I have the print button on my application available?If not, how can I present the report with the print button available?

Thanks a lot!

You must use a server report to use reporting services built in print functionality.

For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

|||

dr_99:
You must use a server report to use reporting services built in print functionality.
For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

Yes I'm sure about that, thanks a lot!Only one problem, on IE the print button appears, but on Firefox does not appear, has anyone ideia what can cause this?It is set to visible!Thanks!

Wednesday, March 7, 2012

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx
|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA Push with Identity column

Hi Everyone:

I am new to Mobile programming. I am now working on a small mobile app.

I encounter an issue when I sync the data:

Using RDA Pull, I can create the database and populate my table fine on my pocket pc device.

After updating the data on the device, I encounter an error when I try to Push my table back to the back-end SQL Server 2005 DB.

Error: "The identity range was not established."

VB CODE:
Private Sub RdaConnKeyHeaderPush()

Dim RemoteAccess As New Data.SqlServerCe.SqlCeRemoteDataAccess
Dim RDAConnectingString As String
RemoteAccess.InternetLogin = "<mylogin>"
RemoteAccess.InternetPassword = "<mypassword>"
RDAConnectingString = "Provider=SQLOLEDB;Persist Security Info=True;Data Source=<MySQLServer>;Initial Catalog=MobileKeyDB;" & _
"User ID=MobileUser;Password=<mypassword>"
RemoteAccess.InternetUrl = "https://www.<mysite>.com/SQLMobile/sqlcesa30.dll"
RemoteAccess.LocalConnectionString = "Data Source=Program Files\SQL Mobile\en\MobileKeyDB.sdf; Password = <mypassword>"
Cursor.Current = Cursors.WaitCursor
Try

RemoteAccess.Push("KeyHeader", RDAConnectingString, Data.SqlServerCe.RdaBatchOption.BatchingOn)

Catch RDAConnectionException As Exception
MessageBox.Show("Can not push Header Data: " & RDAConnectionException.ToString, "Loading Key Tracker")
Finally

RemoteAccess.Dispose()
End Try
Cursor.Current = Cursors.Default
End Sub

TABLE DEFINITION:
USE [MobileKeyDB]
GO
/****** Object: Table [dbo].[KeyHeader] Script Date: 07/11/2007 09:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyHeader](
[trans_id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[date_stamp] [datetime] NOT NULL,
[signature] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [int] NOT NULL,
[id] [int] NOT NULL,
[date_stamp2] [datetime] NOT NULL,
CONSTRAINT [PK_KeyHeader] PRIMARY KEY CLUSTERED
(
[trans_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Anyone have any insight on a solution? Thanks.

For synch scenarios, you should always avoid identity columns, for an elaboration, see http://blogs.msdn.com/synchronizer/archive/2007/06/28/stay-away-from-auto-increment-columns.aspx|||

Ok....then I will try using the unique identifier.

My problem is here that I am unsure of how to add the guid to my insert statement in my application.

I have a dataset created that has an Insert menthod for a my table.

How do I set the properties of this Insert statement to use a GUID in it's execution?

Thanks.

|||Modify your insert command to use NEWID(), see http://msdn2.microsoft.com/en-us/library/ms173249.aspx|||

Ok...thanks for your help.

I tried inserting the NEWID() method into my sql insert statement, however when I run my application in debug mode,

I get an Null Value Exception on that UniqueIdentifier field....what would the best way to identify where and why this is happening?

The error occurs when I try to close the form by clicking 'ok' after entering a record......

Error....

NoNullAllowedException was unhandled

Column 'ID' does not allow nulls.

Code Point where error is generated....

Private Sub KeysEditViewDialog_Closing(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

Me.KeysBindingSource.EndEdit()

End Sub

Some Dataset Code.....is the null error on the sql side or app side, I am not sure of this?

Me._adapter.InsertCommand = New System.Data.SqlServerCe.SqlCeCommand

Me._adapter.InsertCommand.Connection = Me.Connection

Me._adapter.InsertCommand.CommandText = "INSERT INTO Keys"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&" (key_number, key_desc, building, inv_qty,"& _

" avail_qty, id)"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"VALUES (@.p1, @.p2, @.p3, @.p4, @.p5, NEWID())"

Me._adapter.InsertCommand.CommandType = System.Data.CommandType.Text

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p1"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_number"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p2"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_desc"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p3"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "building"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p4"

param.DbType = System.Data.DbType.Int32

param.Size = 4

param.IsNullable = true

param.SourceColumn = "inv_qty"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p5"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "avail_qty"

Me._adapter.InsertCommand.Parameters.Add(param)

Monday, February 20, 2012

Rate calculation with matrix reports, How?

I have a report like this, and I would need to implement drilldown on
both column group and row group, and i am running into problems:
2003
Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
Total_Rate
West 10 10% 20 20% 50 50% 20 20% | 100
100%
East 20 10% 30 15% 20 10% 130 65% | 200
100%
North 20 20% 20 20% 40 40% 20 20% | 100
100%
South 30 30% 20 20% 10 10% 20 20% | 100
100%
----
Total 80 16% 90 18% 120 24% 190 38% 500
100%
I can do sum on the numbers, but the rate calculation is difficult.
Does anyone know how to do this?You can use the scope argument to aggregate functions to define a scope over
which to calculate a total.
For a percent-of-total calculation like you describe, you would want
something like this:
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
where "matrix1_Region" is the name of your row group
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407261518.4e5582b6@.posting.google.com...
> I have a report like this, and I would need to implement drilldown on
> both column group and row group, and i am running into problems:
> 2003
> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> Total_Rate
> West 10 10% 20 20% 50 50% 20 20% | 100
> 100%
> East 20 10% 30 15% 20 10% 130 65% | 200
> 100%
> North 20 20% 20 20% 40 40% 20 20% | 100
> 100%
> South 30 30% 20 20% 10 10% 20 20% | 100
> 100%
> ----
> Total 80 16% 90 18% 120 24% 190 38% 500
> 100%
> I can do sum on the numbers, but the rate calculation is difficult.
> Does anyone know how to do this?|||Where exactly within the matrix would you put the
calculation? Thanks.
>--Original Message--
>You can use the scope argument to aggregate functions to
define a scope over
>which to calculate a total.
>For a percent-of-total calculation like you describe,
you would want
>something like this:
>=Sum(Fields!Sales.Value)/Sum(Fields!
Sales.Value,"matrix1_Region")
>where "matrix1_Region" is the name of your row group
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"Nick" <deadlocklegend@.gmail.com> wrote in message
>news:313b74d.0407261518.4e5582b6@.posting.google.com...
>> I have a report like this, and I would need to
implement drilldown on
>> both column group and row group, and i am running into
problems:
>> 2003
>> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
Q4_Rate | Total
>> Total_Rate
>> West 10 10% 20 20% 50 50% 20
20% | 100
>> 100%
>> East 20 10% 30 15% 20 10% 130
65% | 200
>> 100%
>> North 20 20% 20 20% 40 40% 20
20% | 100
>> 100%
>> South 30 30% 20 20% 10 10% 20
20% | 100
>> 100%
>> ----
--
>> Total 80 16% 90 18% 120 24% 190
38% 500
>> 100%
>> I can do sum on the numbers, but the rate calculation
is difficult.
>> Does anyone know how to do this?
>
>.
>|||As the value of the second data cell.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Terry" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c47417$66c96140$a301280a@.phx.gbl...
> Where exactly within the matrix would you put the
> calculation? Thanks.
>
> >--Original Message--
> >You can use the scope argument to aggregate functions to
> define a scope over
> >which to calculate a total.
> >For a percent-of-total calculation like you describe,
> you would want
> >something like this:
> >
> >=Sum(Fields!Sales.Value)/Sum(Fields!
> Sales.Value,"matrix1_Region")
> >where "matrix1_Region" is the name of your row group
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"Nick" <deadlocklegend@.gmail.com> wrote in message
> >news:313b74d.0407261518.4e5582b6@.posting.google.com...
> >> I have a report like this, and I would need to
> implement drilldown on
> >> both column group and row group, and i am running into
> problems:
> >>
> >> 2003
> >> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
> Q4_Rate | Total
> >> Total_Rate
> >> West 10 10% 20 20% 50 50% 20
> 20% | 100
> >> 100%
> >> East 20 10% 30 15% 20 10% 130
> 65% | 200
> >> 100%
> >> North 20 20% 20 20% 40 40% 20
> 20% | 100
> >> 100%
> >> South 30 30% 20 20% 10 10% 20
> 20% | 100
> >> 100%
> >> ----
> --
> >> Total 80 16% 90 18% 120 24% 190
> 38% 500
> >> 100%
> >>
> >> I can do sum on the numbers, but the rate calculation
> is difficult.
> >> Does anyone know how to do this?
> >
> >
> >.
> >|||Thanks much. That worked.
But the requirement has changed slightly because sometimes the Quarter
numbers don't add up to the "supposed" yearly number for several
reasons. Instead of dividing by the sum of all quarter numbers, I
need to get from proc a yearly number and calculate the percentage by
the yearly number and then tally up. I am running into problems
because when I use sum(Q_No)/First(Year_No) the rates work right, but
the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
right but the individual rates are wrong.
Any help very much appreciated.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> You can use the scope argument to aggregate functions to define a scope over
> which to calculate a total.
> For a percent-of-total calculation like you describe, you would want
> something like this:
> =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> where "matrix1_Region" is the name of your row group
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > I have a report like this, and I would need to implement drilldown on
> > both column group and row group, and i am running into problems:
> >
> > 2003
> > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > Total_Rate
> > West 10 10% 20 20% 50 50% 20 20% | 100
> > 100%
> > East 20 10% 30 15% 20 10% 130 65% | 200
> > 100%
> > North 20 20% 20 20% 40 40% 20 20% | 100
> > 100%
> > South 30 30% 20 20% 10 10% 20 20% | 100
> > 100%
> > ----
> > Total 80 16% 90 18% 120 24% 190 38% 500
> > 100%
> >
> > I can do sum on the numbers, but the rate calculation is difficult.
> > Does anyone know how to do this?|||If you need a different calculation in the total cells than in the detail
cells, that's where the InScope function comes in.
You can do something like this:
=iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
In your case, it would be:
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407271256.179a0f98@.posting.google.com...
> Thanks much. That worked.
> But the requirement has changed slightly because sometimes the Quarter
> numbers don't add up to the "supposed" yearly number for several
> reasons. Instead of dividing by the sum of all quarter numbers, I
> need to get from proc a yearly number and calculate the percentage by
> the yearly number and then tally up. I am running into problems
> because when I use sum(Q_No)/First(Year_No) the rates work right, but
> the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> right but the individual rates are wrong.
> Any help very much appreciated.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > You can use the scope argument to aggregate functions to define a scope
over
> > which to calculate a total.
> > For a percent-of-total calculation like you describe, you would want
> > something like this:
> >
> > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > where "matrix1_Region" is the name of your row group
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > I have a report like this, and I would need to implement drilldown on
> > > both column group and row group, and i am running into problems:
> > >
> > > 2003
> > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > Total_Rate
> > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > 100%
> > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > 100%
> > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > 100%
> > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > 100%
> > > ----
> > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > 100%
> > >
> > > I can do sum on the numbers, but the rate calculation is difficult.
> > > Does anyone know how to do this?|||thanks Chris.
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
didn't work, but this did,
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
However, there is a problem when the year was collapsed, the sum of
yearly number gets multiplied by the number of Q, which is 4
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value) *
CountDistinct(Fields!Q_No.Value))
Thanks a lot for your help.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<OLHBCbCdEHA.592@.TK2MSFTNGP11.phx.gbl>...
> If you need a different calculation in the total cells than in the detail
> cells, that's where the InScope function comes in.
> You can do something like this:
> =iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
> In your case, it would be:
> =iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
> Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407271256.179a0f98@.posting.google.com...
> > Thanks much. That worked.
> >
> > But the requirement has changed slightly because sometimes the Quarter
> > numbers don't add up to the "supposed" yearly number for several
> > reasons. Instead of dividing by the sum of all quarter numbers, I
> > need to get from proc a yearly number and calculate the percentage by
> > the yearly number and then tally up. I am running into problems
> > because when I use sum(Q_No)/First(Year_No) the rates work right, but
> > the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> > right but the individual rates are wrong.
> >
> > Any help very much appreciated.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > > You can use the scope argument to aggregate functions to define a scope
> over
> > > which to calculate a total.
> > > For a percent-of-total calculation like you describe, you would want
> > > something like this:
> > >
> > > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > > where "matrix1_Region" is the name of your row group
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach of
> > > children under 3.
> > > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > > I have a report like this, and I would need to implement drilldown on
> > > > both column group and row group, and i am running into problems:
> > > >
> > > > 2003
> > > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > > Total_Rate
> > > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > > 100%
> > > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > > 100%
> > > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > > 100%
> > > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > > 100%
> > > > ----
> > > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > > 100%
> > > >
> > > > I can do sum on the numbers, but the rate calculation is difficult.
> > > > Does anyone know how to do this?

Friday, March 30, 2012

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Reading a flat file

Wednesday, March 28, 2012

Read xml from sql server column(xml data type) and return as XmlDocument in C#

Read XML > 8k in sql TEXT column?

Monday, March 26, 2012

read only cell

read only cell

read only cell

Wednesday, March 21, 2012

Read and write a Constraint or Default Value

Tuesday, March 20, 2012

Re: How to encrypt a column in SQL Server

Re: How do you encrypt a password column?

Re: How can you encrypt a column in SQL Server?

Re: help please

Monday, March 12, 2012

RDLC Reporting Service - setting up query parameters - is this possible?

Wednesday, March 7, 2012

RDA with Identity column.

RDA with Identity column.

RDA with Identity column.

RDA Push with Identity column

Monday, February 20, 2012

Rate calculation with matrix reports, How?

Raster Catalog

Blog Archive

About Me