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 dataset with custom code

Hi,

Need a little help on the code here. How can I import values from an RSS dataset? I will store them in a hash table and then call the function to check for dups. An example or pointer or article reference would be appreciated.

Thanks.

Since RSS is xml, you can use the XML data processing extension that comes with SQL Server 2005. This link has documentation on this feature:

http://msdn2.microsoft.com/en-us/library/ms159741.aspx

Basically, you create a data source in report designer with type xml and specify the connection string to be the url of RSS feed. The query language it uses is similar to XPath. The doc for the query language is here:

http://msdn2.microsoft.com/en-US/library/ms365158.aspx

|||Here is an example of using the Xml Data Processing extension using this forum's RSS feed as the source.

Connection String:
http://forums.microsoft.com/MSDN/rss.aspx?ForumID=82&Mode=0&SiteID=1

Query:
<Query>
<ElementPath IgnoreNamespaces="True">rss{}/channel{title,link, description}/item{title, date, link, description, comments}</ElementPath>
</Query>

Ian|||

Thanks for all the help. This stuff is pretty much new to me. I'm mostly familiar with VBA, Classic VB, and T-SQL but we'll see where this leads.

Accessing Dataset values

I have 2 datasets. One is used to give the values to a parameter and the
second one is used to populated the main table in my report.
The first one has a code and a Name like:
Code Name
c cat
d dog
I would like to display â'catâ' in the main table, but I just have the code
â'câ'. Is there a way to do it ?Soan,
I'm not sure I fully understand your scenario.
If you're after the 'label' from the chosen parameter, you can get it
using;
=Parameters!paramname.Label
Chris
Soan wrote:
> I have 2 datasets. One is used to give the values to a parameter and
> the second one is used to populated the main table in my report.
> The first one has a code and a Name like:
> Code Name
> c cat
> d dog
> I would like to display â'catâ' in the main table, but I just have
> the code â'câ'. Is there a way to do it ?|||I am looking for a function that returns the second value of the dataset when
i give the first one.
Something like Parameters!paramname.Label the difference is that the
parameter does not have the valued assigned (I want to access different
values in the dataset).
> I'm not sure I fully understand your scenario.
> If you're after the 'label' from the chosen parameter, you can get it
> using;
> =Parameters!paramname.Label
> Chris
>
> Soan wrote:
> > I have 2 datasets. One is used to give the values to a parameter and
> > the second one is used to populated the main table in my report.
> >
> > The first one has a code and a Name like:
> > Code Name
> > c cat
> > d dog
> >
> > I would like to display â'catâ' in the main table, but I just have
> > the code â'câ'. Is there a way to do it ?
>|||Soan, I think you just need a join in the second dataset on the table
from the 1st dataset that gives you the description. You should make
the dataset contain all the data you want to show in the data region.
eg dataset 1 gives salesperson list;
Select SalesId, FirstName from tblSalesperson
this is used in the parameter. The value is SalesId, the label is
FirstName.
the second dataset would return the orders for that salesperson, but
you want the sales persons fullname on it from the salespersons table;
Select O.OrderId, O.SalesId, S.FullName, O.Date, O.Value
From tblOrder O
Join tblSalesperson S On O.SalesId = S.SalesId
Where O.SalesId = @.parametername
You can achieve this in the Graphical query designer, but I always use
the Generic designer so can't really explain it that well.
Hope that helps.
Chris
Soan wrote:
> I am looking for a function that returns the second value of the
> dataset when i give the first one.
> Something like Parameters!paramname.Label the difference is that the
> parameter does not have the valued assigned (I want to access
> different values in the dataset).
>
> > I'm not sure I fully understand your scenario.
> > If you're after the 'label' from the chosen parameter, you can get
> > it using;
> > =Parameters!paramname.Label
> >
> > Chris
> >
> >
> > Soan wrote:
> >
> > > I have 2 datasets. One is used to give the values to a parameter
> > > and the second one is used to populated the main table in my
> > > report.
> > >
> > > The first one has a code and a Name like:
> > > Code Name
> > > c cat
> > > d dog
> > >
> > > I would like to display â'catâ' in the main table, but I just
> > > have the code â'câ'. Is there a way to do it ?
> >
> >

Accessing conditional values in Groups

Hello

I am trying using following in a group footer and it gives me an error!

=Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502",Fields!BUDGETAMOUNTOYR.Value,0)).

Can anyone please shed some light

Thanks

What exactly is the error message?

Is it about aggregating fields of varying data type? In that case, the following expression should work for you: =Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502", CDbl(Fields!BUDGETAMOUNTOYR.Value), 0.0))

-- Robert

|||

Thanks Robert.

Casting the value to double solved it! But I still think this is a bug. As this only occurs when the contidion satisfies at some value, otherwise it works fine. For example:

Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "B1502", Fields!BUDGETAMOUNTOYR.Value, 0.0)) - Give an Error in the text box

But

Sum(IIF(Fields!COSTCENTRETYPECODE.Value <> "XXX", Fields!BUDGETAMOUNTOYR.Value, 0.0)) - Would sum all the amounts.

Data set contains value B1502 as Costcentretype code while XXX is not in the data set. So even though it will not include the amount where this cost centre is found you still got to convert it to double to not get an error. But when the condition never becomes true it will return the sum of the amounts without converting.

Regards

accessing blank values in a table

Hi,
I am trying to format a column of varchar which has decimal values..Since it
is varchar I was not able to format it directlyu, so had to mulitply and
divide each value by 100 and then apply formatting..Not all the rows of the
table have data, some are blank..and these show up as "#Error" on the
report.I try not to do any operations on them by using "Isnothing" or
comparing each value with "" in the IIf statment..But neither works...Can
someone tell me how I can access the blanks in the table...
ThanksTo see if the column is empty in the IIF statement, use the following syntax
IIF ("" & MyColumn <> "", the rest ...)
It takes an empty string and concatenates the column value to the empty
string, if the column is empty, it results in an empty string.
"Dolly" <Dolly@.discussions.microsoft.com> wrote in message
news:FD0B22DD-BA90-4B2C-9495-A5E783B0467B@.microsoft.com...
> Hi,
> I am trying to format a column of varchar which has decimal values..Since
it
> is varchar I was not able to format it directlyu, so had to mulitply and
> divide each value by 100 and then apply formatting..Not all the rows of
the
> table have data, some are blank..and these show up as "#Error" on the
> report.I try not to do any operations on them by using "Isnothing" or
> comparing each value with "" in the IIf statment..But neither works...Can
> someone tell me how I can access the blanks in the table...
> Thanks

Accessing ASPNETDB from outside web application

Hi there,

I have a slight problem. I have a web service running on my server. In this, I need to access values in User's profiles. The problem is of course, that Profile, ProfileCommon, ProfileBase, etc... are not available to me here.

What can I do?

Is using an SQL command best? If so, could someone post an example SQL command - I am getting confused by how to structure the command, since I need Membership data like UserName, and Profile data (an object serialized as binary in the Profile).

Kind regards,

kreid

Hi Kreid,

To share the ASPNETDB to another application, you will need to attach the database to an instance of SQL Server. Then change the connection string of both applications to point to that database.

|||

Hi Kevin,

I'm having the sampe problem even though I have my ASPNETDB in an SQL-server. After thoroughly checking the interfaces, and forums, there is apparently no way to access the Profile from a console application. Certainly, I can do a LINQ query and get the Profile properties string and values string from the ASPNETDB, but then parse them myself etc.? There must be a better way? Any tips/examples out there?

Thanks!

Richard

|||

The Solution:

http://blogs.msdn.com/msdnts/archive/2006/12/16/asp-net-2-0-membership-role-management-out-of-asp-net-context.aspx

Access Yes/No field equivalent in Sql Server

Hi,
I am building a table in sql server. One of the field is equivalent to
Access yes/no field. This will have values Yes or No obtained from a form in
Asp page. I am wonering what would be the correct datatype for this field.
Thanks.char(1) - "y" or "n"
varchar(3) - "yes" or "no"
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||Jack,
You could use a bit datatype with support for 0 or 1 then translate that to
Yes or No in code. You could also use a char(3) with a check contraint
limiting the valid entries to Yes/No. A default constratint can be added as
well for a default of Yes or No when a user does not explicitly enter a
value.
HTH
Jerry
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||You may consider using Bit for boolean data type in Presentation layer.
Perayu
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||Thanks for all the help from Raymond, Jerry and Perayu. I appreciate it.
"Perayu" wrote:

> You may consider using Bit for boolean data type in Presentation layer.
> Perayu
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
>
>

Access to the property values for my expressions

I may be looking too hard for this but I can't find a way around it.

I have an Expression and in that expression, I want to access a property on the same object (it would be great to get the properties of other objects as well).

Example: I have a flat file connection where I defined the name of the flat file in my ConnectionString. I also have a variable that I have it linked to my dtsConfig which points to the proper folder name at run time.

How can I create an expression similar to this:

@.[User::strFolder] + @.[Connectionstring]

where @.[User::strFolder] is my variable and points to the correct folder for the given server the package is running on and @.[Conenctionstring] is my made up name to access the VALUE of the Connectionstring that I have for this flat file.

So if I have the following:

in my connectionstring property: flatfile.txt

in my strFolder derived from dtsConfig at runtime: E:\etl_data\

I would like my final connectionstring to look as follow:

E:\etl_data\flatfile.txt

So far I know I can do it with two variables but it would be great if I could reuse the property values of the current object for my expressions or any other object.

Perhaps this value is available thru the script where I can access "any" property in my dtsx and store it into another variable and then use it. This option at least allows me to reuse code instead of hardcoding table name (connectionstring) into my variables.

Did I make this too difficult and there is a simple way to access an object's property inside the expression builder?

Thanks

Anatole

I guess what you want to be able to do is specify the filename at design time and reuse that at runtime. Unfortunately you can't have both worlds. Either its set at design time with directly or via an expression.

I think your only option is to use two variables.

|||

HOw about just accessing any of the properties? Can it be done? So far I know I can access the system/pkg and user defined values.

For my example, if reading from & writing to the same property is not valid/acceptable, how about writing to ConnectionString using a Variable + another property value such as the description?

Anatole

|||

Package properties are read only at runtime. The only way to change them is via expressions.

This is a principle of the SSIS architecture.

Whats wrong with 2 variables?

|||

Simon,

Are they read-only or Not accessible? If read-only, I'll be happy with that as long as I can read them somehow and use expression to change them but perhaps you meant to say that SSIS engine reads them and not available to anyone else.

I'm OK with 2 variables but since I already have some 40 plus pkgs that read from flat files where the location is different once deployed, it would be great to write a simple expression using concatenation and substring (if needed to parse the file name) instead of adding two variables to every pkgs. The design changed and instead of using dtsconfig to change the location when the manifest is deployed, I now need to have a parent pkg execute file system pkgs droped in a folder.

The SSIS already has the functionality to pull the system properties. It can create variables and has this additional expressions available for us to manipulate the values at run time. Why restrict it to system properties? If they didn't allow us to change the value of a property at run-time, I can understand. But since we have this feature, why not allow us to read it?

Maybe in the next release they will have this access.

Thanks

Anatole

Access to Sqlserver transfering ?

I am having Access database in my local system . Values in the access database will be updated for every 5 min .

I am also having Sqlserver database online with the same structure database of Access in my local system .

What i want is my Access database values must be updated in my online sqlserver at every 5 min automatically .How to do that ?
I hope your database isn't too big. Google around about the DTS packages of SQL Server. You can make one (it defines import/export mechanism), then call it periodically.|||

You can set a flag column in access database with default value 0. If the data is updated set flag to 1.

Then you run a program periodically update the updated data in access to sql server and set all flags to 0.

|||

hi dear ;

i think u mast using datareader object (connected datasource) to get ur data online updated but u mast try to refrishing it.

dont use dataset (disconnected datasource)

Access temp table within Table value function

hi,

I am trying to access a temperory table within a table values function, but when i try to do i get an error saying unable to access.

This is wat i am trying to do. here is the sample code. I am creating a table value functiona and trying to insert data from a temp table. if this is not possible, there should be an alternative. creation of this function is important as the same is used in SSIS to build a package.

Please help me ....

Create function SiteAddress_func()

returns @.Data_For_Site_Address_Table table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(10),

CreatedBy varchar(20)

)

As

Begin

insert into @.Data_For_Site_Address_Table

select * from #NON_Matching_Records_From_Site_Address

return

end

rgds,

Meher Krishna.V

Meher:

One of the rules for function creation is that a function is not allowed to access temp tables. Please examine books online about some of the constraints that are applied to functions.

Dave

|||

hi Dave,

your right.... But there should be an alternative to this ? I cannot use an SProc here, because they are not schema bound.

rgds,

Meher

|||

Meher:

You might need to resort to transorming your temp table to a "string array / string table" and passing that to the function. Also, since your function does not include schema binding why are you worried about stored procedures not being schema bound? How are you planning on using this function?

You can find an article here that discusses array implementation here:

http://www.sommarskog.se/arrays-in-sql.html

Dave

|||

I need to use this function in creating an SSIS package. Here its important that whatever i use should be schema bound. Only then i can map the columns. If i use a stored procedure with temp tables, my purpose will be defeated as it not schema bound.

rgds,

Meher

|||

Instead of using the Temp table you can use Physical table..

UDF never allows to access the temp tables..Need more info about what exactly your function try to do.. So we can suggest similar solution with Physical table...

|||

Here is the problem in detail.

I am extracting the required data from three physical tables. After extracting i will be doing furthur manipulations like running a cursor to remove the duplicates and store the final output data in a temp table.Then i will be using this query in an SSIS package in BIDS(Business intelligent development studio) to transform data from SQL server to an MDB file at regular basis using jobs and schedulers.

The constraint is that i cannot use temp tables directly in the SSIS package because it doesn't return a schema. The same is with sproc. I need it to return a schema so that i can map the fields from an input source namely SQL to a destination which is the MDB file. So, instead of using sproc i am trying to using a tvf(Table Value Function) which is capable of returning a schema. But even this failed as i cannot use a temp table in a function.

Hence creating physical tables for this purpose alone is a bit problem as the data porting is to be deployed on a live server.

please let me know wat to do furthur.......

rgds,

Meher

Access Items Generated in Matrix?

I am using a matrix in a report and it would be great to be able to work with
some of the values it is displaying after they had been grouped and split
apart.
Is it possible to access these values from the matrix somehow?i would look up actions with analysis services. this might be what you're
talking about.
OLAP (analysis services) against OWC allows a flexible, high-performance
reporting solution.
-aaron
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:56DC1DA7-0E89-4FA0-821C-775B33B965C2@.microsoft.com...
> I am using a matrix in a report and it would be great to be able to work
with
> some of the values it is displaying after they had been grouped and split
> apart.
> Is it possible to access these values from the matrix somehow?|||Interesting... I will look at it.
Besides this possibility, does anyone know of any other technique that might
be useful here? Is there a way to do something akin to subtotaling but
instead have it show the difference between columns?
"aaron kempf" wrote:
> i would look up actions with analysis services. this might be what you're
> talking about.
> OLAP (analysis services) against OWC allows a flexible, high-performance
> reporting solution.
> -aaron
>
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:56DC1DA7-0E89-4FA0-821C-775B33B965C2@.microsoft.com...
> > I am using a matrix in a report and it would be great to be able to work
> with
> > some of the values it is displaying after they had been grouped and split
> > apart.
> >
> > Is it possible to access these values from the matrix somehow?
>
>

Access Formula values at runtime

How can i retrive value of a formula field or any other field in code at runtime, which can be used in code 4r further operations.

e.g :I have a formula field which calculates a Value Discrepancy.I have to show a message box Message Box based on its value from the code.
I am using CR for VS2005


Thankyou
ShauryaFinally i found the solution to my problem so i am sharing the solution here.

The values that are printed on the report can be accessed from the code by this code.

CrystalDecisions.CrystalReports.Engine.ReportDocument a = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

string value=a.Rows[0][20].ToString();

The Rows property contains the data on the front page of the report.|||how did you get this CrystalDecisions.

Any reference to add to repor to get it.

Amarjit