Accessing grouped values "inside" fields

Hello,
I'm struggling to make operations on results obtained from grouping by
specific dimensions. To be more specific, I'd like to substract value of
e.g. sales for some product for one year from values from the preceding
year.
I'd like to somehow access the resulting recordset from grouping and make
some operations on these values, just like the SUBTOTAL function does.
Thanks in advance for any help.
Best wishes,
Marek T³uczekOne correction to my question:
> I'd like to somehow access the resulting recordset from grouping and make
> some operations on these values, just like the SUBTOTAL function does.
I meant not recordset, but set of fields.
> Thanks in advance for any help.
> Best wishes,
> Marek T³uczek
>
>

accessing datasets / Field collections through custom code

Hi,

My report has multiple datasets, and I want to access the fields of a particular dataset from custom code. The problem is that the name of the dataset to use is only calculated during the execution of the custom code.

Allow me to illustrate:

public function Test(data as DataSets, fieldName as string)

dim datasetName as string = CalculateDatasetName()
dim ds as Dataset = data(datasetName)

' now here I want to get the fieldName out of the dataset.
' in the report, I would do something like
' First(Fields!fieldName.Value, datasetName)
' but in custom code, this obviously doesn't work.

end function

I've been looking for a way to accomplish this, but it seems you cannot get data from a dataset through custom code (there is only the commandtext property). Also it is not possible to pass the Fields collection as a parameter, as I do not know the dataset name when in the report designer.

I hope my problem description is clear.
Does anyone know how to solve this issue?

Thanks,
Phil

Hi Phil,

Look at the code below

public DataSet Dataset_name = new DataSet();

public DataTable DataTable_name = new DataTable();

public DataColumn DataColumn_name = new DataColumn();

public DataView View_name = new DataView();

public datatype function(name_of _variable_which catch_the column_value datatype)

{

DataColumn_name = new DataColumn("name_of _variable_which catch_the column_value", System.Type.GetType("System.String"))

}

Hope this helps

Priyank

Accessing Data Fields in Page Footer

Hi All,

How do I access data fields in page footer. Although Page Header and Page Footer can't contain data fields.

What is alternative or other other solution for the same?

It would really nice if somebody can help me out.

Thank you

Regards,

Palak Shah

Have an invisible textbox in your report and name it as textboxHidden and have any value in it from your dataset.

And in your footer or header, use the expression "ReportItems!textboxHidden.Value" to the value.

Shyam

|||

I was trying in the same way but using directly textbox.value.

Thank you for letting know that we can access Report elements using ReportItems.

It was really helpful. You have really solved one of my important problems.

Can you also let me know which Book will be good for Studying.

Also can we calculate Cumulative Count using Runnning value function like it is in 2005 reporting services.

I have query reagding graph. Can we break graph if the scale is more than the certain range.

Thank you,

Regards,

Palak Shah

|||

So, can you please mark my post as answer?

I dont know about any book, but you can always learn by visiting this forum and then using MSDN.

Can you please start a new thread for other issues, so we can discuss them there?

Shyam

Accessing a View from within a Stored Procedure

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @.MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @.MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!

Any offers kind people??It's a top kinda monday

top without order by is meaningless

Where's the DDL for the view?

And the actual sql statement or sproc?|||All the sorting and the like is done within the view itself. The only thing I need to know is if the view returns a recordset and if it does then proceed with the stored pro. A 'trimmed' down version of the Code is the following:

CREATE PROCEDURE SP_LastPDFCreated
AS
DECLARE @.MyLastPDFDate as DateTime, @.MyCurrentTime AS DateTime, @.MyJobN AS INT

SET @.MyJobN = ''
SET @.MyCurrentTime = GETDATE()

SELECT TOP 1 @.MyLastPDFDate = DatePDFCreated FROM dbo.TArcCert WHERE (DatePDFCreated IS NOT NULL) ORDER BY DatePDFCreated DESC

SELECT TOP 1 @.MyJobN = IJobN FROM MyView

IF @.MyJobN <> ''
BEGIN
IF DATEDIFF(n, @.MyLastPDFDate, @.MyCurrentTime) > 10
BEGIN
EXEC LimsLive.dbo.SP_TestXPSendMail
END
END
GO|||You sure the view exists?

And change the if statement...

IF EXISTS(SELECT TOP 1 IJobN FROM MyView)
BEGIN
.....
END|||God and it's only Monday!

Early entry for 'Twat of the week' award. I'd spelt my view incorrectly.

Apologies for wasting your time but thank you for your responses.

Sorry Matey|||and there's a term I haven't heard in a looooooooooooong time...

begs the question...since you didn't fill out the profile...

gender?|||That'll be Male, I've filled in bits and bobs of the profile|||I think "Twat" is the plural of "Twit" ;)|||I think "Twat" is the plural of "Twit" ;)Two points for the correct interpretation of English slang!

As I'm still a "Technical Wizard of Information Technology" with a number of UK friends, I've been informed in great detail about these things!

-PatP|||Two points for the correct interpretation of English slang!
if I can ever do that, It's an accident. I'm STILL trying to figure out what a couple gals (who CLAIMED to be speaking english) were saying in front of me in cockney at lunch one day in Croydon - something about crossing a road to see a toad or something...:scratching head:|||if I can ever do that, It's an accident. I'm STILL trying to figure out what a couple gals (who CLAIMED to be speaking english) were saying in front of me in cockney at lunch one day in Croydon - something about crossing a road to see a toad or something...:scratching head:Whenever you have two women talking to you about "crossing a road to see a toad", the best possible response I can imagine is to "play dumb" whether you have any clue or not!

-PatP|||Or put on some green and get over there before them.|||Hate to disagree folks, but I don't think Twat is a plural of anything. Rather a slang name for a part of a lady's anatomy!

Access to fields in a table

I am trying to access the fields in a table using Visual Basic in Web Developer .NET

Start with the link below and click on SelectCommand, InsertCommand, DeleteCommand and UpdateCommand to get tbe basic of manipulating data in the database through ADO.NET. Hope this helps.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

Access to dataset fields in Code window

Hi,
Is it, or will it be possible to use dataset fields in expressions in the
code window. We are converting formulas which are too complex for an IIF
expression in the report, and would prefer not to create too many sprocs in
the database.
Cheers,
MattYou cannot access fields directly in the code window. You need to pass-in
the field values as arguments to your custom code function. The following
article shows how to do this:
http://odetocode.com/Articles/130.aspx
A more detailed discussion is provided in the following MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:3EFF6484-97F2-4744-BB6F-6DC35F084D7B@.microsoft.com...
> Hi,
> Is it, or will it be possible to use dataset fields in expressions in the
> code window. We are converting formulas which are too complex for an IIF
> expression in the report, and would prefer not to create too many sprocs
> in
> the database.
> Cheers,
> Matt

Access second grid from SQL stored procedure

Hi:
I have a SQL stored procedure that returns 2 tables. How can I access the
fields from the second table. even though the column names are distinct in
the 2 tables, my reports do not get to the second table's fields. I cannot
join the 2 tables returned. And i cannot have them in 2 separate stored
procedures, because they use the data from the first table.
Please suggest something.
ThanksAre you using temporary tables? Not sure why you can't combine the 2 tables
into a 3rd table to return one result set. Can you explain further?
"NI" wrote:
> Hi:
> I have a SQL stored procedure that returns 2 tables. How can I access the
> fields from the second table. even though the column names are distinct in
> the 2 tables, my reports do not get to the second table's fields. I cannot
> join the 2 tables returned. And i cannot have them in 2 separate stored
> procedures, because they use the data from the first table.
> Please suggest something.
> Thanks|||well my first table is like a summary table, and the second table is like a
detailed table. One issue am having whihc is stopping me from combining both
tables is that the second table has duplicates (whihc I want to show in the
second table but not in the first table). I know SQL RS has the hide
duplicates option, ad if I do a grouping on that particular column it hides
duplicates, but the issue is with the count. If I have just one table (the
one with duplicates in it), I cannot get the right count (without duplicates)
in RS, as it always returns me the count with duplicates. so i had to
separate these two tables.
Also though there a few common columns in these tables, knowing the data I
know it wouldnt make much sense combining them, so I want to leave them as
separate tables.
Right now the workaround I have is have 2 different datasets doing the same
thing, but returning only one of the select statements. But its kinda wierd
to have 2 separate datasets doing the same thing.
Thanks
"phil" wrote:
> Are you using temporary tables? Not sure why you can't combine the 2 tables
> into a 3rd table to return one result set. Can you explain further?
> "NI" wrote:
> > Hi:
> >
> > I have a SQL stored procedure that returns 2 tables. How can I access the
> > fields from the second table. even though the column names are distinct in
> > the 2 tables, my reports do not get to the second table's fields. I cannot
> > join the 2 tables returned. And i cannot have them in 2 separate stored
> > procedures, because they use the data from the first table.
> >
> > Please suggest something.
> >
> > Thanks|||And you never will, except for using aggregation functions.
I think this is a bummer.
Look up SCOPE in the RS help and you will see what I mean.
Here's a sample assuming dataset1 and dataset2:
TextBox1.value = Fields!FirstName.Value
TextBox2.value = Sum(Fields!Income.Value, "dataset2")
BR//Jerry

Access Memo field to SQL Server Text field

Hi,

I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).

I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.

Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.If the data is visible within SQL Server, but not from your ASP page, then the problem is with your ASP page and the way it is pulling the text data.
Do you really need this to be a text field? The varchar datatype will handle up to 8000 bytes.|||That's a good point - I might be able to get away with varchar. It's something I'll look into, but I'm still curious as to why the data behaves this way (I ran into similar problems in the past while trying to import Access data to SQL server).

My ASP page is functioning properly. I use a simple "SELECT *" statement to pull the data and display it on the page. With an Access DSN it encounters no problems and shows all the data as intended. When I import the data into SQL Server and swap the DSN to point to the newly-created database (while not modifying the ASP page itself in any way) the text fields display no content.|||A text field in SQL Server is not stored within the record. The record actually only stores a pointer to the location of the actual data. This may be why your page is not handling it properly, but I don't know the work-around. You could try posting your question on the ASP forum.|||Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...

Maybe there's something that can be done on the ASP side - I'll ask some ASP guys.

Thanks.|||Interesting... I was aware that Text fields are stored differently and separately from the regular data in the database, but I didn't think it would affect the way they're displayed through the recordset...I wouldn't have thought so either, but it is my best guess.
What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?|||I use a simple "SELECT *" statement to pull the data and display it on the page.

ahhhhhhhhhhhhhhhhhhhhhhhhhhh

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx|||I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...|||I try to do it properly with larger systems and database, but, yeah, for me it's laziness that makes me use "Select *"...

Hey! No one's lazier than me....

SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'xxx'
ORDER BY ORDINAL_POSITION

SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code|||I wouldn't have thought so either, but it is my best guess.
What happens in your ASP code if you enumerate the column names rather than using "select *" (which is a bad practice anyway)?

Well, that's an interesting question, mostly because it gives me some sort of a deja vu feeling - as if I encountered something like this several years ago...
It's possible that I did something like this in the past and fixed the problem that way.
I'll check into this on Thursday when I'm back at work.

Thanks.|||You are off UNTIL Thursday?

Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.|||Hey! No one's lazier than me....

SELECT ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'xxx'
ORDER BY ORDINAL_POSITION

SELECT * can buy you a boatload of trouble...so it's amatter how you want to spend your laziness...you have no choice when you have exploding code

That's an interesting way to structure a query - I've never done it this way before.

Dare to dream... Sorry, you think that you're the laziness champ, but you're not. Your laziness doesn't compare to mine - not even close.|||You are off UNTIL Thursday?

Okay, but this Thursday is Turkey Day for us Yanks, so you'll have to depend upon the Pootle Flumps and Cannucks of the world to help you out.

Yes, it sounds a bit odd. I'm actually at work as we speak, but I also have another part time job as a contractor with my previous employer. Thanksgiving will be a good time to come in and get a bunch of work out of the way.|||See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

some laziness makes your life more simple, other types make your life much more difficult, you have to decide|||See but here's the rub, my laziness is born out of economy of effort..the smarter you work, the less you have to

some laziness makes your life more simple, other types make your life much more difficult, you have to decide

Agree. Mine usually gets me in trouble somewhere down the line.

... but it still feels good to be the "Champ" :)

Access managment in SQL

Hi,
I have just imported my old access database into my new
SQL database and have come over a few problems.
All memo fields have been changed to "ntext" fields. This
is no good for me as I have lots of text in the fields.
When I try and change one of the fileds I get this error:
'creek_products' table
- Unable to modify table.
ADO error: Cannot create a row of size 8129 which is
greater than the allowable maximum of 8060.
The statement has been terminated.
I'm only trying to change one field to nchar because then
it will let me have a lenght of 4000.
Can anyone help me with this?
I need to get the max lenght field type working.
Thakns
I hope you got answer to your question but if not:
In SQL Server every row has a limit in size.
Consider when converting, creating tables first and then pouring data into it.
I am still puzzled what's wrong with ntext data type?
"Luca" wrote:

> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
>
|||Luca wrote:
> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
Why is ntext no good for you? If you have lots of text, as you
mentioned, then ntext or text is what you want. ntext is unicode and
requires twice the storage of text. If you don't require unicode
support, use text.
The nchar(4000) requires SQL Server reserve 4000 bytes for each row.
That will give you terrible performance because only a couple rows will
fit on each SQL Server page (same as nvarchar(4000) would do). There's a
limit of about 8060 total bytes stored within a row (excluding the text
in text and ntext columns). Using text or ntext fixes this by only
requiring a 16 byte pointer in the table. The text is stored elsewhere,
on a different filegroup if you prefer.
Can you explain what problem you feel you are having using ntext?
David G.

Access fields in the Fields collection by index

Is there anyway to access the fields collection using an index?
e.g,. =Fileds(0).Value ?
I want to create a calculated field based on two columns (position 1 and 2)
in a matrix report, but their column names change based on the dates as
paramaters.
Any ideas?On May 3, 11:06 am, Eric <E...@.discussions.microsoft.com> wrote:
> Is there anyway to access the fields collection using an index?
> e.g,. =Fileds(0).Value ?
> I want to create a calculated field based on two columns (position 1 and 2)
> in a matrix report, but their column names change based on the dates as
> paramaters.
> Any ideas?
You should be able to access the position 1 column via: =Min(Fields!
FieldThatIsPivoted.Value). The second positioned column will be tricky
to capture though. Otherwise, you should be able to handle this in the
query/stored procedure that sources the report (via while loop or
cursor). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant