Need help with crosstab report - count over multiple fields

[Registrar]

Hi all,

I need help with a crosstab report. On my Y axis, I have the list of majors, (major1),  and on the X axis the students' classification Freshman, sophomore, junior, senior. In the middle I have student count - distinct.

It works beautifully. The problem is that student may have more than one major and we have three fields, Major1, major2 and major3. I want to add the three fields together in the crosstab report. So if I have 5 freshman with an accounting major in major1 and 2 freshmen with an accounting major 2 and 0 in major 3, then my crosstab report would show 7 freshman with an accounting major.

I hoped that I could create a data item called "all majors" and get all the values in that column and use that field in my Y access. If so, I am having trouble with the code.

[hespora]

Now I am assuming this is a relational data source - disregard if you have dimensional.

a) run three separate queries, with each query only returning one of the Majors 1, 2, 3.
b) union the queries
c) use the union query to feed your crosstab
d) only in the union query you can calculate your count, and you need to modify it to count distinct [student] for [major]