Group keywords by site

Question

I am finding a lot of useful help here today, and I really appreciate it. This should be the last one for the day:

I have a list of the top 10 keywords per site, sorted by visits, by date. The records need to be sorted as follows (excuse the formatting):

        2010-05 2010-04
site1.com   keyword1    apples  wine
    keyword1 visits 100 12
    keyword2    oranges water
    keyword2 visits 99  10
site2.com   keyword1    blueberry   cornbread
    keyword1 visits 90  100
    keyword2    squares biscuits
    keyword2 visits 80  99

Basically what I need to accomplish involves grouping, but I can't seem to figure it out. Am I heading down the right path, or is there another way to achieve this, or is it just impossible?

Edit: The dataset is something like this (csv):

site_name,date,keyword,visits
site1.com,2010-04,apples,100
site1.com,2010-04,oranges,99
site1.com,2010-05,wine,12
site1.com,2010-05,water,10
site2.com,2010-04,cornbread,100
site2.com,2010-04,biscuits,99
site2.com,2010-05,blueberry,90
site2.com,2010-05,squares,80

Across the X-axis, we need to have the 'date' value Across the Y-axis, we need to have the 'site_name' as the primary value, but grouped within that we need to have the 'keyword' followed by the respective 'visits'.

Answer 1

Ok, I think you are going down the right track. It's a little tricky getting the groups right, but this should be able to be solved with grouping.

What it looks like you need is a matrix (the table where you can have dynamic rows and columns) and put the dates in a group across the top. Then group the rows by site name and then (I think) by keyword.

If grouping by keyword doesn't work, try grouping by the row number instead (within the scope of the site name group)? If this doesn't work, try getting your database to produce an extra column with rank in it first. Then you can definitely group by that. What I mean is:

site_name,date,keyword,visits,rank
site1.com,2010-04,apples,100,1
site1.com,2010-04,oranges,99,2
site1.com,2010-05,wine,12,1
site1.com,2010-05,water,10,2
site2.com,2010-04,cornbread,100,1
site2.com,2010-04,biscuits,99,2
site2.com,2010-05,blueberry,90,1
site2.com,2010-05,squares,80,2

You should then be able to add two rows in that group to put the keyword and visits in. If you can't, you might have to resort to fancy rectangle work - in the detail cell, put a rectangle, then two textboxes, with the keyword in the top one and the number of visits in the bottom one.

Answer 2

Create a row grouping on "site" then a child/sub row grouping on "keyword"

You don't need to use a Matrix as you know how many columns you will have, so you can just do it in a table

So the grouping would be something like =Fields!site_name with the same value appearing in the text box

then for the next grouping down =Fields!keyword ditto for the textbox

you can just use SUM to figure out how many vists =SUM(Fields!vists) in the group total