Conditional SUM?

I have a table that lists both "Buy" and "Sell" orders. I want to display a
SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
conditional SUM the rows based on a column value in reporting?
Thanks,
JeffOn Apr 30, 11:22 am, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> I have a table that lists both "Buy" and "Sell" orders. I want to display a
> SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
> conditional SUM the rows based on a column value in reporting?
> Thanks,
> Jeff
Generally, accessing table control data in a footer is very
restrictive. You can either try to access the datasets directly or
access the dataset(s) via hidden parameters. You might be able to
access the dataset from the footer w/: =iif(Max(Fields!
SomeFieldName.Value, "SomeDataSetName") = "Buy", Sum(Fields!Buy.Value,
"SomeDataSetName"), Sum(Fields!Sell.Value, "SomeDataSetName"))
-or-
=iif(Parameters!SomeParameterName.Value = "Buy", Sum(Parameters!
Buy.Value), Sum(Parameters!Sell.Value))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for your reply. Unfortunately, the example you provided would not
work for me. To apply your first example to my case, it would read like this:
=iif(Max(Fields!SomeFieldName.Value, "SomeDataSetName") = "Buy",
Sum(Fields!Quantity.Value, "SomeDataSetName"), Sum(Fields!Quantity.Value,
"SomeDataSetName"))
This obviously would NOT work since I only have one Quantity column, nor
would it work if I put a value of zero for the false condition.
As for your second example, I do not see how summing up parameters would
help when what I need is summing up of row values. Perhaps you can elaborate
on this?
"EMartinez" wrote:
> On Apr 30, 11:22 am, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> > I have a table that lists both "Buy" and "Sell" orders. I want to display a
> > SUM of all Buys and a SUM of all Sells in the footer. Is there a way to
> > conditional SUM the rows based on a column value in reporting?
> >
> > Thanks,
> > Jeff
>
> Generally, accessing table control data in a footer is very
> restrictive. You can either try to access the datasets directly or
> access the dataset(s) via hidden parameters. You might be able to
> access the dataset from the footer w/: =iif(Max(Fields!
> SomeFieldName.Value, "SomeDataSetName") = "Buy", Sum(Fields!Buy.Value,
> "SomeDataSetName"), Sum(Fields!Sell.Value, "SomeDataSetName"))
> -or-
> =iif(Parameters!SomeParameterName.Value = "Buy", Sum(Parameters!
> Buy.Value), Sum(Parameters!Sell.Value))
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Conditional Line Breaks

There has to be a simple solution to this, but I can't find one. I have several fields dropped onto a report which display the relevant company address for that customer. But, some of the addresses don't have the full itinary, some may not have state or country where other may have. How can I implement a conditional statement which will remove remove the line break if the content is present. Or vice versa if no content is there, how can I not display that field?

Thanks in advanceIn the suppress event write this

If IsNull({filed}) then True;

conditional grouping (to group or not to group)

I have 3 groups: region, district, facility.
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>

Conditional Formatting - not so conditional?

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .What part of the matrix do you have this in, and what values show in the textbox?|||OK, well you pointed me right to my problem. The values are percentages. I was doing my conditional formatting based on 3 instead of .03 . . .

That is fixed but I still have something else wrong. A very small number of values that are not .03 are coloring red. I am also getting a warning that says "[rsNonAggregateInMatrixCell] The Color expression for the textbox ‘textbox22’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling."

I assume this message is related to my problem.

Here is a sample of what the output is looking like. All of the values formatted correctly except for the 2.60 %. The 2.60% was colored red as if it was >= .03. I know you can't see the colors here:

WRAP

3.50 %

1.22 %

1.15 %

3.13 %

2.43 %

2.60 %

14.21 %

0.00 %

8.41 %

6.14 %

5.23 %

5.23 %

2.42 %

2.42 %

1.87 %

1.87 %

2.39 %

2.39 %

4.17 %

4.17 %

3.15 %

|||

A matrix cell is always in the intersection of a row and a column group. Usually, several detail rows will go into the same matrix cell instance based on the row/column grouping. That's the reason why you get the warning. By just referencing a field without aggregate function (=iif(Fields!Wrap.Value >= 0.03 , "Red", "Green") ), you reference just the field value of the first row in that cell - while the textbox in the cell may actually show the aggregate of the values.

You should use the following color expression instead: =iif(Sum(Fields!Wrap.Value) >= 0.03 , "Red", "Green")

-- Robert

|||I made the change that you suggested. Which eliminates the warning that I was receiving. However, I am still getting inconsistent results with the formatting. For instance, of the following values only 5.09, and the two 0.00's are colored green.

Could it be because I have enabled drill-down in this report? Even if I drill down to the lowest level the colors still are not consistently populating based on the conditions in this formula.

WRAP

5.09 %

2.73 %

0.00 %

0.00 %

1.77 %

2.55 %

3.61 %

6.68 %

3.02 %

3.50 %

2.86 %

Conditional Execution based on Multi-Valued Parameter Selection

If a user selects all (266) parameter values from a mult-valued parameter list, I would like to display a table based on a version of a stored-proc that does not select records using parameter driven criteria.

The total selected count can be acquired by =Parameters!ParmName.Count syntax. Total available parameter values can be extracted by =CountDistinct(Fields!ColName.Value,"DatasetName").

Setting a filter on a table and inspecting the total selected count can limit the number of values executed for the query.

That is great, but I would also like to run an efficient query if all values were selected. Thought I could set a filter on a table and compare these two values, but I can't use an aggregate.

Any ideas?

Decided to create a one row dataset (query) containing a count of the number of values to select from. Used this value in a hidden parameter "PrimaryListCount" and used the new dataset to populate the default report parameter value. Then I determined if all values were selected using the following table filter:

=CInt(Parameters!PrimaryNames.Count) = =CInt(Parameters!PrimaryListCount.Value)

If you have a better solution, I'd like to know about it. If not, hope this can help someone else.

Conditional Displaying

Hi all,

I am using Crystal reports XI R1 . Here is the problem, I have an integer field (x), i need to display three more fields (a,b,c) based on that integer field (x) when it is maximum.

for example
when max(x) then i need to display the corresponding records of a, b, and c.

Any help would be greatly appreciated.

Thanks in advance
Sudharsan.What is the database?
I think you need to write the query

Select * from table
where x=(select max(x) from table)

and design the report using that query

Conditional Display of report Header/footer data

Is visibility of the header/footer regions able to be turned on off
based on some expression?Yes. Select the header or footer whichever you'd like, and find the
Visiblity/Hidden section, choose "expression" from the drop down, or simply
type your expression in the space provided. This will be evaluated at
runtime and set the visibility to its proper state.
Michael
"Weston Weems" wrote:
> Is visibility of the header/footer regions able to be turned on off
> based on some expression?
>

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

Conditional display

This is a multi-part message in MIME format.
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
How do I get a row to display conditionally? For example below - I = don't want the shirts row to display because it's zero. I'm using = Report Builder against a Cube:
Products: Qty:
shoes 5
shirts 0
gloves 10
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
How do I get a row to display = conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:

Products: = Qty:
shoes  = ; 5
shirts = 0
gloves = 10

--=_NextPart_000_006A_01C7E655.2B232280--On Aug 24, 3:46 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> How do I get a row to display conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:
> Products: Qty:
> shoes 5
> shirts 0
> gloves 10
I'm not sure if this will work in your scenario, but in a standard
SSRS report you could use an expression like this in the Value
property of the table cell/etc:
=iif(Fields!Qty.Value = 0, Nothing, Fields!Qty.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Concatinating two field and insert the result

Hii,
I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?
use northwind

select city, region,([city]+ +[region]) as uniqe
from customers
where region is not null
The resulting records in Quary
Anchorage AK AnchorageAK
Tsawassen BC TsawassenBC
Vancouver BC VancouverBC
San Francisco CA San FranciscoCA

Try it like this:

UPDATE
Customers
SET
city = city + ' ' + region
WHERE
region IS NOT NULL

Concatenation of two columns

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.This is only a guess, but:SELECT RTrim(first_name) + ',' + last_name
FROM Address-PatP|||Thank you for the prompt reply, it worked!