I have to filter out records in either Crystal Reports Dev 8.5 or SQL Server 2008 Stored Procedure

I have a report that is getting in a field called {Prod_Cat) for Product Catagory. I have to pad the values for some to identify what Prod_Cat in the Crystal Report which I successfully completed thanks to some brilliant EE members.

Now they tell me they want to get rid of some of the groups that show up. They are in the top of the list and at the very bottom. I need to remove 000000 and the INV , R13 , ZRW. Anything that starts with a 4 can stay. All others need to go. The funny thing is I can remove it from the report on the right but not the grouping on the left. Does anyone know what can be done to correct this issue.

See image.

The question. I cannot seem to get it removed from Crystal Reports......How could I remove it from the stored procedure so it never even shows up for Crystal Reports to handle. Your advice greatly apprciated. Thank you.
GroupListCombined.PNG
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
How are you filtering it from the report?

mlmcc
0
Brian CroweDatabase AdministratorCommented:
You should be able to use the Report -> Selection Formulas -> Group... to filter out what groups you don't want to see.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
That method leaves them in the group tree.  The group filter is simply suppression from the report.

Can you add a filter to the report like

Left({YourField},1) = '4'

mlmcc
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mlmccCommented:
DO you need the information from those groups in the report for overall totals?
If so then they will always appear in the group tree.  You could create a formula like

If Left({YourField},1) = '4' then
   {YourField}
Else
  " "

That would put them all in one group and the tree would show a blank entry

mlmcc
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
mlmcc
I used those exact formulas you posted on my own before you posted them ..... ( good to know I am on track.) Neither of which worked in Records Selection and on the Formula Editor using it as a Boolean control to get the results removed. Noting worked.
0
mlmccCommented:
WHat is in the report filter?

mlmcc
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Tried the Group Expert and nothing worked there either.
I thought at one time that the difference might be where
{prod_cat} = "000000"
or
Not NumericText({prod_cat})

might do it but the groups are still listed up on the left hand side of the screen.
The report won't show it but the group on the left still does. So users might be tempted to click on the groups that should not be visible and try to drill into them....
Any thoughts appreciated.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
The {@ProdCat4Only} is the formula we discussed that has shown the
Left({YourField},1) = '4'.......and the afore mentioned formulas which did not work.
You are making me think maybe I should move the formula up higher in the record selection......? Am I on to something....?
 

{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.inv_dt} in {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.CurrFromDateYTD}
to {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.CurrToDateYTD} or
{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.inv_dt} in {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.PriorFromDateYTD}
to {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.PriorToDateYTD} and

{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Item_no} in {?ItemRange} and
{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.CustNo} in {?CustomerRange}
and
//(if {?GL Type_Prod_cat} = "ALL" then true else {?GL Type_Prod_cat} = {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.prod_cat})
//and
if {?RptTerr} = "ALL" and {?AdoptedTerr} = "ALL" then
{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in "00" to "ZZ"
else
if {?RptTerr} = "ALL" and {?AdoptedTerr} <> "ALL" then
   ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in "00" to "ZZ" and {@TimsTerritory} )
      or ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr}in {?AdoptedTerr} and {@AdoptedTerritory} and {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.TerrAdopted} in "99" to "ZZ")
   else
        if {?RptTerr} <> "ALL" and {?AdoptedTerr} = "ALL" then
        ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in {?RptTerr} and {@TimsTerritory})
         or ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in "00" to "ZZ" and {@AdoptedTerritory} and {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.TerrAdopted}in {?RptTerr})
        else
        ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in {?RptTerr} and {@TimsTerritory} )
          or ({Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.Terr} in {?AdoptedTerr} and {@AdoptedTerritory} and {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.TerrAdopted} in {?RptTerr} ) and
{@ProdCat4Only}
0
mlmccCommented:
Try putting ( ) around the first part  

(
{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.inv_dt} in
...
to {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.PriorToDateYTD} )
AND

mlmcc
0
mlmccCommented:
YOu also need ( ) the if or easier would be to move the last line to the top

{@ProdCat4Only}
AND
(
{Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.inv_dt} in
...
to {Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB;1.PriorToDateYTD} )
AND
Rest of your formula except the last 2 lines

The issue is how the formula is parsed.  BY having it the end it is evaluated only as part of the final ELSE clause.

mlmcc
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I am also wondering if a Stored Procedure would remove the records before Crystal Even had a chance to do anything with it.

Might be worth a try if anyone can point the code out that would keep the SP from passing the values to CR......?
0
mlmccCommented:
Left(YourField,1) = '4' added to the SP WHERE clause would do it

You would have to adjust it to match the SQL for your database

Oracle

substr( YourField,1,1) = '4'

MS SQL/MS Access

Left(YourField,1) = "4"


mlmcc
0
James0628Commented:
Going back to something that you said earlier:

 > The report won't show it but the group on the left still does. So
 > users might be tempted to click on the groups that should not
 > be visible and try to drill into them....

 When you use group selection, the "non-selected" (suppressed) groups will still be visible in the group tree, but if you click on one of them, CR just takes you to the first selected group before or after that one (I'm not sure which) in the report.  The suppressed groups still won't be visible in the report, and the user won't have any way to "drill" into them and make them visible.  I just wanted to make sure that you realized that.


 > I am also wondering if a Stored Procedure would remove the
 > records before Crystal Even had a chance to do anything with it.

 If the report is reading the tables directly, then getting the right test in the record selection may accomplish the same thing.  CR would pass the test to the server, which would filter out the records that you don't want, and only send the remaining records to the report.

 But if Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB is a stored procedure (or view), then anything that you put in the record selection will probably have to be done "locally".  CR would tell the server to run the stored procedure and all of the records from that SP would be sent to the report, and then the record selection formula would be used to filter out the records that you don't want.

 If Rpt_SalesSummaryAdopted_sproc_MAC_RAMS_RB is a SP, then you might want to consider adding parameters to that to replace the ones in the report (eg. {?RptTerr}), so that the SP can do the filtering on the server.


 Keep in mind that if that is a stored procedure, and anything else uses that SP, then any changes that you make, whether it's adding parameters, or just changing it to check one field for values that start with '4', will affect everything that uses that SP.

 James
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Excellent thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.