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

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Displaying RowCount from DataSet in Report TextBox

What is the expression for displaying the rowcount from a dataset in a textbox report item?

On a related note, if how can I access the error code I am sending back from Stored Proc using Return statement, to show up in a text box on report?

.. uspSomeStoredProc...

If error occured

Return -1

elseif some other error

Return 1

else

Return 0

End

Is there a way to display that? In general, how can the values that are returned by stored procs such as RowsAffected or some err code using RETURN or Output Variable be accessed in report? I don't see any properties exposed by datasets in the expression window for doing that.

Any input on this will be appreciated.

Thanks.

|||Any one? Any suggestions or input on this?

displaying records in two tables

HI,
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat

displaying multiple charts using a single dataset

Hello,

I have the following tables :

Area
areano
areaname

Locality

locno
areano

RequestType
reqid
reqdtls

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.

I have written the query for this.

select q.[areaname] as Area,

classCount as TotalCount, ReqType

from(select a.areano,

a.classCount, ReqType

from(select areano,

count(*)as classCount, reqdtls as ReqType

from WebSummit

join RequestType

on WebSummit.reqid = RequestType.reqid

where date_created >=dateadd(mm,-3,getdate())

groupby areano, reqdtls

) a

join WebSummit b

on a.areano = b.areano

groupby a.areano,

a.classCount, ReqType

) p

join Area q

on p.areano = q.areano

The query works fine and has been tested. Now I want to display this in chart format.

The display should be in a chart format, one for each area showing a bar for each RequestType.

I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.

I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.

Plz help me out.

Here's some sample data

Area

AreaNo AreaName

1 Area1

2 Area2

3 Area 3

4 Area4

5 Area5

6 Area6

7 Area7

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 1

4 Ben 12/6/2007 3 1

5 Dale 14/6/2007 1 2

6 Evjen 15/6/2007 1 2

7 Fuller 16/6/2007 2 2

8 Jimmy 16/6/2007 3 2

9 Kart 16/6/2007 3 2

10 Fuller 16/6/2007 3 2

Regards,

Vidya

Any one has a clue?

Displaying months with no data

Hi all,
Does anyone have any thoughts on hard coding values into a result set
displayed in a grid? I have a dataset that I've built a bunch of grids
from in RS 2005. One of the reports I need to create always needs to
have all of the months of 2006 displayed regardless of whether there's
data. If there's no data for say July 2006, I get the following:
-Thanks!
Completed Programs
January 2006 1
February 2006 2
October 2006 3
November 2006 4
December 2006 233
I need
Completed Programs
January 2006 1
February 2006 2
March 2006 0
April 2006 0
May 2006 0
June 2006 0
July 2006 0
August 2006 0
September 2006 0
October 2006 3
November 2006 4
December 2006 233Hi,
What ever you want to display through report see that you bring it through
query first. if then try command from reporting server. In your case, from
query you can easily bring 0 value for the month which has no data. If not
then try in your grid
=IIF(<month> Is nothing, 0, month) some thing like this.
Regards
Amarnath
"bassunddrum@.gmail.com" wrote:
> Hi all,
> Does anyone have any thoughts on hard coding values into a result set
> displayed in a grid? I have a dataset that I've built a bunch of grids
> from in RS 2005. One of the reports I need to create always needs to
> have all of the months of 2006 displayed regardless of whether there's
> data. If there's no data for say July 2006, I get the following:
> -Thanks!
> Completed Programs
> January 2006 1
> February 2006 2
> October 2006 3
> November 2006 4
> December 2006 233
> I need
> Completed Programs
> January 2006 1
> February 2006 2
> March 2006 0
> April 2006 0
> May 2006 0
> June 2006 0
> July 2006 0
> August 2006 0
> September 2006 0
> October 2006 3
> November 2006 4
> December 2006 233
>|||You need to have the values in the dataset somehow, so if you can I would
create a Month table in the database which you can join to to provide the
complete list. If you can't do that then see if you can do a Stored
Procedure, then you can populate a memory table and return that instead.
Craig
<bassunddrum@.gmail.com> wrote in message
news:1140151459.214316.219040@.o13g2000cwo.googlegroups.com...
> Hi all,
> Does anyone have any thoughts on hard coding values into a result set
> displayed in a grid? I have a dataset that I've built a bunch of grids
> from in RS 2005. One of the reports I need to create always needs to
> have all of the months of 2006 displayed regardless of whether there's
> data. If there's no data for say July 2006, I get the following:
> -Thanks!
> Completed Programs
> January 2006 1
> February 2006 2
> October 2006 3
> November 2006 4
> December 2006 233
> I need
> Completed Programs
> January 2006 1
> February 2006 2
> March 2006 0
> April 2006 0
> May 2006 0
> June 2006 0
> July 2006 0
> August 2006 0
> September 2006 0
> October 2006 3
> November 2006 4
> December 2006 233
>|||If your completely stuck give me your SQL statement and I can add in to it
the extra months using unions.
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:%23tHX4u4MGHA.916@.TK2MSFTNGP10.phx.gbl...
> You need to have the values in the dataset somehow, so if you can I would
> create a Month table in the database which you can join to to provide the
> complete list. If you can't do that then see if you can do a Stored
> Procedure, then you can populate a memory table and return that instead.
> Craig
> <bassunddrum@.gmail.com> wrote in message
> news:1140151459.214316.219040@.o13g2000cwo.googlegroups.com...
>> Hi all,
>> Does anyone have any thoughts on hard coding values into a result set
>> displayed in a grid? I have a dataset that I've built a bunch of grids
>> from in RS 2005. One of the reports I need to create always needs to
>> have all of the months of 2006 displayed regardless of whether there's
>> data. If there's no data for say July 2006, I get the following:
>> -Thanks!
>> Completed Programs
>> January 2006 1
>> February 2006 2
>> October 2006 3
>> November 2006 4
>> December 2006 233
>> I need
>> Completed Programs
>> January 2006 1
>> February 2006 2
>> March 2006 0
>> April 2006 0
>> May 2006 0
>> June 2006 0
>> July 2006 0
>> August 2006 0
>> September 2006 0
>> October 2006 3
>> November 2006 4
>> December 2006 233
>

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

displaying data source in report

How would you display a dataset's data source in a report?
I want to say something like "This report uses data from X database"
Thanks!
DominiqueCreate a new dataset (data tab, select combo and new dataset).
Switch to generic designer (the button is to the right of the ...)
Put this in:
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140026946.331926.266950@.z14g2000cwz.googlegroups.com...
> How would you display a dataset's data source in a report?
> I want to say something like "This report uses data from X database"
> Thanks!
> Dominique
>|||Whoops, sent before done. OK here is the whole answer:
Create a new dataset (data tab, select combo and new dataset).
Switch to generic designer (the button is to the right of the ...)
Put this in:
declare @.DBID int
select @.DBID = db_id()
select db_name(@.DBID) as DatabaseName
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140026946.331926.266950@.z14g2000cwz.googlegroups.com...
> How would you display a dataset's data source in a report?
> I want to say something like "This report uses data from X database"
> Thanks!
> Dominique
>|||Dear Bruce:
I definitely feel on the right track, to get other information, I did
something like
declare @.DBID int
select @.DBID = db_id()
select db_name(@.DBID) as DatabaseName, @.@.version as Version ,
@.@.servername as ServerName
(which I would expect to only return a single row)
And to display, I created a list in layout view, and added the fields
within the list to text boxes. The biggest pain was that I had to
manually add the fields to the dataset, and sometimes VS .NET 2003
would reset the list of fields back to nothing.
Thanks for all the help!!!
Dominique|||You shouldn't have had to manually add the field names. Most likely you just
needed to click on the refresh fields button (to the right of the ..., it
looks like the refresh button of IE).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140038964.115390.256080@.f14g2000cwb.googlegroups.com...
> Dear Bruce:
> I definitely feel on the right track, to get other information, I did
> something like
> declare @.DBID int
> select @.DBID = db_id()
> select db_name(@.DBID) as DatabaseName, @.@.version as Version ,
> @.@.servername as ServerName
> (which I would expect to only return a single row)
> And to display, I created a list in layout view, and added the fields
> within the list to text boxes. The biggest pain was that I had to
> manually add the fields to the dataset, and sometimes VS .NET 2003
> would reset the list of fields back to nothing.
> Thanks for all the help!!!
> Dominique
>

Displaying data from multiple datasets in the same table

I have two datasets, the first one attached to a table, and I would like
to display a field from the second dataset based on a criteria. Think as an
analogy to joins, but for various reasons I cannot merge the queries to get
a single dataset (data is coming from different databases). So, I have a
common key and I want to lookup into the second dataset? How can I achieve
that?
Practical example: first dataset retrieves a list of people, with their
address and a column the contains the country code. In a different DB I have
a table that has associations for a country code a country name. How can I
display a list of people and the country name? No join at query level is
acceptable.
Regards,
MariusA data region can only be bound to one dataset. Why is a join in a query
unacceptable? Can you create a view in your database with a join in it and
create a dataset off of that view? Would that be an acceptable alternative?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
> I have two datasets, the first one attached to a table, and I would
like
> to display a field from the second dataset based on a criteria. Think as
an
> analogy to joins, but for various reasons I cannot merge the queries to
get
> a single dataset (data is coming from different databases). So, I have a
> common key and I want to lookup into the second dataset? How can I achieve
> that?
> Practical example: first dataset retrieves a list of people, with
their
> address and a column the contains the country code. In a different DB I
have
> a table that has associations for a country code a country name. How can I
> display a list of people and the country name? No join at query level is
> acceptable.
> Regards,
> Marius
>|||It's not acceptable because we are not using a database, but instead a
custom data extensions that gets data from our business layer objects. In
business layer we do complex calculations, so writing the reporting as pure
SQL queries wouldn't be feasible. So we have business layer object that
retrieves a list of persons, and a business layer that retrieves a table
with mapping from country codes to names. We would prefer not to implement
joins in our custom data extensions if it would be possible. Also, I guess
that subreports would do it, but is seems to be that it would be an
overkill, and would raise problems when going to numeric (orders with items,
and items with prices in a different dataset), because for example we could
have two different tables, and show the difference between two prices.
Regards,
Marius Cristian CONSTANTIN
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:Oc03VBHrEHA.1964@.TK2MSFTNGP12.phx.gbl...
>A data region can only be bound to one dataset. Why is a join in a query
> unacceptable? Can you create a view in your database with a join in it and
> create a dataset off of that view? Would that be an acceptable
> alternative?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
> message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
>> I have two datasets, the first one attached to a table, and I would
> like
>> to display a field from the second dataset based on a criteria. Think as
> an
>> analogy to joins, but for various reasons I cannot merge the queries to
> get
>> a single dataset (data is coming from different databases). So, I have a
>> common key and I want to lookup into the second dataset? How can I
>> achieve
>> that?
>> Practical example: first dataset retrieves a list of people, with
> their
>> address and a column the contains the country code. In a different DB I
> have
>> a table that has associations for a country code a country name. How can
>> I
>> display a list of people and the country name? No join at query level is
>> acceptable.
>> Regards,
>> Marius
>>
>

Displaying Data Columnwise in Reportviewer

hai,

iam new to the ReportViewer control.when i display data from Dataset it is displayed in Rowwise.ie,

if Name and place is to be displayed then it is displaying in

Name1 Place1

Name2 Place2

i want to display in column wise

ie,Name1 Place1 Name2 Place2

can anybody tell which control can perform this functionality?

HI, jobinthomasv:

Matrix is a data region that arranges data into columns and rows that intersect at specific data points. Matrices provide functionality similar to crosstabs and pivot tables. Unlike a table, which has a static set of columns, matrix columns can be dynamic. You can define matrices that contain static and dynamic rows and columns. For more information, seeAdding Matrix Data Regions.

http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx

|||

HI, jobinthomasv:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

|||

Thank you for your information sir...i want to display in following manner.i tried a lot but i can't achieve it.

My Crieteria:

I Used to Develop An attendence Register for employees.Each Employees have Multiple Login and Logout.

Nw My Issue is I want to Group Each Employees with having his own IN and Out on a particular day he Present.And at the same time since each employees having several login and logout for a particular day.I want to Wrap a matrix after certain columns and display the rest in next line for that day of that employee

ie,

i Want in this format

Employee Name: E1

Present Day 1

IN1 OUT1 IN2 OUT2 IN3 OUT3........after a particular column it should be wrapped and displayed in next line

IN4 Out4 IN5 OUT5............this process is continued until all the login and Logouts of tht employees is displayed...

Then

Present Day2

INs and Outs

Present Day3

INs and Outs

After The first Employees

Similar Format is For all the other employees

How can i achieve this??

Display Vlaue

Hi I am reporting off a dataset and I want to have a Display Value witch is the code rather then having the value witch is a Id is it possible?

All fields returned in your query should be available in design mode. If not then you may need to click the refresh button on the data tab for Visual Studio to re-create your dataset filed definitions. To access fileds from your dataset just use the following expression template:

= Fields!<<column_name>>.Value

When grouping of when you want to reference a filed outside of the context of a containing control e.g. matrix , table, list, then you have to use an aggregation function e.g. Sum, Avg, Max, Min, First, Last, and pass the dataset name as a scope e.g.

= First(Fields!<<column_name>>.Value, "<<dataset_name>>")

|||

Hi Adam

Thank you for replying but what I mean is having the actual column should be the ID but the display should be the Description or the code from a other dataset and I can’t use a join because I have the data in memory of my dataset

|||You need to do the join in your query and bring back an extra column (the description) in the same dataset as the ID. Use SQL for joins and use RS for presentation. RS has no capability for joining disperate datasets - not that I know of anyway.

Display top N records and its total

For a given dataset, I know how to display the top-N records. Is there
a way in reporting services to then show the total of those top-N
records?
For example, let's suppose I have a dataset of sales people and their
sales totals. By grouping on the sales person, I am able to display
the top 5 sales person base on their sales total. I then need to
display which % of total sales these top 5 sales person represent.
Some like this:
Top 5 sales
Bill 35%
Lucy 10%
Bob 7%
Jeff 5%
Kim 3%
Total: 60%
Is this possible?I figure out how to accomplish this. If anyone needs help doing this,
ping me.

Display N/A Something When Data Does Not Exist

Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
of Values
On my report I put a filter on a list filtering out the dates with
expressions. Such as = (colDate.value = myDate)
But some time myDate does not exist in the dataset, so the after the filter
the list has no results.
What i want to achieve in this case is to display an N/A. i tried something
like this in the textbox contorl inside the list
=IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
Any help is appreciated. ThanksHi,
Why not try something within your stored procedure
Select Col1(ISNULL, 'N/A') FROM TABLE
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||Is this in an actual "list" control? There is a property in the properties
panel, called "NoRows" .. you simply type the text you want to display when
there are no rows in the list. Hope that fixes your problem.
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||thanks that worked.
"isamu" wrote:
> Is this in an actual "list" control? There is a property in the properties
> panel, called "NoRows" .. you simply type the text you want to display when
> there are no rows in the list. Hope that fixes your problem.
> "gMaster" wrote:
> > Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> > of Values
> > On my report I put a filter on a list filtering out the dates with
> > expressions. Such as = (colDate.value = myDate)
> >
> > But some time myDate does not exist in the dataset, so the after the filter
> > the list has no results.
> >
> > What i want to achieve in this case is to display an N/A. i tried something
> > like this in the textbox contorl inside the list
> > =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> >
> > Any help is appreciated. Thanks

Display message when there is no data

I have a graph that filters a DataSet for itself. I want to display a message
when there are no rows after the filter.
I can't seem to use ReportItems! from a textbox to reference the graph and
the norows property on the graph does not seem to do anything.
Does anyone know how to do this?
DaveThis works for me. Just try the attached example at the bottom of this
posting, which filters e.g. on the inner column grouping and shows the
NoRows message. Also moving the filter to the entire chart works.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
news:F6D266A0-4833-480E-82C9-97A24844F867@.microsoft.com...
> I have a graph that filters a DataSet for itself. I want to display a
message
> when there are no rows after the filter.
> I can't seem to use ReportItems! from a textbox to reference the graph and
> the norows property on the graph does not seem to do anything.
> Does anyone know how to do this?
> Dave
=============================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="Chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<Color>Firebrick</Color>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Style>
<BackgroundColor>=Switch(Sum(Fields!UnitsInStock.Value) =Max(Fields!UnitsInStock.Value,"Chart1"), "Red",
Sum(Fields!UnitsInStock.Value) = Min(Fields!UnitsInStock.Value,"Chart1"),
"Yellow", true, "Green")</BackgroundColor>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>55</PointWidth>
<Type>Bar</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>6.375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="ProductCategory">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
<Filters>
<Filter>
<FilterExpression>=Fields!SupplierID.Value</FilterExpression>
<Operator>Equal</Operator>
<FilterValues>
<FilterValue>=-1</FilterValue>
</FilterValues>
</Filter>
</Filters>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="ProductName">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>3.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<NoRows>All rows are filtered out!</NoRows>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>
</BorderColor>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MinorTickMarks>Cross</MinorTickMarks>
<Min>0</Min>
<MajorInterval>20</MajorInterval>
<MinorInterval>5</MinorInterval>
<Interlaced>true</Interlaced>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Maybe I am too new to this or something ... but having you post the rdl didnt
tell me how you solved the issue. I am having the same problem but I dont
have a chart. I have set an expression in the NoRows property but it is not
showing.
Can you put that in english
"Robert Bruckner [MSFT]" wrote:
> This works for me. Just try the attached example at the bottom of this
> posting, which filters e.g. on the inner column grouping and shows the
> NoRows message. Also moving the filter to the entire chart works.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dlloyd" <Dlloyd@.discussions.microsoft.com> wrote in message
> news:F6D266A0-4833-480E-82C9-97A24844F867@.microsoft.com...
> > I have a graph that filters a DataSet for itself. I want to display a
> message
> > when there are no rows after the filter.
> >
> > I can't seem to use ReportItems! from a textbox to reference the graph and
> > the norows property on the graph does not seem to do anything.
> >
> > Does anyone know how to do this?
> >
> > Dave
>
> =============================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="Chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>20</Inclination>
> <Shading>Real</Shading>
> <WallThickness>25</WallThickness>
> <DrawingStyle>Cylinder</DrawingStyle>
> <Clustered>true</Clustered>
> </ThreeDProperties>
> <Style>
> <Color>Firebrick</Color>
> </Style>
> <Legend>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Excel</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Fields!UnitsInStock.Value</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> </DataLabel>
> <Style>
> <BackgroundColor>=Switch(Sum(Fields!UnitsInStock.Value) => Max(Fields!UnitsInStock.Value,"Chart1"), "Red",
> Sum(Fields!UnitsInStock.Value) = Min(Fields!UnitsInStock.Value,"Chart1"),
> "Yellow", true, "Green")</BackgroundColor>
> </Style>
> <Marker>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style>
> <FontSize>8pt</FontSize>
> </Style>
> <MajorGridLines>
> <Style>
> <BorderWidth>
> <Default>1.5pt</Default>
> </BorderWidth>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Dotted</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <MajorInterval>5</MajorInterval>
> <MinorInterval>1</MinorInterval>
> <CrossAt>0</CrossAt>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>55</PointWidth>
> <Type>Bar</Type>
> <Top>0.25in</Top>
> <Title>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </Title>
> <Width>6.375in</Width>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="ProductCategory">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> <Filters>
> <Filter>
> <FilterExpression>=Fields!SupplierID.Value</FilterExpression>
> <Operator>Equal</Operator>
> <FilterValues>
> <FilterValue>=-1</FilterValue>
> </FilterValues>
> </Filter>
> </Filters>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="ProductName">
> <GroupExpressions>
> <GroupExpression>=Fields!ProductName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ProductName.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>3.25in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Stock</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>OldLace</BackgroundColor>
> </Style>
> </PlotArea>
> <NoRows>All rows are filtered out!</NoRows>
> <Left>0.25in</Left>
> <ValueAxis>
> <Axis>
> <Title>
> <Caption>Amount</Caption>
> <Style />
> </Title>
> <Style>
> <BorderColor>
> <Default>IndianRed</Default>
> </BorderColor>
> <FontSize>8pt</FontSize>
> </Style>
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderColor>
> <Default>Blue</Default>
> </BorderColor>
> <BorderStyle>
> <Default>Dashed</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderColor>
> <Default>CornflowerBlue</Default>
> </BorderColor>
> <BorderStyle>
> <Default>Dotted</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MinorTickMarks>Cross</MinorTickMarks>
> <Min>0</Min>
> <MajorInterval>20</MajorInterval>
> <MinorInterval>5</MinorInterval>
> <Interlaced>true</Interlaced>
> <Margin>true</Margin>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>3.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.75in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SupplierID">
> <DataField>SupplierID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryID">
> <DataField>CategoryID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="QuantityPerUnit">
> <DataField>QuantityPerUnit</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="UnitsInStock">
> <DataField>UnitsInStock</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="UnitsOnOrder">
> <DataField>UnitsOnOrder</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="ReorderLevel">
> <DataField>ReorderLevel</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="Discontinued">
> <DataField>Discontinued</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="NullUnits">
> <DataField>NullUnits</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT TOP 10 *, NULL AS NullUnits
> FROM [Alphabetical list of products]
> WHERE (UnitsOnOrder > 0)</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>

display list problem

dear all
i have a dataSet in this way
ID NAME
1 VALUE1
2 VAlue2
3 VALUE3
4 VALUE4
5 VALUE5
i need to display this daya as the following
1- VALUE1 2- VALUE2 3- VALUE3
4- VALUE4 5- VALUE5
is there are anyway
thanksBolos,
A few options for you here,
You could use the multi-column feature. I think it is the Body's
Columns property.
Or, if we are talking about low volumes of data you pivot in-line. The
following is based on a code snippet in an article by Clinton Herring
on SQLServerCentral.com. You could use as is for one line or stick
inside a loop for a few more
SELECT @.tmp_values = @.tmp_values + convert(varchar(20), column_data) +
','
FROM DB.dbo.many_tbl
WHERE id = @.tmp_id
ORDER BY column_data
SELECT @.tmp_values
Chris
Bolos wageh wrote:
> dear all
> i have a dataSet in this way
> ID NAME
> 1 VALUE1
> 2 VAlue2
> 3 VALUE3
> 4 VALUE4
> 5 VALUE5
> i need to display this daya as the following
> 1- VALUE1 2- VALUE2 3- VALUE3
> 4- VALUE4 5- VALUE5
> is there are anyway
> thanks

Display in pageHeader

Hi,

i got one problem. I want to show my username in pageHeader.when i drag and drop my value from dataset to pageheader its giving some error.

in Body its working.but i want show only in page header that username.

can any body help me.

kiran

common workaround for header errors occuring is this

create a textbox in your body, make it hidden in the textbox properties (hidden=true)

put the data you are interested in in the expression for the textbox

(e.g. in your case =User!UserID )

then in the header you can reference this as follows

= reportItems!<yourtextboxnamehere>.Value

|||there is also another way to display dataset value in Page Header/ Page Footer. It is that u do one thing that make one internal parameter for that field and put textbox in header/footer and go into expressions then click on parameter, u will get the list of parmeter select that parameter which u want.|||

Hi Thanx for reply. I know this one but actual problem is its not getting in my first page from 2 pages onewards only my user name is coming.because first header will come so first time no data. second time onwards its coming. i want to show in first page also?

thanx

|||You say that it is not appearing on the first page.

I'm only aware of the ability to supress after the first page and not the inverse of this.

Display first non NULL value from my dataset?

Hello,

I would like to display the first non null value from my dataset, is this possible? I am aware of first() and last() but what are my options for displaying the aforementioned? Thanks much!

John

I'm not sure if you want to exclude the row completely or for a particular textbox just get the first non-Null value, but you may want to look into using dataset or data region filtering. With filtering you can filter the dataset to remove any rows with the Null value, and then use the First Aggregate function.

Ian|||Thanks for the reply, I am trying to get the first non-null value from a field in my dataset and place it in a textbox. I am aware of the filtering techniques, however, the entire dataset is required for a graph I have above the textbox that I would like to put that value in--I'm gona keep digging, perhaps I need to write my own function for this?|||What you may be able to do is create a Matrix that has a Data Region level filter, which filters out the unneeded rows. The Matrix should contain just a data cell, with no added Rows or Columns. (In the Report Designer, it should contain a default dynamic row and column with groups containing no group expressions and empty textboxes.) And the data cell is the textbox with the expression using the First Aggregate function.

Ian

Display details section for each group

Hi,

I want to display details ection for each group in a single table using one dataset.

For ex:

Group 1 Row --> Name Age

Details 1 Row--> XXX 30

Details 2 Row--> YYY 20

Group 2 Row --> City Country

Details 1 Row--> CCC ZZZZ

Details 2 Row--> BBB AAAA

Hi,

Read this article in BOL please: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/a4aa29f9-eaa4-4116-8e22-87748c5ef0b8.htm

It is much more easier tham I write here

Regards,

Janos

|||

Hi Janos,

Thanks for the reply. But i am not able to access the url given by you. Can you please provide me the correct URL?

Thanks in Advance.,

Parimal

|||

Hi,

it is in the books online for SQL server 2005. Try to copy/paste into it, or open it on the MS website at here: http://technet.microsoft.com/en-us/library/ms159169.aspx

Regards,

Janos