Bug? Report Builder returning only unique records!

Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.

Example:

TransID Type TransQuantity

1 CS 4
2 CS 4
3 CP 2
4 CN 3

Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:

Type TransQuantity

CS 4
CP 2
CN 3

Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.

Thanks,
Taurkon

You need to be aware of what Report Builder is grouping on. If you don't want rows based on distinct values, add the ID field first to make sure you get an entity group, then remove it later.

See this blog post for more info:

http://blogs.msdn.com/bobmeyers/archive/2006/12/20/getting-grouping-right-in-report-builder.aspx

Hope this helps!

|||Thanks Bob for your response.

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB

British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB