Reports and Report Charts can only be built from views. So If we want to create a Report Chart that uses Key Attribute Data then we must first create the view.
The structure of the tables that holds the key attribute data is complex and trying to work this out on our own to build a view would be a deeply unpleasant task. There is, however, a trick we can use.
Groups (or sometimes called Target Lists) work in a similar way as Reports. The code that creates them has a lot of overlap and both the definitions of Groups/Target Lists and Reports are held in the same meta data tables.
The trick to get the SQL for our view requires us to build a Group (target list) that uses the Key Attributes in which we are interested.
We can use the standard demo system for this and create a view that uses the existing demo Key Attribute Category 'Installed Software' that is linked to the company table. We can find this under the Key Attribute tab when looking at a Company.
We need to create a new Group for the Company entity based on the standard CompanyGroup. We want to create a pie chart to show the numbers of Companies using software of each type.
We then need to include from the Key Attributes (Installed Software) the field that you want (Existing Enterprise Software) against the company entity. The Key Attribute Data should be associated with the Group Contents and the Search Criteria. If we are wanting to plot a chart against all data then each of the options in the selection list should be chosen when answering the question 'Matches any of the values'.
Once the Group definition has been saved. If we are logged on to the system as the System Administrator then we will be able to see the Advanced Button at the bottom right of the screen.
If we click this button we will have access to the underlying SQL of this Group. Which may look something like this:
SELECT DISTINCT Comp_CompanyId, comp_name, addr_city, comp_emailaddress,
comp_website, comp_secterr, V6.OutputString DDFld_16x5 FROM vtargetlistcompany
INNER JOIN vDynamicData V6 on Comp_CompanyId = V6.DData_EntityID AND
(V6.DData_Entity=5 and V6.DData_FieldID=16) AND
(V6.DData_ShortStr IN (N'2', N'3', N'1', N'4'))
WHERE Comp_CompanyId IS NOT NULL
We can now use this SQL to create a new view against the Company entity. This will have to be done through the interface
Administration>Customization>Company and then choose the View tabs. We should only put the SQL that we need in place so I will edit the view SQL to drop the DISTINCT
CREATE VIEW vCompanyKA
AS
SELECT Comp_CompanyId, comp_name, addr_city, comp_emailaddress, comp_website,
comp_secterr, V6.OutputString DDFld_16x5 FROM vtargetlistcompany INNER JOIN
vDynamicData V6 on Comp_CompanyId = V6.DData_EntityID AND (V6.DData_Entity=5
and V6.DData_FieldID=16) AND (V6.DData_ShortStr IN (N'2', N'3', N'1', N'4'))
WHERE Comp_CompanyId IS NOT NULL
The SQL above assumes that the view was created with the name vCompanyKA.
Now that we have the view created we need to make sure that it is marked in the definition screen as available for reports.
Once we have saved the view we can then build the report. This report can have graphs that make use of the Key Attribute data and these graphs in turn can be included and called from user dashboards.
The structure of the tables that holds the key attribute data is complex and trying to work this out on our own to build a view would be a deeply unpleasant task. There is, however, a trick we can use.
Groups (or sometimes called Target Lists) work in a similar way as Reports. The code that creates them has a lot of overlap and both the definitions of Groups/Target Lists and Reports are held in the same meta data tables.
The trick to get the SQL for our view requires us to build a Group (target list) that uses the Key Attributes in which we are interested.
We can use the standard demo system for this and create a view that uses the existing demo Key Attribute Category 'Installed Software' that is linked to the company table. We can find this under the Key Attribute tab when looking at a Company.
We need to create a new Group for the Company entity based on the standard CompanyGroup. We want to create a pie chart to show the numbers of Companies using software of each type.
We then need to include from the Key Attributes (Installed Software) the field that you want (Existing Enterprise Software) against the company entity. The Key Attribute Data should be associated with the Group Contents and the Search Criteria. If we are wanting to plot a chart against all data then each of the options in the selection list should be chosen when answering the question 'Matches any of the values'.
Once the Group definition has been saved. If we are logged on to the system as the System Administrator then we will be able to see the Advanced Button at the bottom right of the screen.
If we click this button we will have access to the underlying SQL of this Group. Which may look something like this:
SELECT DISTINCT Comp_CompanyId, comp_name, addr_city, comp_emailaddress,
comp_website, comp_secterr, V6.OutputString DDFld_16x5 FROM vtargetlistcompany
INNER JOIN vDynamicData V6 on Comp_CompanyId = V6.DData_EntityID AND
(V6.DData_Entity=5 and V6.DData_FieldID=16) AND
(V6.DData_ShortStr IN (N'2', N'3', N'1', N'4'))
WHERE Comp_CompanyId IS NOT NULL
We can now use this SQL to create a new view against the Company entity. This will have to be done through the interface
Administration>Customization>Company and then choose the View tabs. We should only put the SQL that we need in place so I will edit the view SQL to drop the DISTINCT
CREATE VIEW vCompanyKA
AS
SELECT Comp_CompanyId, comp_name, addr_city, comp_emailaddress, comp_website,
comp_secterr, V6.OutputString DDFld_16x5 FROM vtargetlistcompany INNER JOIN
vDynamicData V6 on Comp_CompanyId = V6.DData_EntityID AND (V6.DData_Entity=5
and V6.DData_FieldID=16) AND (V6.DData_ShortStr IN (N'2', N'3', N'1', N'4'))
WHERE Comp_CompanyId IS NOT NULL
The SQL above assumes that the view was created with the name vCompanyKA.
Now that we have the view created we need to make sure that it is marked in the definition screen as available for reports.
Once we have saved the view we can then build the report. This report can have graphs that make use of the Key Attribute data and these graphs in turn can be included and called from user dashboards.
