Skip to Content
avatar image
Former Member

Cross-Tab Groups by Date Not Working

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!

Untitled-1.jpg (75.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 06, 2015 at 09:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded