Print Page | Close Window

Showing non-matched parameter values on report

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=23021
Printed Date: 05 May 2024 at 3:08pm


Topic: Showing non-matched parameter values on report
Posted By: brain
Subject: Showing non-matched parameter values on report
Date Posted: 06 Oct 2022 at 5:15am
A user had an odd request: he wants to not only filter by the multi-value parameter, but also indicate when any of those values is not found in the source data.

That is, I already write a report for which the user enters a list of PO numbers in the multi-value parameter, and the report correctly brings up all orders for those PO numbers. So far so good.

But now the users wants to also indicate on the report if the user entered a PO number that does not exist in the database. If this were SQL, I would use a "...WHERE PONumber Not In (Select PONumber from [SourceDataTable]).

Is there a formula or SQL Expression I can write to determine which of the multi-value values does not exist in the source table.


Replies:
Posted By: hilfy
Date Posted: 03 Jan 2023 at 10:35am
I realize this question is several months old and you have probably found an answer already, but here's how I would do it.

Because multi-select parameters store their selections in an array, you might do something like this: 

WhileReadingRecords

StringVar array foundPOs;

Local Numbervar i;

If OnFirstRecord then

(

  foundPOs := MakeArray({PO Field})

)

Else

(

  if not {PO Field} in foundPOs then

  (

    i := uBound(foundPOs) + 1;

    redim preserve foundPOs;

    foundPOs := {PO Field};

  );

);

""


Put this on the report in the same section where you show each PO number (PO group header?) The "" at the end means it won't display anything, but it will calculate.

You'll then create another formula to display a list of the selected POs that were not found. It would look something like this: 


whileprintingrecords;

Stringvar array foundPOs;

Stringvar notFoundPOs := "";

Local Numbervar i;

If uBound({?PO Parameter}) = uBound({foundPOs}) then

  notFoundPOs := "All selected POs were found"

Else

(

  for i := 1 to uBound({?PO Parameter})

  (

    if not ({?PO Parameter} in foundPOs) then

      notFoundPOs := notFoundPOs + ", " + {?PO Parameter};

  );

);

right(notFoundPOs, length(notFoundPOs) - 1_


This should put the POs that aren't found in a comma-delimited list that you can put in either the report header or report footer.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: brain
Date Posted: 07 Jan 2023 at 11:13pm
Thank you, Dell. That got me headed in the right direction, although I beat my head against the wall very seriously for many hours (well over 12!). I think I tried every possible wrong syntax until I finally got past five things:

     1. I had to add ";" after WhilePrintingRecords in order to get anything to work.
     2. The "This function cannot be used because it must be evalutaed later" error when using OnFirstRecord in the context of WhileReadingRecords. For many hours, I went hither and yon trying to figure out some workaround, some substitute for OnFirstRecord that would work with WhileReadingRecords. In the end, I gave up on that and came back to start again on the theory that this might be available only after the records are put into print order, that is, within WhilePrintingRecords. Sure enough, that got me past that error. So then I was able to progress to the jumping through the three additional hoops below.
     3. This line tripped me up: "foundPOs := {PO Field};". Crystal indicated it was required to be subscripted. So I did this instead: "foundPOs:= {PO Field};" Ah, so small a change, and I thought I had it then, but then it just led me to the next issue.
     4. Once I subscripted #2 above, it made me subscript this as well: foundPOs := MakeArray({PO Field}). Since it occurs on the first record only, I could not find a way to subscript it within the MakeArray function that would not generate an error, so I finally worked out this: "FoundPOs[1]:= {Command.PO#};" That got me past that subscript deficiency, and then the formula had no errors. Enter #4.
     5. But when I ran the report, I got an error indicating that the hard-code 1 was not within the bounds of the array. So, and this works, although I still do not know enough about arrays to know why I had to do this, I resorted to redim on the array to a size of 1 immediately before that: "redim FoundPOs[1];"

In the end, my code looks like this with my actual variables:

Formula for the detail section (I have no group header)
     WhilePrintingRecords;
     StringVar array FoundPO;
     Local Numbervar ArrayCounter;
     If OnFirstRecord then
          (
               redim FoundPO[1];
               FoundPO[1]:= {Command.PO#};
          )
     Else
          if not ({Command.PO#} in FoundPO) then
               (
                    ArrayCounter:= uBound(FoundPO) + 1;
                    redim preserve FoundPO[ArrayCounter];
                    FoundPO[ArrayCounter]:= {Command.PO#};
               );
     ""

Formula for the footer
     WhilePrintingRecords;
     StringVar array FoundPO;
     Local Numbervar ArrayCounter;
     Local StringVar PONotFound:="";
     if ubound(FoundPO) = ubound({?PONumbers}) then
          PONotFound = "All PO numbers were matched"
     else
          for ArrayCounter:= 1 to uBound({?PONumbers}) do
               (
                    if not ({?PONumbers}[ArrayCounter] in FoundPO) then
                         if PONotFound = "" then
                                   PONotFound:= "PO numbers not found" + chr(10) + {?PONumbers}[ArrayCounter]
                         else
                              PONotFound:= PONotFound + chr(10) + {?PONumbers}[ArrayCounter];
               );
     PONotFound


One thing still mystified me entirely: how is the FoundPO array in my footer-section formula aware of the contents of the FoundPO array from the other (detail) formula. I assumed that arrays existed only within the context where used (within the formula) and/or that declaring the same array in another formula would clear the array contents. But it does work correctly. Is there an implied shareability among formulas as long as they use the same variable type and name and one does not set values that override the other?


Print Page | Close Window