Skip to Content
0
Former Member
Jan 02, 2008 at 05:55 PM

Force a GROUP BY to a dimension without including the column in the SELECT

79 Views

Post Author: ewagener

CA Forum: Desktop Intelligence Reporting

I have a SUM measure object in my universe that has a Select statement consisting of a COUNT(distinct([Some column on Fact Table])). I would like to force a join to a dimension table and subsequent GROUP BY statement for one column on the dimension table anytime that this measure object is used.

I know that I can force the join from the fact table to the dimension table by simply putting the join syntax in the WHERE clause of the measure object. Example

SELECT COUNT(distinct ([Some column on Fact Table]))

WHERE FACT.DIM_KEY = DIM.DIM_KEY

The SQL that BusObj generates is correct and a join is made whenever the object is used, even by itself. Now I want to force BO to include a GROUP BY clause for one of the columns that is on the dimension table, while not forcing the user to select that column in the report. The SQL that I would like BO to generate is:

SELECT COUNT(distinct ([Some column on Fact Table]))

WHERE FACT.DIM_KEY = DIM.DIM_KEY

GROUP BY DIM.FIELD_A

I know a u201Cwork aroundu201D is to just include FIELD_A in my report and then hide it or remove it from the results. This works because I have the appropriate join defined in my universe linking the fact table to the dimension table on the PK/FK relationship. My report writer user does not want to have to add the column then remove it from the report, however, even though it gives her the correct answer. She has asked if we could do all of that behind the scenes. I also know that the ultimate solution would be do declare another fact grain and either use a view or a new fact table altogether. It may come to that, however, I am looking for a quick solution to just force the object to do a GROUP BY to a field in a dimension not selected in the report.

Thanks,

Ed