| Author |
Message |
cpresk
Newbie
Joined: 18 Jan 2017
Online Status: Offline
Posts: 9
|
   Topic: Sum of non-suppressed data
Posted: 24 Jan 2017 at 8:51am |
|
I want to display a total of non-suppressed data in the Report Header. I can use Running Total to get the right number, but can't put that in the header. I'm suppressing data based on {table.id} = Previous ({table.id}). I've tried adding a field like this -
If {table.id} <> Previous ({table.id})
then {table.productsales}
else 0
That seems to work, but Crystal won't allow me to do a sum on that field. It says "This field cannot be summarized".
Any ideas?
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 24 Jan 2017 at 9:00am |
|
It won't let you use this because of the use of "Previous()".
It is possible, though. Will this be a single total for the report or does it need to be broken down in any way?
-Dell
|
|
|
|
IP Logged |
|
cpresk
Newbie
Joined: 18 Jan 2017
Online Status: Offline
Posts: 9
|
Posted: 24 Jan 2017 at 9:06am |
|
Just a single total. If there is a way to filter the data out of the report entirely, instead of just suppressing it, that would be ideal.
Edited by cpresk - 24 Jan 2017 at 9:14am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 24 Jan 2017 at 11:20am |
The only way to remove the suppressed records is to use a Command, which is a SQL query. To learn more about how to use commands, see my blog post here:
https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/
What type of database are you connecting to? How are you grouping and sorting your data in the report? Can you post the SQL that Crystal is generating for your report along with the formula that's in the Select Expert? That way I can write you a query that will pull the data you need for the report.
-Dell
|
|
|
|
IP Logged |
|
cpresk
Newbie
Joined: 18 Jan 2017
Online Status: Offline
Posts: 9
|
Posted: 25 Jan 2017 at 8:01am |
|
SQL Query -
SELECT "Call_Report_Entry__c"."Store_Name__c", "Call_Report_Entry__c"."Model_Number__c", "Call_Report__c"."Channel__c", "Call_Report_Entry__c"."Status__c", "Call_Report_Entry__c"."Date__c", "RecordType"."Name", "Call_Report_Entry__c"."of_Box_Stock_brought_to_shelf__c", "Call_Report_Entry__c"."Id", "Call_Report_Entry__c"."Is_the_SKU_present__c"
FROM ("2020 Sales Organization"."2020 Sales Organization"."Call_Report_Entry__c" "Call_Report_Entry__c" INNER JOIN "2020 Sales Organization"."2020 Sales Organization"."Call_Report__c" "Call_Report__c" ON "Call_Report_Entry__c"."Store_Name__c"="Call_Report__c"."Store_Name__c") INNER JOIN "2020 Sales Organization"."2020 Sales Organization"."RecordType" "RecordType" ON "Call_Report_Entry__c"."RecordTypeId"="RecordType"."Id"
WHERE ("Call_Report_Entry__c"."Date__c">={d '2017-01-15'} AND "Call_Report_Entry__c"."Date__c"<={d '2017-01-21'}) AND "Call_Report_Entry__c"."Status__c"='Complete' AND "RecordType"."Name"='2020RK-SMM Walmart Modular Sku Compliance' AND "Call_Report__c"."Channel__c"='Walmart'
ORDER BY "Call_Report_Entry__c"."Id", "Call_Report_Entry__c"."Store_Name__c" DESC, "Call_Report_Entry__c"."Date__c" DESC, "Call_Report_Entry__c"."Status__c"
Select Expert Formula -
{Call_Report_Entry__c.Date__c} in LastFullWeek and
{Call_Report_Entry__c.Status__c} = "Complete" and
{Call_Report__c.Channel__c} = "Walmart" and
{RecordType.Name} = "2020RK-SMM Walmart Modular Sku Compliance"
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 25 Jan 2017 at 8:11am |
|
What type of database are you connecting to? I need to use some date functions with this and those tend to vary by database.
Also, is it correct to assume that all of the conditions in the Select Expert are static strings and not parameters that the user sets?
Finally, I don't see the product sales field in the query you sent me. Which field is it?
-Dell
Edited by hilfy - 25 Jan 2017 at 8:13am
|
|
|
|
IP Logged |
|
cpresk
Newbie
Joined: 18 Jan 2017
Online Status: Offline
Posts: 9
|
Posted: 25 Jan 2017 at 9:36am |
|
Honestly, I'm not sure what type of database it is...is there a way for me to tell using Crystal?
Yes, the conditions are static strings and in the field that equates to product sales is of_Box_Stock_brought_to_shelf__c.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 25 Jan 2017 at 11:07am |
|
Looking at this, I have something I'd like you to try first. On the Database menu, turn on "Select distinct records". Then turn off your suppression formula and determine whether that resolves the problem.
If it doesn't, do you know which fields might be causing the duplicate data? Which fields are you actually showing on the report and which are used only in formulas?
-Dell
Edited by hilfy - 25 Jan 2017 at 11:08am
|
|
|
|
IP Logged |
|
cpresk
Newbie
Joined: 18 Jan 2017
Online Status: Offline
Posts: 9
|
Posted: 26 Jan 2017 at 9:28am |
|
That appears to have done the trick! Thank you so much! I've only tested it with a limited data set so far...it keeps timing out when I try the full set. I'm working on some things to speed it up.
I have a related, but slightly different issue with another report where I have records that aren't duplicates, but I don't want to show them in a chart. The scenario is multiple visits to the same store and I only want to chart the most recent visit. What is the best way to accomplish that?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 26 Jan 2017 at 10:28am |
That's definitely going to require a command. Assuming that the person visiting the store is a customer, here's the logic you'll use:
Select <all fields for your report>
From <all tables for your report>
inner join (
select CustomerID, Max(VisitDate) as maxDate
from <whatever table defines a visit>
group by CustomerID) as maxVisit
on <visit table>.Customer ID = maxVisit.CustomerID
and <visit table>.VisitDate = maxVisit.maxDate
Use the correct field and table names from your data.
Doing this in a join instead of the where clause will speed up the query as well.
-Dell
|
|
|
|
IP Logged |
|
|
|
