Sum of non-suppressed data
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22206
Printed Date: 04 May 2024 at 12:54am
Topic: Sum of non-suppressed data
Posted By: cpresk
Subject: Sum of non-suppressed data
Date 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?
|
Replies:
Posted By: hilfy
Date 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
-------------
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: cpresk
Date 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.
|
Posted By: hilfy
Date 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/ - 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
-------------
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: cpresk
Date 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"
|
Posted By: hilfy
Date 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
-------------
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: cpresk
Date 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.
|
Posted By: hilfy
Date 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
-------------
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: cpresk
Date 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?
|
Posted By: hilfy
Date 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
-------------
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: cpresk
Date Posted: 27 Jan 2017 at 8:12am
I think this will actually help me a lot with speeding up future reports. For this particular chart, I don't think I need to join any tables. I am almost illiterate when it comes to SQL, but here is what I pared this down to -
SELECT If_no_products_floored_why_not__c, Max (Date__c) as 'maxDate', Store_Name__c,
FROM Call_Report_Entry__c
It's not working and I'm guessing it's a simple syntax error, but I can't figure out what it is.
|
Posted By: hilfy
Date Posted: 27 Jan 2017 at 8:32am
What is the error you're getting?
-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: cpresk
Date Posted: 27 Jan 2017 at 9:30am
|
I'm trying to attach a picture, but can't figure out how to do it! It says 'Failed to retrieve data from the database......syntax error near 'SELECT........as 'maxDate'<<<???>>>, Store_Name_c......'
|
Posted By: hilfy
Date Posted: 27 Jan 2017 at 10:24am
I see the issue now. Remove the comma after "Store_Name__c" in the Select part of the query.
-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: cpresk
Date Posted: 30 Jan 2017 at 4:35am
GAAA!!! Now I have a new error! I'm definitely going to need to take some SQL training. Any suggestions?
Anyway, the new error says - 'Failed to retrieve data from database.
Details: 42000:[Simba][SQLEngine] (31430) Column 'datasource.If_no_products_floored_why_not_c' is neither in an aggregate function nor from the GROUP BY clause. [Database Vendor Code: 31430]
Any idea what that means?
|
Posted By: hilfy
Date Posted: 30 Jan 2017 at 5:26am
This one's an easy fix. When you use a summary (sum, max, etc) in a query, all of the fields that are not in a summary have to be in a Group By clause in the query. So, you'll need to change it to this:
SELECT If_no_products_floored_why_not__c, Max (Date__c) as 'maxDate', Store_Name__c
FROM Call_Report_Entry__c
GROUP BY If_no_products_floored_why_not__c, Store_Name__c
-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: cpresk
Date Posted: 30 Jan 2017 at 9:39am
|
Getting closer...I was able to get the data in now, but I'm still getting all of the visits instead of just the most recent to each store. Do I need something else in the Max function to tell it to only include the most recent visit to each store?
|
Posted By: hilfy
Date Posted: 30 Jan 2017 at 10:39am
I'm pm-ing you my email address so that you can send me the report to take a look at. I think something is getting lost in translation here.
Thanks!
-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: hilfy
Date Posted: 31 Jan 2017 at 12:40pm
Ok, you're getting the extra details because of the differences in data in If_no_products_floored_why_not__c.
You have two options for this query, depending on whether SalesForce will allow you to join to a sub-query.
Option 1 - This is faster at the database level:
SELECT cre.If_no_products_floored_why_not__c, cre.Date__c as maxDate, cre.Store_Name__c
FROM Call_Report_Entry__c as 'cre'
inner join (
Select Store_Name__c, Max(Date__c) as maxDate
from Call_Report_Entry__c
group by Store_Name__c
) as maxDates
on cre.Store_Name__c = maxDates.Store_Name__c
and cre.Date__c = maxDates.maxDate
Option 2 - If it won't left you use the subquery in a join, try this:
SELECT cre.If_no_products_floored_why_not__c, cre.Date__c as maxDate, cre.Store_Name__c
FROM Call_Report_Entry__c as 'cre'
WHERE cre.Date__c =
(
Select Max(Date__c) as maxDate
from Call_Report_Entry__c as 'tmp'
where tmp.Store_Name__c = cre.Store_Name__c
) as maxDates
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|