Filter Business Contacts via Accounts?

[lostmyshape]

In BCM for Outlook2007, is there a way to filter Business Contacts with
criteria from their associated Account? Here's an example of what I'm trying
to do:

I have Account records with a field that is marked either "client" or
"prospect." I want to get a report of all the Business Contacts from
Accounts marked "client."

When I filter an Account report, I can only show Primary Contacts. But,
when I filter a Business Contact report, I can't filter on criteria from the
parent Account because that particular Account field isn't part of the
Business Contact entity.

Any work around for this?

 

[lostmyshape]

Does anyone know a way to perform a more robust search than is possible with
the built-in reports? The report section is pretty good, but won't do a
couple things that I need.

Will Crystal Reports or some other software interact with the BCM database?

 

[Leonid S. Knyshov]

Does anyone know a way to perform a more robust search than is possible
with
the built-in reports? The report section is pretty good, but won't do a
couple things that I need.

Will Crystal Reports or some other software interact with the BCM
database?

Crystal should work, and so will SQL Reporting Services (included free with
SQL Server 2005) or any other standard SQL reports tool.

Are you using BCM 2003 or BCM 2007? 2007 is very customizable as far as
reports are concerned.
--
Leonid S. Knyshov, CEO
Crashproof Solutions, LLC - http://www.crashproofsolutions.com
MCP Exchange 2003/Small Business Server 2003
Microsoft Small Business Specialist Partner
See the tips and tricks section on my website for video tutorials on BCM
Send a smile to Microsoft (Office 2007 Beta feedback tool)!
http://tinyurl.com/m4omy

 

[lostmyshape]

Thanks... I'm using 2007 and the reports section is very good, but will not
let me sort "Business Contacts" using criteria from the Account record the
Contact is associated with.

So, I can filter out the Accounts that I want, and see the Primary Contact
for that Account, but cannot see ALL the Contacts associated with that
Account.

Seems like something that would be pretty easy to make happen with a SQL
statement. Can you edit BCM 2007 reports with SQL code? If not it looks
like i'll just have to resort to another Reporting software.

 

[Leonid S. Knyshov]

Thanks... I'm using 2007 and the reports section is very good, but will
not
let me sort "Business Contacts" using criteria from the Account record the
Contact is associated with.

So, I can filter out the Accounts that I want, and see the Primary Contact
for that Account, but cannot see ALL the Contacts associated with that
Account.

Seems like something that would be pretty easy to make happen with a SQL
statement. Can you edit BCM 2007 reports with SQL code? If not it looks
like i'll just have to resort to another Reporting software.

You don't need a report to get a list of all business contacts associated
with all accounts.

Go to Contacts, Business Contacts
Click View -> Arrange by -> Show in Groups
Click View -> Current View -> By Accounts
Then click on any account, right-click and choose Collapse All or Expand All
as necessary.
--
Leonid S. Knyshov, CEO
Crashproof Solutions, LLC - http://www.crashproofsolutions.com
MCP Exchange 2003/Small Business Server 2003
Microsoft Small Business Specialist Partner
See the tips and tricks section on my website for video tutorials on BCM
Send a smile to Microsoft (Office 2007 Beta feedback tool)!
http://tinyurl.com/m4omy

 

[lostmyshape]

I know I can do that. You don't understand what I'm trying to do. I want to
filter the Account listings by a custom field (type of client) that I've
added. But I don't want to see the Account information. I want to see ALL
of the Business contacts that are associated with that account. Currently,
the reporting section only allows you to see the Primary associated with that
account.

I can get the view you're talking about, but with over 1,000 Account
listings, I don't want to have to go through the list by hand and pick out
the accounts that I don't want. I need to filter the Accounts and then see
their associated contacts.

This doesn't seem possible with the reports module in BCM. I think the SQL
Server 2005 "Reports Builder" will do it, but I don't want to spend $7,000 to
upgrade SQL Server just to get the Report Builder (especially when I can
spend less than 1/3 that to get a handful of Crystal Reports licenses). Is
it possible to purchase the Reports Builder module to work with the SQL
Server that comes with BCM?

Right now I'm messing with it in Access, but it doesn't seem to be able to
import the associations, so it's difficult picking through the codes that SQL
Server gives fields for the associations.

 

[Leonid S. Knyshov]

I know I can do that. You don't understand what I'm trying to do. I want
to
filter the Account listings by a custom field (type of client) that I've
added. But I don't want to see the Account information. I want to see
ALL
of the Business contacts that are associated with that account.
Currently,
the reporting section only allows you to see the Primary associated with
that
account.

I can get the view you're talking about, but with over 1,000 Account
listings, I don't want to have to go through the list by hand and pick out
the accounts that I don't want. I need to filter the Accounts and then
see
their associated contacts.

That custom field should be available in your field chooser in the view
builder and filter. I don't think you need to run a report to produce this
view. I can probably show this to you in a couple of minutes.
--
Leonid S. Knyshov, CEO
Crashproof Solutions, LLC - http://www.crashproofsolutions.com
MCP Exchange 2003/Small Business Server 2003
Microsoft Small Business Specialist Partner
See the tips and tricks section on my website for video tutorials on BCM
Send a smile to Microsoft (Office 2007 Beta feedback tool)!
http://tinyurl.com/m4omy

 

[lostmyshape]

Do you understand what I'm trying to do? I don't think that you do. OK,
I'll explain for the 3rd time: I have NO problem filtering with my custom
field. I pick account reports and filter out what I want. NOW, I want to
see EVERY Business Contact associated with the accounts that I've just
filtered with my custom field. This seems impossible, with the built-in
Reporting. OR, I can run the "Business Contacts by Account" Report and see
every contact that is associated with EVERY account. But then I can't filter
with a custom field from an account data type, only with fields from the
business contact data type.

If you see a way to do this, I'd be really grateful, but I just don't think
it's possible with the built-in reporting.

Am I wrong?

 

[lostmyshape]

Oh... my appologies... I see what you're saying. You might not be able to
make it happen in the reports, but you can build a "view" to see this. I've
tried that, too. In fact, I've tried to edit the SQL in the custom view
builder, but with no success. Is it really possible this way? I can't make
it work.

 

[bcmtest]

Could you explain how to do what you describe in this and your previous post.
When I attempt to create the view from business contact by account, I get all
of the contacts listed by account. However, I then want to filter the view
further by only viewing those contacts that are linked to accounts that have
a specific user-defined field selected.

The issue I am having is only fields for business contacts are showing up
when attempting to filter. The user-defined fields that are assigned to
accounts don't show up for filtering. Is there a way to write it in SQL to
filter based on business_contact->account->account_user_defined_fieldXXX =
yes?