How to query on multiple proposals?

Options
Tom Wright
Tom Wright ✭
Ancient Membership Facilitator 1 Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi all,



Does anyone have any crafty ways of querying on constituents with multiple proposals?



Unusually among child record types, proposal records can't be queried upon directly (instead they must be accessed through constituent queries) and don't have any summary fields available in the query module.



When querying on proposals in a constiuent query, each criterion based on a proposal field is tested against every proposal. In other words, if I specify that I want constituents who have a proposal with a purpose of X and who also have a proposal with a fund of Y, the query will return only constituents who have a proposal that matches both criteria. This is often the desired behaviour and I think it's right that it's the way RE works by default.



On occasion though, it would be really useful to identify constituents who have one proposal with one value and another proposal with another value. For example, I often think it'd be useful to find people who have multiple proposals in different statuses. Naively, we might try the following:



image

As we'd expect, this returns 0 records. Is there a way to make this sort of query work?



Cheers,

Tom
Tagged:

Comments

  • James Andrews
    James Andrews ✭
    Ancient Membership Facilitator 2 Photogenic
    Merging queries is the way to get around this.  You'd do a query for people with a proposal in Scheduled review, and a second query for people with a proposal in Research.  Then merge them using AND.
  • Tom Wright
    Tom Wright ✭
    Ancient Membership Facilitator 1 Photogenic
    Hi James,

    James Andrews:

    Merging queries is the way to get around this.  You'd do a query for people with a proposal in Scheduled review, and a second query for people with a proposal in Research.  Then merge them using AND.

    Yup, I can see this working for one-off jobs. Problem is when we want to access this data as a dynamic query (e.g. for a dashboard panel).



    Are there any other workarounds lurking anywhere?
  • James Andrews
    James Andrews ✭
    Ancient Membership Facilitator 2 Photogenic
    Not that I know of.  If you look over here: https://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/2861856-make-proposals-a-record-like-gifts-that-can-be-exp you can see that people have been asking about this sort of thing for years now, and every few months someone comes upon the suggestion and adds another vote/comment in support of it.  Nothing doing yet though.



    If you have access to the back end of RE and know some SQL it's not super difficult to do, but then you have a whole other set of issues with reporting and whatnot.
  • James Andrews
    James Andrews ✭
    Ancient Membership Facilitator 2 Photogenic
    Just had another thought. What if you created those various static queries, and then used Queue to refresh them nightly?  Would that be dynamic enough?
  • Tom Wright
    Tom Wright ✭
    Ancient Membership Facilitator 1 Photogenic
    Dipping directly into the database is the approach we take currently. (Our reporting stack is based on SSRS.)



    Using queue to refresh merged queries is a neat trick - will have to give that a try.



    Thanks James!
  • Steven Cianciarulo
    Steven Cianciarulo ✭
    Tenth Anniversary Facilitator 1 Name Dropper
    I'm having this issue as well -- are you using RODBA, or some other solution?
  • Tom Wright
    Tom Wright ✭
    Ancient Membership Facilitator 1 Photogenic

    Steven Cianciarulo:

    I'm having this issue as well -- are you using RODBA, or some other solution?

    Not really - we just use SQL Server Management Studio (SSMS) to explore the data and build queries. Then we plug these queries into SQL Server Reporting Services (SSRS) to expose them as reports. This is probably the "nuclear" option for most organisations, but since this is our approach to reporting across the board it makes a lot of sense for us.

Categories