Showing posts with label expression. Show all posts
Showing posts with label expression. Show all posts

Wednesday, March 28, 2012

how to assign null in derived column transformation editor

Dear friends, can any one tell me how to assign null to the expression value in derived column transfromation editor?

thanks,

In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.

How to assign an expression with a ssis variable?

Hi all of you,

That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.

Something like that:

'La tabla "' + SUBSTRING( @.[System:Stick out tongueackageName], 3,20) + "' se ha cargado correctamente'

TIA for that,

Sorry, it's solved

" La tabla " + SUBSTRING( @.[System:Stick out tongueackageName], 3,20) + " se ha cargado correctamente "

Wednesday, March 21, 2012

How to aggregate a column that was itself an aggregate

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

Below is a message:
The value expression for the textbox 'textbox13' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.


Need help.
Thanks, MickB

Dude,

did you get any luck from anyone with this? It seems that this major piece of functionality has been omitted from Reporting Services? Or am I just being thick? It's certainly not as simple and straight forward as Crystal, if it can be done at all. Will M$ be adding this at some point in the future? Can anyone shed any light on either my lack of knowledge or if M$ is going to implement it?
Cheers,
Dunk

|||

Ditto. And runningvalue in a chart would be nice too.

|||Have you tried to create a new field that contains the RunningValue (let's call it NewField) and instead of make a Sum on the Running value you make a sum on the new field (=Sum(Fields!NewField.Value, "YourDataSet") )?
It works, as far as I can tell.|||

Is this in Report Designer?

I believe that the aggregate can show different values depending on the grouping and where on the table you put the column.

For Example, If you create a new grouping row with all of your monthly data in it and a monthly total then use the same aggregate in the row below it should give you a total of all of the monthly rows.

Don' know if i've explained this very well, it sounds better in my head!!

Hope this helps

|||Harley's right. You can NOT embed aggregate functions within each other, nor can you use a calculated field as an expression in an aggregation function. What you can do is specify at which level you want to apply the aggregate function.

For instance, if you want to add up all the sales in a grouping called salesDay, you'd do this :

RunningValue( Fields!Sales.value, sum, "salesDay" )

or salesMonth, same thing

RunningValue( Fields!Sales.value, sum, "salesMonth" )

If you want to add up all the sales for the current grouping ( if you're in the footer, for instance), you can leave the scope parameter blank:

RunningValue( Fields!Sales.value, sum )

If you place that code in the footer for your month grouping and in your table footer, i think that'll do what you need.|||

The problem I have is that one group nested inside another, and I do a CountDistinct on a column for the innermost group. This value represents a number of events that my report is supposed to track.

If I do the same CountDistinct on the outermost group, the CountDistinct is different because values that were unique within two groups are not unique within only the outermost group.

What I need to do is a CountDistinct on the innermost group and a Sum of the CountDistinct values I came up with -- I would show this in lieu of the CountDistinct for the outermost group.

But, alas, I don't see a way to do it. I'm open to suggestions if anyone has one.

|||

So are we saying that this in the grouping should or shouldn't work?

=Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value)

It works fine however try something a little more complex and we get the same error

=RIGHT(("00"&(INT(SUM(CInt((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600))),2) & ":" & RIGHT(("00" & (FLOOR((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)-INT((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)))*60)))),2) & ":" & RIGHT("00" &(ROUND(((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60)-INT((SUM(CINT((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60))))*60))),2)

Any ideas?

How to aggregate a column that was itself an aggregate

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

Below is a message:
The value expression for the textbox 'textbox13' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.


Need help.
Thanks, MickB

Dude,

did you get any luck from anyone with this? It seems that this major piece of functionality has been omitted from Reporting Services? Or am I just being thick? It's certainly not as simple and straight forward as Crystal, if it can be done at all. Will M$ be adding this at some point in the future? Can anyone shed any light on either my lack of knowledge or if M$ is going to implement it?
Cheers,
Dunk

|||

Ditto. And runningvalue in a chart would be nice too.

|||Have you tried to create a new field that contains the RunningValue (let's call it NewField) and instead of make a Sum on the Running value you make a sum on the new field (=Sum(Fields!NewField.Value, "YourDataSet") )?
It works, as far as I can tell.|||

Is this in Report Designer?

I believe that the aggregate can show different values depending on the grouping and where on the table you put the column.

For Example, If you create a new grouping row with all of your monthly data in it and a monthly total then use the same aggregate in the row below it should give you a total of all of the monthly rows.

Don' know if i've explained this very well, it sounds better in my head!!

Hope this helps

|||Harley's right. You can NOT embed aggregate functions within each other, nor can you use a calculated field as an expression in an aggregation function. What you can do is specify at which level you want to apply the aggregate function.

For instance, if you want to add up all the sales in a grouping called salesDay, you'd do this :

RunningValue( Fields!Sales.value, sum, "salesDay" )

or salesMonth, same thing

RunningValue( Fields!Sales.value, sum, "salesMonth" )

If you want to add up all the sales for the current grouping ( if you're in the footer, for instance), you can leave the scope parameter blank:

RunningValue( Fields!Sales.value, sum )

If you place that code in the footer for your month grouping and in your table footer, i think that'll do what you need.|||

The problem I have is that one group nested inside another, and I do a CountDistinct on a column for the innermost group. This value represents a number of events that my report is supposed to track.

If I do the same CountDistinct on the outermost group, the CountDistinct is different because values that were unique within two groups are not unique within only the outermost group.

What I need to do is a CountDistinct on the innermost group and a Sum of the CountDistinct values I came up with -- I would show this in lieu of the CountDistinct for the outermost group.

But, alas, I don't see a way to do it. I'm open to suggestions if anyone has one.

|||

So are we saying that this in the grouping should or shouldn't work?

=Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value)

It works fine however try something a little more complex and we get the same error

=RIGHT(("00"&(INT(SUM(CInt((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600))),2) & ":" & RIGHT(("00" & (FLOOR((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)-INT((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)))*60)))),2) & ":" & RIGHT("00" &(ROUND(((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60)-INT((SUM(CINT((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60))))*60))),2)

Any ideas?

How to aggregate a column that was itself an aggregate

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

Below is a message:
The value expression for the textbox 'textbox13' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.


Need help.
Thanks, MickB

Dude,

did you get any luck from anyone with this? It seems that this major piece of functionality has been omitted from Reporting Services? Or am I just being thick? It's certainly not as simple and straight forward as Crystal, if it can be done at all. Will M$ be adding this at some point in the future? Can anyone shed any light on either my lack of knowledge or if M$ is going to implement it?
Cheers,
Dunk

|||

Ditto. And runningvalue in a chart would be nice too.

|||Have you tried to create a new field that contains the RunningValue (let's call it NewField) and instead of make a Sum on the Running value you make a sum on the new field (=Sum(Fields!NewField.Value, "YourDataSet") )?
It works, as far as I can tell.|||

Is this in Report Designer?

I believe that the aggregate can show different values depending on the grouping and where on the table you put the column.

For Example, If you create a new grouping row with all of your monthly data in it and a monthly total then use the same aggregate in the row below it should give you a total of all of the monthly rows.

Don' know if i've explained this very well, it sounds better in my head!!

Hope this helps

|||Harley's right. You can NOT embed aggregate functions within each other, nor can you use a calculated field as an expression in an aggregation function. What you can do is specify at which level you want to apply the aggregate function.

For instance, if you want to add up all the sales in a grouping called salesDay, you'd do this :

RunningValue( Fields!Sales.value, sum, "salesDay" )

or salesMonth, same thing

RunningValue( Fields!Sales.value, sum, "salesMonth" )

If you want to add up all the sales for the current grouping ( if you're in the footer, for instance), you can leave the scope parameter blank:

RunningValue( Fields!Sales.value, sum )

If you place that code in the footer for your month grouping and in your table footer, i think that'll do what you need.|||

The problem I have is that one group nested inside another, and I do a CountDistinct on a column for the innermost group. This value represents a number of events that my report is supposed to track.

If I do the same CountDistinct on the outermost group, the CountDistinct is different because values that were unique within two groups are not unique within only the outermost group.

What I need to do is a CountDistinct on the innermost group and a Sum of the CountDistinct values I came up with -- I would show this in lieu of the CountDistinct for the outermost group.

But, alas, I don't see a way to do it. I'm open to suggestions if anyone has one.

|||

So are we saying that this in the grouping should or shouldn't work?

=Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value)

It works fine however try something a little more complex and we get the same error

=RIGHT(("00"&(INT(SUM(CInt((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600))),2) & ":" & RIGHT(("00" & (FLOOR((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)-INT((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)))*60)))),2) & ":" & RIGHT("00" &(ROUND(((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60)-INT((SUM(CINT((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60))))*60))),2)

Any ideas?

How to add txt file in reports design

Hi
I want to add txt file in my report and it should view in Browser.
I need the expression also.
Thanks and Regards
--
asharma1. In visual studio .net 2003, click on tools and select "TEXT"
2. Use your mouse to locate and arrange the size of the area.
3. Enter or copy/paste the txt into this area.
asharma2004 wrote:
>Hi
>I want to add txt file in my report and it should view in Browser.
>I need the expression also.
>Thanks and Regards
--
I am a frog enjoying the sunshine on waikiki beach. Do you want to join with
me?!
Message posted via http://www.sqlmonster.com|||You can also give a link to this txt file using "Action" and when a user
clicks it opens in the browser.
Amarnath, MCTS
"asharma2004" wrote:
> Hi
> I want to add txt file in my report and it should view in Browser.
> I need the expression also.
> Thanks and Regards
> --
> asharma

Wednesday, March 7, 2012

How to add an "if then else" condition within an expression?

Greetings:

I am creating a complex expression in a Send Mail Task for the MessageSource. The expression contains the values of many package variables. I want to conditionally include some variables in the expression depending on what value they contain.

Is it possible to create conditional logic (if then else) within an expression? I've got to conditionally include 8 different variables.

Thanks,

BCB

Using the expression editor, you can look at the Operators. One of them is the "conditional."

This is what you want. (? Smile

your test ? if true : else false|||

Thanks, Phil.

This is part of the code I came up with to conditionally include "lines" in the body of my email message. The 2nd and 4th lines will appear only if the value of a variable is not empty. (Those are the lines that begin with Trim.) The key thing I discovered is that you need to enclose the conditional logic in parens in order for the overall expression to evaluate properly.

"The number of ESHL rows inserted was " + (DT_STR, 10, 1252) @.[User::ESHL_RowsInserted] + "." + "\n" +
(Trim(@.[User::ESHL_Error_LongMessage]) == "" ? "" : "\n" + "ESHL Message: " + @.[User::ESHL_Error_LongMessage] + "\n\n") +
"The number of Gulf States rows inserted was " + (DT_STR, 10, 1252) @.[User::GulfStates_RowsInserted] + "." + "\n" +
(Trim(@.[User::GulfStates_Error_LongMessage]) == "" ? "" : "\n" + "Gulf States Message: " + @.[User::GulfStates_Error_LongMessage] + "\n\n")