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

Access value from previou row

My formula needs to use value from previous row,how can i access a value from previous row?My formula needs to use value from previous row,how can i access a value from previous row?

try out with :

Previous (myfield)

Access to SQL conversion

I'm trying to convert an Access Query to SQL. I am using a "Mid Statement" to select a portion of a value:

Mid([ITEM_NUMBER],2,7)

When I try to parse this statement in SQL I always get an error "Does not recognize Mid as a valid procedure."

Is there an SQL statement I could use to accomplish this?the sql server equivalent is the SUBSTRING function

instead of
Mid([ITEM_NUMBER],2,7)

use
substring([ITEM_NUMBER],2,7)

Access to group field value from the child element

I have a matrix and have a column groups on month and products ( I have only
4 products) and am showing the number of products sold in that month. What I
need is to show instead of number products, I need to show the % of sales for
that product as compared to all sales.
I need to access the group total in the child field. If 10 items were sold
in each product, I would like to show 25% (10/40 * 100) in the detail. Is
there any way to access the group total in the child fields.
Any help is appreciated.
--
Thanks
RK BalajiI found the solution.. I just have to use the scope parameter in the count
function to get the value. Thanks.
"RK Balaji" wrote:
> I have a matrix and have a column groups on month and products ( I have only
> 4 products) and am showing the number of products sold in that month. What I
> need is to show instead of number products, I need to show the % of sales for
> that product as compared to all sales.
> I need to access the group total in the child field. If 10 items were sold
> in each product, I would like to show 25% (10/40 * 100) in the detail. Is
> there any way to access the group total in the child fields.
> Any help is appreciated.
> --
> Thanks
> RK Balaji

Access Text of Parameter

I have a report that uses parameters from a query. The query returns two
columns. One for the paramerter value and one for the parameter title. I want
the header of the report to access the same fild that I have set as the Label
Field in my report parameter dropdown.
I can add the actual parameter to the header using:
=Parameters!Property.Value
But I don't want the actual parameter, i want the alias from the second
colum of the parameter query.
I tried
=Parameters!Property.Text
but it didn't work.
Any ideas?
Thanks!try =Parameters!Property.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
>I have a report that uses parameters from a query. The query returns two
> columns. One for the paramerter value and one for the parameter title. I
> want
> the header of the report to access the same fild that I have set as the
> Label
> Field in my report parameter dropdown.
> I can add the actual parameter to the header using:
> =Parameters!Property.Value
> But I don't want the actual parameter, i want the alias from the second
> colum of the parameter query.
> I tried
> =Parameters!Property.Text
> but it didn't work.
> Any ideas?
> Thanks!|||That did it.
Many thanks Lev!
"Lev Semenets [MSFT]" wrote:
> try =Parameters!Property.Label
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
> message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
> >I have a report that uses parameters from a query. The query returns two
> > columns. One for the paramerter value and one for the parameter title. I
> > want
> > the header of the report to access the same fild that I have set as the
> > Label
> > Field in my report parameter dropdown.
> >
> > I can add the actual parameter to the header using:
> >
> > =Parameters!Property.Value
> >
> > But I don't want the actual parameter, i want the alias from the second
> > colum of the parameter query.
> >
> > I tried
> > =Parameters!Property.Text
> > but it didn't work.
> >
> > Any ideas?
> >
> > Thanks!
>
>

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 table body row from textbox using reportitems collection

Hi

I need to access value of perticular row that is populated in table component from a textbox outside table.

When you are saying particular row, i think you should be knowing the rownumber of the table.

Why can't you have a seperate dataset some thing like t

select top {row_numer} col_name from table1

take the last value from this dataset. leave the main dataset

I wish you might have got some idea with this push

Thank you,

Raj Deep.A

|||

Thanks for your kind consideration.

This solution is convinient if we have one or two cells in which we need to fetch data. But I have a report in which 40-50 such fields are there. And to create a dataset for each row would be tiresome job and not optimal. I was looking at some solutions in direction of

ReportItems collection. I am not sure wether we can get data from data section of table ie except header and trailer.

Regards

Spandan

Access object within report

Is it possible to access a text box within a report? Something like this:
Report.TextBoxName.Value
Thanksyes, try:
Reports!ReportItem.Value
"Mark Goldin" wrote:
> Is it possible to access a text box within a report? Something like this:
> Report.TextBoxName.Value
> Thanks
>
>|||Sorry that should have been:
ReportItems!ReportItem.Value
Michael C
"Mark Goldin" wrote:
> Is it possible to access a text box within a report? Something like this:
> Report.TextBoxName.Value
> Thanks
>
>|||Thank you. Will try.
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:F586D1A0-9489-4183-9DEA-B3F209FCE4B2@.microsoft.com...
> Sorry that should have been:
> ReportItems!ReportItem.Value
> Michael C
> "Mark Goldin" wrote:
>> Is it possible to access a text box within a report? Something like this:
>> Report.TextBoxName.Value
>> Thanks
>>

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

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

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