Excluding Proposals in Crystal Reports

Options
Justin Norris
Justin Norris ✭
Tenth Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
Good afternoon, Colleagues, 


I thought I would reach out to you for some guidance.  I have been building crystal reports, but there will be another user on the reports.  I have been attempting to use Queries as a way to change some of the information on the reports. 


Right now, I have created a report for proposals.  My problem is that miscellaneous proposals continue to show up.  


For example, I will query for all proposals with a Date Expected between 7/1/18 - 6/30/19. (FY19)  The report outputs any proposal for that year and any other proposals linked to that individual.  For example, Jane Doe has a proposal for FY19, FY20, FY21.  All three proposals show up on Crystal Reports.  


I have tried to manipulate the data via a static query and then link it to the main query for the export.  That has not worked.  I also tried to manipulate the links from left outer join to other options - and that has not worked. 


I would love to hear if anyone has had any success with this themselves.  My static query says that I have 44 proposals, but my reports continues to show 63.  


I appreciate your help.


Best regards,


Justin


Justin D. Norris
Tagged:

Comments

  • Alan French
    Alan French ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    This is because there is no "proposal" query type in RE. When you say you're querying all proposals with a Date Expected between 7/1/18 - 6/30/19, what you're really doing is (assuming you're using a constituent query) creating a query of all constituents who have a proposal between these dates. When you use this query with an export, the query doesn't tell the export anything about your proposal date range, only a list of the constituents that need to be included, so the export then includes all of the proposals for that group of constituents.


    The only solution I could come up with for my own reports was to add some date parameters to Crystal and prompt the user to re-select the date range, so I'd be interested to hear if anyone can come up with a more elegant suggestion.
  • John Heizer
    John Heizer ✭
    Ancient Membership Facilitator 3 Photogenic

    Alan French:

    This is because there is no "proposal" query type in RE. When you say you're querying all proposals with a Date Expected between 7/1/18 - 6/30/19, what you're really doing is (assuming you're using a constituent query) creating a query of all constituents who have a proposal between these dates. When you use this query with an export, the query doesn't tell the export anything about your proposal date range, only a list of the constituents that need to be included, so the export then includes all of the proposals for that group of constituents.


    The only solution I could come up with for my own reports was to add some date parameters to Crystal and prompt the user to re-select the date range, so I'd be interested to hear if anyone can come up with a more elegant suggestion.

    That's the same solution I have used.

  • Justin Norris
    Justin Norris ✭
    Tenth Anniversary First Reply

    Alan French:

    This is because there is no "proposal" query type in RE. When you say you're querying all proposals with a Date Expected between 7/1/18 - 6/30/19, what you're really doing is (assuming you're using a constituent query) creating a query of all constituents who have a proposal between these dates. When you use this query with an export, the query doesn't tell the export anything about your proposal date range, only a list of the constituents that need to be included, so the export then includes all of the proposals for that group of constituents.


    The only solution I could come up with for my own reports was to add some date parameters to Crystal and prompt the user to re-select the date range, so I'd be interested to hear if anyone can come up with a more elegant suggestion.

    Thanks Alan and John, you both confirmed what I feared to be the case.  :)  Where did you prompt the user to re-select the date range?  Query? Export?  Thanks again! JD

  • Alan French
    Alan French ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    The first prompt is within the query, and the second prompt is in the Crystal report, not within RE. I set up 2 parameter fields for the start and end dates, then used these in a selection formula so that only proposals between those dates were included in the report.
  • Deb Dressely
    Deb Dressely ✭
    Ninth Anniversary Facilitator 1 Name Dropper Photogenic

    John Heizer:

     

    Alan French:

    This is because there is no "proposal" query type in RE. When you say you're querying all proposals with a Date Expected between 7/1/18 - 6/30/19, what you're really doing is (assuming you're using a constituent query) creating a query of all constituents who have a proposal between these dates. When you use this query with an export, the query doesn't tell the export anything about your proposal date range, only a list of the constituents that need to be included, so the export then includes all of the proposals for that group of constituents.


    The only solution I could come up with for my own reports was to add some date parameters to Crystal and prompt the user to re-select the date range, so I'd be interested to hear if anyone can come up with a more elegant suggestion.

    That's the same solution I have used.

     

    I've added another criteria to the query - AND Prospect Proposal Is Inactive equals No.  Old proposals are marked inactive and they no longer appear in my current year query.

  • Maya Francis
    Maya Francis ✭
    Tenth Anniversary Facilitator 1 Photogenic

    Justin Norris:

    Good afternoon, Colleagues, 


    I thought I would reach out to you for some guidance.  I have been building crystal reports, but there will be another user on the reports.  I have been attempting to use Queries as a way to change some of the information on the reports. 


    Right now, I have created a report for proposals.  My problem is that miscellaneous proposals continue to show up.  


    For example, I will query for all proposals with a Date Expected between 7/1/18 - 6/30/19. (FY19)  The report outputs any proposal for that year and any other proposals linked to that individual.  For example, Jane Doe has a proposal for FY19, FY20, FY21.  All three proposals show up on Crystal Reports.  


    I have tried to manipulate the data via a static query and then link it to the main query for the export.  That has not worked.  I also tried to manipulate the links from left outer join to other options - and that has not worked. 


    I would love to hear if anyone has had any success with this themselves.  My static query says that I have 44 proposals, but my reports continues to show 63.  


    I appreciate your help.


    Best regards,


    Justin


    Justin D. Norris

    In query you can group all the constituents who have proposals that meets the date range you are looking for and in the Crystal Report using the Select Expert menu you can specify the date range of proposals you would like to see.
  • Graham Getty
    Graham Getty ✭
    Ancient Membership Facilitator 2 Name Dropper Photogenic
    Easiest thing is to make sure all your old proposals are marked Inactive in database. This won't stop export from exporting inactive proposals, but you can then just add the Proposal is inactive field to the export and then add a select expert to your crystal report that will only show active proposals. Once you do that, all should be well.

Categories