Crosstab summary on data that isn't in the crosstab

JimTalbut

I have a table that contains:
Source - string
Created - date
Level - string
I need to produce a crosstab that reports a count of those records created in the last week at each level (col) for each source (row).
That's the easy bit.

Unfortunately they also want, in the same table, to have a grand total on each row giving the total number of records in the database with that source - even though they weren't created in the last week.
Note that most sources won't have any records created in the last week (so the table will have lots of blank cells in it).

Is this doable at all?

My first cut, before the grand totals were added to the requirement, filtered by date at the SQL query level.
Since adding the grand totals requirement I've been trying to work something using date as a filter, but I can't get what I want there.

Thanks for any suggestions.

thuston

If the level (col) are static, you could do this more easily with a Table. You can make filtered aggregations based on the date range and also an unfiltered one for GrandTotal.

For the Crosstab, you might try a computed column (lastweek) that is 1 or 0/null conditional on the date.
Then you can create two measures, one to count all and another to count lastweek==1.

If the query is very slow (returns lots of data), you may even do the counts in your SQL and have the CrossTab just sum those.