Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.

Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.sql

Displaying Result

hi there,

is there any way in populating data like the example below using Reporting Services?

SQL database table result:

Category Name

Group 1 John

Group1 May

Group2 Mary

Display in Report

Group 1 Group2

John, May Mary

regards

angela

Hi Angela

If you want the array of names to be shown next to
each other in instead of having to itterate for each item
in the group you could do the following.

Create a table report with 'group' as the group
and 'Name' as detail

In the expression for name you can the use the join function:

=JOIN(Fields!Name, " ")

This will append all the fields in the aggregation,
if you group by the 'group' field it should effectively
give only the names for that group, in the following formatt:

Group Name
-|
Group1 | Angela Johan Beckham |
--|-|
Group2 | Andre Jaco Peter |
--|-|

Hope this helps

Gerhard Davids

|||

Hi Gerhard Davids,

the solution u provide returns an error like this:

Overload resolution failed because no Public 'Join' can be called with these arguments:

'Public Shared Function Join(sourceArray As String(),[Delimiter As String = ] as string......

Regards

angela

|||

HiAngela

I'm sorry, I made a mistake: the join function is for
joining selected parameter arrays in a multi-selected
parameter. Your problem seems far more complex.

I am trying to find a solution for you, and I will let
you know as soon as I find one.

Sorry for not being more helpfull.

Gerhard Davids

|||

Hi Gerhard Davids,

Thanks 4 ur help, my problem has been solved..but was done in a very tedious way by writing the stored procedure.

regards

angela

displaying all records of duplicates

HI all I have this table below and i need to get records from col1 >1 and col2 >1

col1 col2 language
1 a s
1 b p
3 c e
3 c f

SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)

with the above query it gives me this

col1 col2 expr1
3 c 2

the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

col1 col2 expr1 language
3 c 2 e
3 c 2 f

I need the result above inorder to normalize the table and bring out the language part into another table

thanksSELECT t.col1, t.col2, d.daCount, t.language
FROM table1 AS t
INNER
JOIN (
SELECT col1, col2, COUNT(*) AS daCount
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1
) as d
ON c.col1 = t.col1
AND d.col2 = t.col2|||thanks Rudy

Got it...

Displaying a zero instead of NULL

I am using the expression below to display a zero (number 0) instead of NULL
=IIF(ISNOTHING(SUM(FIELDS!Dis.Value)),0, SUM(FIELDS!Dis.Value))
Whats wrong in this ? It gives me a blank but NO ERROR ..
ThanksYou can't sum a Null value - it doesn't result in a null, just an error. You
need to say:
=Sum(IIf(IsNothing(Fields!Dis.Value),0,Fields!Dis.Value)
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:13D84407-7151-44BE-A276-A3D557FFFC95@.microsoft.com...
>I am using the expression below to display a zero (number 0) instead of
>NULL
> =IIF(ISNOTHING(SUM(FIELDS!Dis.Value)),0, SUM(FIELDS!Dis.Value))
> Whats wrong in this ? It gives me a blank but NO ERROR ..
> Thanks
>|||You could also use the ISNULL function to make nulls 0
SELECT ISNULL(FeildName,0) From TABLE
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:13D84407-7151-44BE-A276-A3D557FFFC95@.microsoft.com...
> I am using the expression below to display a zero (number 0) instead of
NULL
> =IIF(ISNOTHING(SUM(FIELDS!Dis.Value)),0, SUM(FIELDS!Dis.Value))
> Whats wrong in this ? It gives me a blank but NO ERROR ..
> Thanks
>|||Correct Anwer is:
=IIF(ISNOTHING(Sum(Fields!Dis.Value)),0,(Sum(Fields!Dis.Value)))
"Asim" wrote:
> I am using the expression below to display a zero (number 0) instead of NULL
> =IIF(ISNOTHING(SUM(FIELDS!Dis.Value)),0, SUM(FIELDS!Dis.Value))
> Whats wrong in this ? It gives me a blank but NO ERROR ..
> Thanks
>

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (upd
ated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabul
ar form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert Lie
Use the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

display sql xml result

hi all...
i'm using the command below to get a recodset from a sql2000 in a xml
structure.
select * from tb_perguntas FOR XML RAW
i know that with this command i can get a result like this:.
<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders OrderID="10643" OrderDate="08/25/1997" />
<Orders OrderID="11011" OrderDate="04/09/1998" />
</Customers>
i'm having problems getting this xml string from a ASP page.
i tried:
<%
Dim connObj
Set connObj = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
connObj.ConnectionString = "DRIVER={SQL
Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
connObj.Open
Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
response.write( "rs:" & rs.fields(0).value)
%>
the only thing i want it's display the complete string SQL returned.
please help.
thanks.
please help.
You need to use the ICommandStream interface and not the recordset to get
your XML back. See the documentation for examples.
Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
Best regards
Michael
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> hi all...
> i'm using the command below to get a recodset from a sql2000 in a xml
> structure.
> select * from tb_perguntas FOR XML RAW
> i know that with this command i can get a result like this:.
> <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
> <Orders OrderID="10643" OrderDate="08/25/1997" />
> <Orders OrderID="11011" OrderDate="04/09/1998" />
> </Customers>
> i'm having problems getting this xml string from a ASP page.
> i tried:
> <%
> Dim connObj
> Set connObj = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
> connObj.ConnectionString = "DRIVER={SQL
> Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
> connObj.Open
> Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
> response.write( "rs:" & rs.fields(0).value)
> %>
> the only thing i want it's display the complete string SQL returned.
> please help.
> thanks.
> please help.
>

display sql xml result

hi all...
i'm using the command below to get a recodset from a sql2000 in a xml
structure.
select * from tb_perguntas FOR XML RAW
i know that with this command i can get a result like this:.
<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders OrderID="10643" OrderDate="08/25/1997" />
<Orders OrderID="11011" OrderDate="04/09/1998" />
</Customers>
i'm having problems getting this xml string from a ASP page.
i tried:
<%
Dim connObj
Set connObj = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
connObj.ConnectionString = "DRIVER={SQL
Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
connObj.Open
Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
response.write( "rs:" & rs.fields(0).value)
%>
the only thing i want it's display the complete string SQL returned.
please help.
thanks.
please help.You need to use the ICommandStream interface and not the recordset to get
your XML back. See the documentation for examples.
Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
Best regards
Michael
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> hi all...
> i'm using the command below to get a recodset from a sql2000 in a xml
> structure.
> select * from tb_perguntas FOR XML RAW
> i know that with this command i can get a result like this:.
> <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
> <Orders OrderID="10643" OrderDate="08/25/1997" />
> <Orders OrderID="11011" OrderDate="04/09/1998" />
> </Customers>
> i'm having problems getting this xml string from a ASP page.
> i tried:
> <%
> Dim connObj
> Set connObj = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
> connObj.ConnectionString = "DRIVER={SQL
> Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
> connObj.Open
> Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
> response.write( "rs:" & rs.fields(0).value)
> %>
> the only thing i want it's display the complete string SQL returned.
> please help.
> thanks.
> please help.
>|||hi michael, thanks for the reply.
i looked here:
http://support.microsoft.com/kb/272185/EN-US/
[url]http://www.com.it-berater.org/data_access/oledb/interfaces/ICommandStream.htm[/url
]
and there is no example on how to implement this xml return in asp. the
sample is for win32 console applications.
i can return the normal (not xml) search and then manually get the fields
and values form the recordset and set up the nodes adding the "<" "/>" tags,
however if i can already ge the return in xml why bother creating the tags.
i just can't addimit that nobody have done this before..or maybe it's not
htat simple as i think...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> escreveu na mensagem
news:ew3DUPcWGHA.3492@.TK2MSFTNGP05.phx.gbl...
> You need to use the ICommandStream interface and not the recordset to get
> your XML back. See the documentation for examples.
> Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
> EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
> Best regards
> Michael
> "rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
> news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||Did you take a look at
http://msdn.microsoft.com/library/d...>
DOandXML.asp
?
Best regards
Michael
PS: You may need to adjust the provider string...
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23SedSHpWGHA.5012@.TK2MSFTNGP05.phx.gbl...
> hi michael, thanks for the reply.
> i looked here:
> http://support.microsoft.com/kb/272185/EN-US/
> [url]http://www.com.it-berater.org/data_access/oledb/interfaces/ICommandStream.htm[/u
rl]
> and there is no example on how to implement this xml return in asp. the
> sample is for win32 console applications.
> i can return the normal (not xml) search and then manually get the fields
> and values form the recordset and set up the nodes adding the "<" "/>"
> tags,
> however if i can already ge the return in xml why bother creating the
> tags.
> i just can't addimit that nobody have done this before..or maybe it's not
> htat simple as i think...
>
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> escreveu na mensagem
> news:ew3DUPcWGHA.3492@.TK2MSFTNGP05.phx.gbl...
>

Display quote in data using select statement

Hi,

I want to display data in the below format, please help me how to get this?

Data

'Employee1',Accountant

'Employee2',Programmer

I gave the select statment like this but it is not taking

SELECT '" + employeename + '" + departmentname from empdept

Please help me how to display quotes before and after the employeename.

Thank you

You need two quotes within quotes (thus four if a standalone quote) to display quotes in SQL, not three, hence:

SELECT ''" + employeename + ''" + departmentname from empdept

HTH

For more SQL tips, check out my blog:

display fields vertically and horizontally

hello, i just started using crystal reports and have been trying to get the report to display the same fields as shown below.

2006/02/11 2005/11/09 2006/01/01
James john sandy

2003/08/16 2007/01/22 2006/01/01
Jane fred martha

2004/12/10 2005/05/03 2006/07/03
phil diane roger

The data is all come from the same fields (Start_Date) and (first_name) .

is it possible to display the data as such using crystal reports?

regards,

peter.Try this.
Place first field in the details section. In section expert check the box "Format with multiple columns". Set your options on layout tab.

Next, right click on details section and select "Insert section below". Place your second field in this section. Repeat the steps from above ("format with multiple columns", etc).

Hope this helps.