Posted 25 September 2017, 10:46 pm EST
I cannot figure out a way to do a couple of things so would like to check whether they are possible or not.
I have a “Brand” entity which has an “OptionalMany” relationship to “VehicleModel” and I have data similar to that listed below which all links correctly and shows simple tabular data fine. However, I am trying to make a more complicated report without creating a complicated entity from a SQL query just for this report.
- “Brand” Name -VW, Ford, Colgate, Krispy Kremes
- “VehicleModel” ModelName - Golf, Jetta, Focus, Mustang
“LEFT JOIN” Style I would like to be able to create a table grouped by Brand.Name listing the VehicleModel.ModelNames in each row. If I just use attributes from Brand, all the brands are included. However, when I add a column for “VehicleModel.ModelNames”, I no longer get Colgate or Krispy Kremes because they do not make vehicles. It is possible to get this type of relationship into a report table?
“Filtered” Aggregates I can get a column with a count of VehicleModels by using the “# VehicleModels” attribute. However, I would like to aggregate on certain criteria such as Vehicle type. There is a “VehicleType” attribute on VehicleModel so I would like to add a “Hatchbacks” column to my report’s table for counting VehicleModels where VehicleType = “Hatchback” or something along those lines. Is there a way to do this?