Report Design: Stop Filter

Stop Filter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9108
Printed Date: 01 May 2024 at 7:13pm

Topic: Stop Filter

Posted By: AdamField
Subject: Stop Filter
Date Posted: 12 Feb 2010 at 10:05am

Hey all,

Got myself another problem with CR

I got 1 BIG db with all the articles we have in our shop and where we can buy them called MULLEV

Artikel Supply Code Supply Name

100 A5486 Techdata

100 26810 Ingram

100 GFR512 supplierX

103 ....

got another view of 4 DBO's in SQL where i have set

SELECT DBO.[suppliername].supplyCode AS SupplyCODE, "TD" as Supply

FROM DBO.[suppliername]

UNION ALL

SELECT DBO.[suppliername].supplyCode AS SupplyCODE, "IM" as Supply

FROM DBO.[suppliername]

...

In crystal Report i linked those 2 on supply Code

What im trying to get is a list of all the supplycode's from the MULLEV dbo where the mullev sais we can buy it from supplyier X but the supply code isn't in that second DBO any more

Basicly the main problem is that my first dbo comes from the program we use to buy stuff and we link supplier's in there.

Now when a supplier stops suppliying that artikle the link in my main program is still alive

The report should show a list of all the codes that are alive in my main program but aren't alive (aka can't be found) in my second DBO...

Hope somebody can help

Tnx in advance !!

Adam

Replies:

Posted By: DBlank
Date Posted: 12 Feb 2010 at 11:39am

Let me see if I understand.

You have a table MULLEV that has 1 row of data per SupplyCode and SupplyName

You have 4 other tables (1 per supplier) that has is a 'current list of items avaliable from that supplier' so it is 1 row per supplycode.

Disregarding your UNION process here and limiting it to one supplier would you outerjoin the 2 tables together on both supply code and supply name and then look for NULL table2.supplycode values to get a list that you want (for just that 1 supplier) or is there something I am missing?

Posted By: AdamField
Date Posted: 12 Feb 2010 at 10:41pm

Hey DBlank

The mullev part is correct, but beside the suppycode and supplyname it also has current stock value's, price , tax info , ....

the 4 other's are the files we get from the supplier himself

that one has

product number / suppliercode / price / stock / taxinfo / ....

becouse i only need the code andthe name i use a union all to get 1 BIG list of all the supplycodes and the cupply name

this i make on the SQL server in a view

then in CR i load my mullev DB and the view

This in the hope to make a list of all the supply codes availeble in the mullev but not in the view (aka EOL products)

Adam

Posted By: DBlank
Date Posted: 13 Feb 2010 at 9:43am

What i do not understand though is do suppliers share supplycodes in the MULLEV table?

Also since the MULLEV table holds all the products how do you know which ones 'belong' to each supplier? IS that the value in the Supply Name?

If each row in the MULLEV is per supplier identified via the SupplyName field then do an outerjoin between your tables selecting all rows from MULLEV.

Group on MULLEV.SupplyName

Select ISNULL(table2.Supplycode)

Display MULLEV data field for names, codes, etrc.

Is that what you wanted?

Posted By: AdamField
Date Posted: 15 Feb 2010 at 2:20am

Hey DBlank,

This is exactly what i want, just trying to get it into CR ...

I did the Database expert and linked both mulled and my view on the supply code with a left join (can't slect outer join in CR)

but the moment i drag my view.supplycode he links perfect nice, he shows all the linked artikels, problem is i need the not linked ones :p

Adam

Posted By: AdamField
Date Posted: 15 Feb 2010 at 2:28am

Hey DBlank,

Got it working, i should look sometimes before i ask :p

Tnx for the fix !!

Adam