Showing posts with label orders. Show all posts
Showing posts with label orders. Show all posts

Monday, March 19, 2012

Conditional suppression formula PLEASE HELP

I have Crystal reports 8.5 and Im trying to generate a report that will give all of my sales orders with the part numbers price and the address they shipped to but I keep getting alot of duplicate fields so I used a suppresion forumla below

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber})

That did a great job of eliminated the duplicates but I found out that I have some orders that are in succesion with identical part numbers and they were being suppressed so I tried this formula

{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

thinking that will suppress any field that has the same part number and so number but it errors our and tells me I need a boolean.

I've tried just about everything and Im sure im missing something very simple but I dont know what it is.

PLEASE HELP{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

The very first thing is try to find out why you are getting duplicate data. Something must be wrong with you linking.

To suppress try this:

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber}) AND {InvoiceHeader.SONumber} = previous({InvoiceHeader.SONumber})

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
>

Saturday, February 25, 2012

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Sunday, February 12, 2012

Concatinating Select Results

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrders
SET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

There are probably a number of ways of doing this. The first thing that occurs to me is to use an asynchronous script component that takes a set of data (ordered by ReqNum). Inside the script component loop over the set of data, concatenating comments for each ReqNum.

-Jamie

|||Hey Jamie,

Thanks for the response. I'm very new to SSIS so that's a little over my head. Could you elaborate a little more on all that? Or can you think of an easier way of accomplishing this?

Thanks

Lee.