→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ChadMountain
Engaged Sweeper
Creating this report:
Select tblSoftwareUni.softwareName,
tblAssets.AssetName
From tblSoftwareUni
Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID
Where tblSoftwareUni.softwareName Not Like '%SOFTWARE TITLE 1%' And
tblSoftwareUni.softwareName Not Like '%SOFTWARE TITLE 2%' And
tblSoftwareUni.softwareName Not Like '%SOFTWARE TITLE 3%' And
tblSoftwareUni.softwareName Not Like '%SOFTWARE TITLE 4%' And
AND (tblSoftwareUni.SoftwareName NOT LIKE '%SOFTWARE TITLE 5%' AND tblAssets.AssetName NOT LIKE 'pc-%')

and the report editor always removes the brackets around my last constraint. Obviously this completely changes what the report is doing, so how can I stop it from doing this? I only want to exclude specific software items when they're on specific PCs, some I want to exclude where ever they're installed.
3 REPLIES 3
Tom_P
Lansweeper Employee
Lansweeper Employee
When a new SQL statement is copied into the report builder, some formatting will be applied. This is however expected behavior and should not alter the SQL itself. Do make sure however the AND/OR logic that is being used is correct.

In the first query, as all the filters are linked with the 'AND' operator, the additional brackets are not required and are therefore removed by the report builder. If however your goal is to filter on the assetName only in combination with the 'software 5' and not in combination with the other software packs, you would need a different setup of the query and work with a subquery to filter out the correct assets.

As for the second SQL, as no brackets are defined, SQL-wise the AND operator will be executed first and the report builder will set the brackets accordingly. To add an AND operator to this filter settings, you can use the following statement:

(tblAssets.AssetName Like 'team1-%' Or tblAssets.AssetName Like 'team2-%' Or tblAssets.AssetName Like team3-%') 
AND tblAssets.AssetName Not Like 'team%-a%'



ChadMountain
Engaged Sweeper
Anyone able to assist with this? We now have a fair number of reports that we can't run because of these bugs.
ChadMountain
Engaged Sweeper
Is there a way to write reports but disable the auto formatting feature? The automatic addition and removal of brackets into queries is incredibly frustrating, and just completely wrong most of the time.
For example, I've just tried the below in another report:

Add this in the constraints editor:
 Like 'team1-%' Or Like 'team2-%' Or Like 'team3-%'

This correctly gives me:
AND (tblAssets.AssetName Like 'team1-%' Or tblAssets.AssetName Like 'team2-%' Or
tblAssets.AssetName Like 'team3-%')


However if I then add an exclusion into my constraint (that I want to apply to the whole OR group):
Like 'team1-%' Or Like 'team2-%' Or Like 'team3-%' And Not Like 'team%-a%'

it incorrectly brackets this to be:
AND (tblAssets.AssetName Like 'team1-%' Or tblAssets.AssetName Like 'team2-%' Or
(tblAssets.AssetName Like 'team3-%' AND tblAssets.AssetName Not Like 'team%-a%'))

No matter what I try, it puts the brackets back, or removes the whole Not Like part of my query.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now