<< Click to Display Table of Contents >> Navigation:  Using Cimera > Searching and Querying > Structured Queries > Reporting DB Query > Reporting DB Assistant

The Reporting DB Assistant simplifies the creation of complex queries by allowing users to navigate the Item Types and Valid Links and selecting the columns and joins they are interested in.

 

 

The top pane displays all of the Item Types (inherited Item Types are shown in italics) which typically form the basis of queries.

 

The lower pane shows the generated SQL which can then be copied and pasted to the SQL querying tool of choice (whether that be Cimera's Reporting DB Query or an external tool such as SQL Server Management Studio or Crystal Reports).

 

To generate (or regenerate) the SQL press the Generate SQL button.

 

To copy the generated SQL to the clipboard (for pasting elsewhere) press the Copy SQL to clipboard button.

 

Expanding an Item Type shows its field collection and the Valid Links that it has to other Item Types:

 

 

By default all fields are selected (for Item Types, not for Valid Links) but expanding the Fields node allows them to be individually selected:

 

 

Fields are shown with their name and then their display name (if there is one assigned) in brackets.

 

For a versionable Item Type there are two entries - one for the stem and one for the version.

 

The stem for Product Build:

 

 

The version includes fields from both the stem and the version suitably annotated:

 

 

Expanding a Valid Link shows the Item Types that can be at the other end of the relationship:

 

 

Notice the inherited Item Types in italics. Each of their descendant Item Types is shown. So in this instance the query could be constructed to find Problems that are related to a Server or to find Problems that are related to a Computer, which includes Server, Desktop and Laptop.

 

Complex queries can be built quite easily:

 

 

As this stands all fields are being selected. This is probably not what is required as there will be over 100 of them.

 

After some judicious field selection this results in the following SQL being generated:

 

SELECT

Problem.[ciname],Problem.[ciowner_Group],Problem.[ciowner_UserName],Problem.[cistatus],Problem.[title],Problem.[ciassignee_Group],Problem.[ciassignee_UserName],

Computer.[ciname],

Product_Build_Version.[ciname],Product_Build_Version.[civersionname],Product_Build_Version.[cistatus],

Technical_Change.[ciname],Technical_Change.[cistatus],Technical_Change.[title]

FROM

Problem

INNER JOIN LK_affbypr ON Problem.ciobjid=LK_affbypr.ChildId

INNER JOIN Computer ON LK_affbypr.ParentId=Computer.ciobjid

INNER JOIN LK_computerhostsrel ON Computer.ciobjid=LK_computerhostsrel.ParentId

INNER JOIN Product_Build_Version ON LK_computerhostsrel.ChildId=Product_Build_Version.ciobjid

INNER JOIN LK_Product_Technical_Change ON Product_Build_Version.ciobjid=LK_Product_Technical_Change.ParentId

INNER JOIN Technical_Change ON LK_Product_Technical_Change.ChildId=Technical_Change.ciobjid

 

This SQL may be the complete query but it is more likely that it will form the basis for the desired query and will need further tailoring to add Where criteria or to perform any grouping and sorting or perhaps to modify the join types.

 

Note that the Reporting DB Assistant is write-only. It cannot read and parse previously created SQL.