LEFT JOIN and Filtered Grouping

LEFT JOIN and Filtered Grouping

Posted by: grapecity on 25 September 2017, 10:46 pm EST

• 

  grapecity

  • Post Options:
  • Link
    Copy

  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?

  Thanks,

  Simon

• 

  bhupeshmalhotra

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Hi Simon,

  Outer Joins

  The “Optional Many” is an option that you can set in the “Cardinality” property of the relation between the two entities (see screenshot below).

  There is a sentence under the Cardinality property which explains in regular English the type of join that is being set. I would recommend see the Cardinality property for the relationship from both sides (entities) to confirm they are valid for your business data.

  Another thing to consider will be which entity you “start” the relation from. You will have relevant choices based on the entity that you are on when you add the relation. So if you are not getting the correct choices that you want for the cardinality, try creating the relation using the other entity as the base entity.

  Filtering Aggregates

  This one can be tricky. If you are looking for aggregating at the Vehicle Type group level, then the # Vehicle attribute will work, such that the Hatchback group header will have the number of hatchbacks. Similarly, if you create a filter on a table for hatchback vehicles, the # Vehicle attribute used in that table will also have the filtered number (since the table dataset is filtered).

  The part which is tricky, is if you want to use # Hatchback Vehicles as a column and then # SUV Vehicles as another column on the same row (say for each Brand). Now, this is a case which borders on the concept of a Cross-Tab table. The way to do this would be to create expression based attributes in the Vehicles entity for each of the types that you want to be reporting on. Then use aggregates (Sum/Count depending on the value you have in the attribute) to get the total number of the different types for each brand.

  Let me know if this helps.

  Cheers, Bhupesh.

• 

  blackmirror

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Thanks for the reply.

  Re: Outer Joins I did have the “OptionalMany” set (see original comments) and have tried different options since and can’t get it to work. I have reproduced the problem using the ActiveTunes database which has the same type of relationship and the same thing happens (please see screenshot). I have checked the ActiveTunes relationships which mimic mine both ways (Artist has “OptionalMany” Albums and Album has “One” Artist).

  A better display would be using an “Artist Name” group header but I have tried this and makes no difference to the data shown.

  Re: Filtering Aggregates I could see that your solution was a way to get the data but unfortunately the users will not have access to modifying Entities. At least this confirms that I hadn’t missed something so is helpful.

  Thanks,

  Simon

• 

  arpit-jain

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Hello Simon,

  Please accept our sincere apologies for the delay caused in responding to you. The issue got missed out from the loop somehow. We are investigating on the issue further at our end and will let you know as soon as it is done.

  Regards, Arpit Jain

• 

  bhupeshmalhotra

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Simon,

  Can you please attach this report? Did you make any changes to the ActiveTunes Data Model? If you did, can you please share that also?

  I would like to review them and see if there is anything missing or abnormal behavior in the product.

  Thanks.

  Bhupesh.

• 

  blackmirror

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Hi Bhupesh,

  I have attached the RDLX file that I downloaded from the server. I have made no changes to the ActiveTunes database.

  Simon 2016/04/Left-Join-Issue.rdlx

• 

  bhupeshmalhotra

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Thanks Simon,

  I am going to try to reproduce this on my server and share feedback with you soon.

  Cheers, Bhupesh.

• 

  bhupeshmalhotra

  • Post Options:
  • Link
    Copy

  Posted 25 September 2017, 10:46 pm EST

  Hi Simon,

  I have confirmed with the team that this is a limitation for the Model relations when working with discrete data. Aggregates as you see in the first part of your report (Home | GrapeCity Forums), are not effected by this and work with the left-join. So they show a number “0” when there are no records. However, when joining for getting discrete attributes from both tables, an inner join is used.

  Let me know if this clarifies the behavior and if there are any other questions.

  Cheers, Bhupesh.

• 

  lance.stanley

  • Post Options:
  • Link
    Copy

  Posted 16 May 2018, 8:52 am EST

  This does not seem to be an acceptable answer for a reporting solution. Are you saying that we cannot perform an outer join using a report model?

  We are using ARS 12

  Thanks

• 

  tommchris7

  • Post Options:
  • Link
    Copy

  Posted 4 September 2022, 12:29 pm EST

  Filtered grouping allows us to group records by a specific field, such as a user https://1v1lol.app . In this scenario, whenever the user ID is matched with another record, the record will be grouped with it.