I added a new role 'Add Reports' with tasks 'Manage Reports' + 'Manage Folders'.

I then created folder 'Report Builder' added a New Role Assignment.

If anyone has a view on...

Thanks Guys.

HAVING COUNT(1) > 1 appears to be correct (returned 106 rows), HAVING COUNT(1) > 2 returned 0.

Great Work,

Phil.

Just a thought......after the update is there a way to filter WHERE Products appear more than once + WHERE the DateLatQuoted match AND SequenceNumber = 500.

On the 'Live' data 3000...

Thanks NULL. Excuse the error in my script (lack of INSERT INTO......I deleted in error!).

Many Thanks,

Phil.

Good work guys. Chris your code worked a treat.

Many thanks to all those who posted I appreciate your efforts.

Kind Regards,

Phil.

Thanks Richard.

Phil.

Hi Chris, yes the same values. The query is for a report. Currently the report returns a blank for NULL's, I thought it would look better if it displayed 0.00.

Many...

Guys my code now looks as follows:

WITH BidReport AS

(

SELECT

so.SalesOrderId AS BidNumber,

u.FirstName AS FirstName,

u.LastName AS LastName,

so.EffectiveDate AS Date,

c.CustomerName AS Customer,

Countries.CountryId AS DestinationSite,

soi.ItemDescription,CVOption.CustomFieldValueBit AS 'SystemOption',

CVWinProb.CustomFieldValueInteger...

Thanks guys.

Phil.

Chris my code runs without error? What can I do to help? Let me explain further. The user may enter multiple custom fields on the app screens (SalesOrders / SalesOrderDetails...

Hi Bob. I looked at this from a different angle and decided to modify the query rather than format in the report using visual studio. I then posted to the...

Yes. The app allows users to create custom fields that are then stored in tables CustomFields, CustomValues. The Order Header (SalesOrders) has a custom field 'WinProb', the Order Detail (SalesOrderDetails)...

Hi guys. I just had a play changing the joins. It did make a difference. Changing to

LEFT JOIN CustomValues AS CVOption

LEFT JOIN CustomFields AS CFOption ON CVOption.CustomField = CFOption.CustomField

AND CFOption.CustomFieldId...

Code as requested.

SELECT

so.SalesOrderId AS BidNumber,

u.FirstName,

u.LastName,

so.EffectiveDate AS Date,

c.CustomerName AS Customer,

Countries.CountryId AS DestinationSite,

soi.ItemDescription,CVOption.CustomFieldValueBit AS 'SystemOption',

(SELECT SUM(isnull(soi.ItemValue,0))

WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',

CVWinProb.CustomFieldValueInteger AS WinProbability,...

Hi, I tried:

(SELECT SUM(isnull(soi.ItemValue,0))

WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',

CVWinProb.CustomFieldValueInteger AS WinProbability,

QuoteValue still returns NULL?

Basically what I am trying to do is sum the ItemValue where a field 'Option'...