We are looking to create a cross tab report. Essentially, let's say
that we conducted a survey and tried to collect the brand of beers
that people drink.
So, let's say we have the following data
1. Respondent A drinks Corona and Bud Light
2. Respondent B drinks Corona and Guinness.
3. Respondent C drinks Corona and Bud Light
4. Respondent D drinks Guinness and Heineken.
5. Respondent E drinks Heineken and Bud Light.
6. Respondent F drinks Corona and Guinness.
Now, if I were to create a cross tab to understand the number of users
that drink both Bud Light and Corona, the answer is that 2 people
drink both.
However, can you think of a way we can do this with SQL Server's
analysis series? Please understand that in this example, we are
looking at two specific brands. However, we should be able to perform
the analysis across any two brands (like Corona and Guinness).
Any help is appreciated. I think the problem can be solved with a MDX
query. However, we are wrestling with the syntax.
Thanks.