Cross-Tab Groups by Date Not Working

[Former Member]

Hello,

I am using a cross-tab to display the number of phone calls and emails are team made in a given period. I named three groups under each activity to count the number per Week To Date, Month To Date and Year To Date...

As you can see from my screenshot, year to date does not display. The grouping is a simple sum formula for counting any activities with dates that are in the period YTD.

I have a feeling it has to do with data belonging to more than one group. In other words, an activity counted as "Week to date" would also belong to "Month to date" and also "Year to date", but it can only belong to one group. Is that true? Is there a way to resolve this.

It seems like a simple cross tab report that I should be able to do.

Thanks in advance for any help!

[DellSC · Accepted solution]

You will probably have to have three queries unioned together in a Command in order to do this.

A Command is a SQL Select statement that is available in Crystal for most types of database connections.  There are some tricks to use Commands, though.

1.  A command should return ALL of the fields required for a report.  If you try to join a command and tables or other commands, Crystal will pull all of the data from each into memory and process the join there instead of pushing the join down to the database where it is more efficient.

2.  DO NOT use the Select Expert to set up the filters for your data!  Instead, put the filters into the Where clause of the query in the command.

3.  Commands cannot see parameters that have been created in the Field Explorer for the report.  Instead, they require that any prompts be created in the Command Editor and then used in the query.  You can't set many parameter properties in the Command Editor, but you can modify the params in the Field Explorer AFTER they've been created and used in the Command Editor.

If you'll provide the SQL that your report is currently using (Database>>Show SQL Query - make sure that all of the filters from the Select Expert are included in the query!)  I may be able to come up with some SQL to get you started.

-Dell

[Former Member · Accepted solution]

Thank you Dell, for your reply!

In the Field Explorer are you referring to "SQL Expressions"?

I am not sure exactly what you mean, but I will research it.

Below is our SQL Query.

SELECT "Task"."ActivityDate", "Task"."Description", "Task"."Activity_Type__c", "Account"."Name", "User"."FirstName"

FROM   ("Structural Graphics, LLC"."Structural Graphics, LLC"."Task" "Task" INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."Account" "Account" ON "Task"."AccountId"="Account"."Id") INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."User" "User" ON "Task"."OwnerId"="User"."Id"

WHERE  ("Task"."ActivityDate">={d '2015-01-01'} AND "Task"."ActivityDate"<={d '2015-01-08'}) AND ("User"."FirstName"='Janel' OR "User"."FirstName"='Karen' OR "User"."FirstName"='Mary Ann' OR "User"."FirstName"='Ronnie' OR "User"."FirstName"='Steven' OR "User"."FirstName"='Teresa' OR "User"."FirstName"='Tina' OR "User"."FirstName"='Tom' OR "User"."FirstName"='Warren')

[DellSC · Accepted solution]

No, I'm not talking about SQL Expressions.  Depending on the type of database connection you're using, under the connection in the Database Expert you should have the option to "Create Command".  A Command is a SQL Select statement that includes ALL of the data for your report.

In the query you posted above, are all of the filters included?  Are there any parameters in your report?  What type of database are you connecting to?  With this information I can definitely get you started with a command to pull the data you need.

-Dell

[Former Member · Accepted solution]

Thank you again, for your continued support.

There are no filter, just the selects in the query. No parameters. We are connecting to Salesforce.

I am not sure I understand how a command would help. Shouldn't I be able to display the data in a table format bucketing the data into date-ranges, as I specified in original post? It just seems so easy. Is the problem, as I suspected, that the different columns cannot count the same data twice?

FYI, I tried to send you a private message but it wouldn't let me since we're not connected. I am happy to establish a consulting relationship with you.

Thanks so much!

Nick

[DellSC · Accepted solution]

The problem is, each record is only calculated once.  So, you have to have a separate set of rows for each aggregation level that you're looking for - This Week, This Month, This Year.  I'm not absolutely sure how to set this up in a command coming out of Sales Force or if the Sales Force driver will allow you to do Unions.  If you were connected to a regular database this would not be difficult.

Here's some "generic" SQL for this, that you might be able to tweak:

SELECT
  'Week' as timeframe,
  Task.Description,
  Task.Activity_Type__c,
  Account.Name,
  User.FirstName,
  Count(*) as taskCount

FROM   "Structural Graphics, LLC"."Structural Graphics, LLC"."Task" Task
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."Account" Account
    ON Task.AccountId=Account.Id
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."User" User
    ON Task.OwnerId=User.Id

WHERE Task.ActivityDate>=<beginning of current MONTH> AND Task.ActivityDate<=<current date>
  AND User.FirstName in ('Janel','Karen','Mary Ann','Ronnie','Steven','Teresa','Tina','Tom','Warren')

UNION

SELECT
  'Month' as timeframe,
  Task.Description,
  Task.Activity_Type__c,
  Account.Name,
  User.FirstName,
  Count(*) as taskCount

FROM   "Structural Graphics, LLC"."Structural Graphics, LLC"."Task" Task
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."Account" Account
    ON Task.AccountId=Account.Id
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."User" User
    ON Task.OwnerId=User.Id

WHERE Task.ActivityDate>=<beginning of current MONTH> AND Task.ActivityDate<=<current date>
  AND User.FirstName in ('Janel','Karen','Mary Ann','Ronnie','Steven','Teresa','Tina','Tom','Warren')

UNION

SELECT
  'Year' as timeframe,
  Task.Description,
  Task.Activity_Type__c,
  Account.Name,
  User.FirstName,
  Count(*) as taskCount

FROM   "Structural Graphics, LLC"."Structural Graphics, LLC"."Task" Task
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."Account" Account
    ON Task.AccountId=Account.Id
  INNER JOIN "Structural Graphics, LLC"."Structural Graphics, LLC"."User" User
    ON Task.OwnerId=User.Id

WHERE Task.ActivityDate>=<beginning of current YEAR> AND Task.ActivityDate<=<current date>
  AND User.FirstName in ('Janel','Karen','Mary Ann','Ronnie','Steven','Teresa','Tina','Tom','Warren')

Because this will actually query the data three times, this week's data will be counted 3 times and this month's data will be counted twice.  You would set the columns in your CrossTab to the "Timeframe" field that this query returns.

-Dell

[Former Member · Accepted solution]

Thank you so much for your support!

Nick