Extra white space in table

I've got a table with a header, group, and detail row. The detail row
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql

Extra Row Needed

Here is the basic sql I am trying to implement:

select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

Here is my result set:

A1 3 2006-07-01 00:00:00.000
A1 10 2006-08-15 00:00:00.000
A1 11 2006-09-15 00:00:00.000
A1 10 2006-10-15 00:00:00.000
A1 10 2006-11-01 00:00:00.000
A1 10 2006-11-30 00:00:00.000

If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:

A1 0 (today's date)

Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.Try this:

select classid, count(*) as [COUNT], dtmready
into #tempunit
from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

if ((select min(dtmready) from #tempunit)>getdate())
insert into #tempunit values ('a1','0',getdate())

select * from #tempunit

This should work?|||Thanks for the response.

I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?

Thanks, Mike|||I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
Regardless, here is another method:select classid,
count(*) as [COUNT],
dtmready
from unit
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
classid,
dtmready
UNION
select classid,
0 as [COUNT],
getdate() as dtmready
from unit
group by classid
having min(dtmready) > getdate()
order by dtmready;|||That did it! Thanks so much for your help!

Mike

External image problem when sorting in table

Reporting Services I am using external image png as my header for reports When i am clicking on sort row of the report the header image is dissapeared.Though it is working well in Visual Studio 2005 the problem is occuring after deploying it in reportserver.

What the reason and how can i remove this error.It was working well when i had embeded the image..But i need to have external image only..

Did you install SP1 on the report server (http://www.microsoft.com/sql/sp1.mspx)?

-- Robert

Expressions in Height property of a Table

Is it possible to use an expression to set the Height property of a row
in a Table? If so, show me an example.I don't think this is possible, but the hight will can be set to change in
accordance with the content
"Rlane" wrote:
> Is it possible to use an expression to set the Height property of a row
> in a Table? If so, show me an example.
>

Expression to Parameter

I would like my header text not to be hard coded

I took a very big table which include texts i need from my DB to DS

How can i pick a single row with single data in it to be bind

to my Paramter ?

Could you explain this in more detail ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

expression sum and iif help?

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

Hi Nick,

The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.

Hope this helps.

Regards,

Pradeep

|||

Hi,

I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.

Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.

eg:

Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))

Somiya

|||

Thanks Chaubey and Somiya,

You guys are right, after i explicitly convert to same type , then it works.

Cheers

Nick

expression based on multiple values in a dataset

Hi
I am trying to seth the background colour of a textbox to either red or
green depending on the multiple values in my dataset.
Each row of my dataset has a boolean value for Pass. The background colour
of the text box needs to be green if all rows in my dataset have a Pass
value of True but if only one row in the dataset has a Pass value of False,
the background colour needs to be set to red.
I have tries the obvious expression =IIF(Fields!Pass.Value = True, "Green",
"Red") but this will set the background to green again if their is a row in
the dataset with a Pass value of True after the row which had the Pass value
of False.
I have also tried counting the number of rows with a specific value but this
does not seem to work.
=IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
Does anyone have any ideas how to get what I want?
Thanks
Lewis Holmes
eNateTry this for multiple values:
=iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"l.holmes" <enate@.newsgroups.nospam> wrote in message
news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Hi
> I am trying to seth the background colour of a textbox to either red or
> green depending on the multiple values in my dataset.
> Each row of my dataset has a boolean value for Pass. The background colour
> of the text box needs to be green if all rows in my dataset have a Pass
> value of True but if only one row in the dataset has a Pass value of
> False, the background colour needs to be set to red.
> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
> "Green", "Red") but this will set the background to green again if their
> is a row in the dataset with a Pass value of True after the row which had
> the Pass value of False.
> I have also tried counting the number of rows with a specific value but
> this does not seem to work.
> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
> Does anyone have any ideas how to get what I want?
> Thanks
> Lewis Holmes
> eNate
>|||Hi Robert
Thanks for the reply.
I do not think this will work still. For example say my dataset has three
rows which have Pass values of True, False and True.
As i understand, using this expression for the first row, the expression
will evaluate to Green as Value is True. Then for the second row the
expression will evaluate to Red as value is False (this is all correct).
However, my problem is that now after evaluating the expression for third
row, the value returned will be Green as Value is true and so sum returns 1.
This is not the behaviour I want as the background colour should be Red if
one or more of the Pass values is false.Using this expression, the result
from the third row is setting the background colour back to green.
Hope this explains the problem better.
Kind Regards
Lewis Holmes
eNate
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eO1NksR2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Try this for multiple values:
> =iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "l.holmes" <enate@.newsgroups.nospam> wrote in message
> news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> I am trying to seth the background colour of a textbox to either red or
>> green depending on the multiple values in my dataset.
>> Each row of my dataset has a boolean value for Pass. The background
>> colour of the text box needs to be green if all rows in my dataset have a
>> Pass value of True but if only one row in the dataset has a Pass value of
>> False, the background colour needs to be set to red.
>> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
>> "Green", "Red") but this will set the background to green again if their
>> is a row in the dataset with a Pass value of True after the row which had
>> the Pass value of False.
>> I have also tried counting the number of rows with a specific value but
>> this does not seem to work.
>> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
>> Does anyone have any ideas how to get what I want?
>> Thanks
>> Lewis Holmes
>> eNate
>|||Hi Lewis,
In you case, I understood if there is one backgroup to be Red (false), you
want all following backgroup to be shown as Red (false). If I have
misunderstood your concern, please feel free to point it out.
I am afraid there won't be an easy to accomplish this. You may try using
.net assembly to identify the color according to row number with custom
function.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

expression

In RS, is it possible to code an expression so that if a field contains the
word "STAT" that whole row is highlighted in another color? If not, how about
a that field?
I can do it with numeric values, not text.
Thanks.On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> In RS, is it possible to code an expression so that if a field contains the
> word "STAT" that whole row is highlighted in another color? If not, how about
> a that field?
> I can do it with numeric values, not text.
> Thanks.
In Layout view, select the field that you want to change the
background color for and select F4 (for the Properties window). To the
right of Background Color, select <Expression...> and enter an
expression similar to the following:
=iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That worked. I ad the syntax wrong. Thanks!
"EMartinez" wrote:
> On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > In RS, is it possible to code an expression so that if a field contains the
> > word "STAT" that whole row is highlighted in another color? If not, how about
> > a that field?
> >
> > I can do it with numeric values, not text.
> >
> > Thanks.
>
> In Layout view, select the field that you want to change the
> background color for and select F4 (for the Properties window). To the
> right of Background Color, select <Expression...> and enter an
> expression similar to the following:
> =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On May 24, 9:12 pm, brian <b...@.discussions.microsoft.com> wrote:
> That worked. I ad the syntax wrong. Thanks!
> "EMartinez" wrote:
> > On May 24, 2:46 pm, brian <b...@.discussions.microsoft.com> wrote:
> > > In RS, is it possible to code an expression so that if a field contains the
> > > word "STAT" that whole row is highlighted in another color? If not, how about
> > > a that field?
> > > I can do it with numeric values, not text.
> > > Thanks.
> > In Layout view, select the field that you want to change the
> > background color for and select F4 (for the Properties window). To the
> > right of Background Color, select <Expression...> and enter an
> > expression similar to the following:
> > =iif(Fields!FieldName.Value Like "*STAT*", "Orange", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Expressing a One to Many Relationship in a Single Row

I've two tables.

Table Charge

Table Payments

Charge has a one to many relationship with payments.

I have to transform the data from this new system of normalized tables over to a text file so that it may be read into an old mainframe computer. For each record I need to include the charge details and up to 10 payment details all on the same row.

Like this:

Charge1, ChargeDate, ChargeAmount, Payment1Amount, Payment2Amount, Payment3Amount

Charge2, ChargeDate, ChargeAmount, Payment1Amount, Payment2Amount, Payment3Amount

etc.

If their are less than 10 payments, the row has to be padded.

All of this is going to be DTS'd to a text file.

Is there a query that can handle this?

Simple answer yes:

Without seeing the table structures I can write some code based on these assumptions:

1) when you say Charge1, Charge2 that this is an ID field so I named it ChargeID

2) The Payments table carries the fieldname Payment.

3) Payments table carries the ChargeID as well for reference.

4) Payments carries the payment# represented here as paymentnum

Code Snippet

select distinct chargeID, ChargeDate, ChargeAmount

, pay1.payment [Payment1Amount]

, pay2.payment [Payment2Amount]

, pay3.payment [Payment3Amount]

, pay4.payment [Payment4Amount]

, pay5.payment [Payment5Amount]

, pay6.payment [Payment6Amount]

, pay7.payment [Payment7Amount]

, pay8.payment [Payment8Amount]

, pay9.payment [Payment9Amount]

, pay10.payment [Payment10Amount]

from charge

left join payments [pay1] on pay1.ChargeID = charge.ChargeID and pay1.paymentnum = 1

left join payments [pay2] on pay2.ChargeID = charge.ChargeID and pay2.paymentnum = 2

left join payments [pay3] on pay3.ChargeID = charge.ChargeID and pay3.paymentnum = 3

left join payments [pay4] on pay4.ChargeID = charge.ChargeID and pay4.paymentnum = 4

left join payments [pay5] on pay5.ChargeID = charge.ChargeID and pay5.paymentnum = 5

left join payments [pay6] on pay6.ChargeID = charge.ChargeID and pay6.paymentnum = 6

left join payments [pay7] on pay7.ChargeID = charge.ChargeID and pay7.paymentnum = 7

left join payments [pay8] on pay8.ChargeID = charge.ChargeID and pay8.paymentnum = 8

left join payments [pay9] on pay9.ChargeID = charge.ChargeID and pay9.paymentnum = 9

left join payments [pay10] on pay10.ChargeID = charge.ChargeID and pay10.paymentnum = 10

You can modify this to match your field names. If you don't have a simple paymentnum field, and they need to order by date or something, then I'll or somebody else will have to think a little longer.

right now the empty rows will come out NULL.

However you can represent them as blank or 'no payment' by this:

Code Snippet

case when pay10.payment is null then 'no payment' else pay10.payment end [Payment10Amount]

|||

You could use CTE + row_number() function for with task:

Code Snippet

createtable Charge

(

Id int,

ChargeDate datetime,

ChargeAmount decimal

)

createtable Payments

(

ChargeID int,

PaymentAmount decimal

)

insertinto Charge values(1,'2007-01-01', 1000)

insertinto Payments values(1,10)

insertinto Payments values(1,1)

insertinto Payments values(1,13)

insertinto Payments values(1,12)

insertinto Payments values(1,14)

insertinto Payments values(1,15)

insertinto Payments values(1,17)

insertinto Payments values(1,18)

insertinto Payments values(1,19)

insertinto Charge values(2,'2007-02-01', 100)

insertinto Payments values(2,37)

insertinto Payments values(2,38)

insertinto Payments values(2,49)

with CTE

as

(

--Numerate Payments for each ChargeID

select ChargeID , p.PaymentAmount,

row_number()over(Partitionby ChargeID Orderby ChargeID )as num

from Payments p

)

select

Id, ChargeDate, ChargeAmount

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=1)as PaymentAmount1

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=2)as PaymentAmount2

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=3)as PaymentAmount3

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=4)as PaymentAmount4

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=5)as PaymentAmount5

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=6)as PaymentAmount6

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=7)as PaymentAmount7

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=8)as PaymentAmount8

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=8)as PaymentAmount9

,(selecttop 1 PaymentAmount from CTE where CTE.ChargeID=ID and num=10)as PaymentAmount10

from Charge

Also check output of following query for understanding:

Code Snippet

select ChargeID , p.PaymentAmount,

row_number()over(Partitionby ChargeID Orderby ChargeID )as num

from Payments p

|||

Robert,

Thanks for trying, but the payment table could return up to 10 separate rows.

I've found that the answer seems to be temporary tables and cursors.

Once I get an answer, I'll post it here.

|||

I'm not sure what you mean here I realize that in the Payment table there will be 10 seperate rows. Hence the joining 10 different times the same table.

The structure that we are nomalizing is this correct?

Charge

charge1 amount, payment

Then in the payment table:

Charge1 payment1 $20

Charge1 payment2 $20

Charge1 payment3 $30

... and so on up to 10 times...

the above code will work for this... It is in fact built to handle up to 10 rows in the payment table for one charge.

BTW I was editing when you replied, I added on the section below about changing nulls to 'no payment' and other things you can do with the fields returned.

|||

You can use the following query and simply export it as text file,

Code Snippet

Create Table #charge (

[cid] int ,

[cname] Varchar(100) ,

[cdate] datetime

);

Insert Into #charge Values('1','Charge1','1/1/2007');

Insert Into #charge Values('2','Charge2','1/2/2007');

Insert Into #charge Values('3','Charge3','1/3/2007');

Create Table #payment (

[pid] int ,

[cid] int ,

[pamount] money

);

Insert Into #payment Values('1','1','1000');

Insert Into #payment Values('2','1','10000');

Insert Into #payment Values('3','1','100');

Insert Into #payment Values('4','1','6000');

Insert Into #payment Values('5','1','9000');

Insert Into #payment Values('6','1','100');

Insert Into #payment Values('7','1','100000');

Insert Into #payment Values('8','1','10098');

Insert Into #payment Values('9','1','1000');

Insert Into #payment Values('10','1','1029');

Insert Into #payment Values('16','1','34533');

Insert Into #payment Values('11','2','1000000');

Insert Into #payment Values('12','2','1928');

Insert Into #payment Values('13','2','100');

Insert Into #payment Values('14','2','10000');

Insert Into #payment Values('15','3','100');

Code Snippet

--SQL Server 2000

Select C.Cid,Cname,cdate,pamount into #Temp from #charge C Join #payment P on C.Cid=P.Cid

--Adding the Row Number, Grouped Row Number

Alter table #Temp Add RowId Int Identity(1,1), GroupId int, RowGroup Int

--Finding the Grouped Row Number

Update #Temp

Set

GroupId = RowId - (Select Min(RowID)-1 From #Temp Sub Where #Temp.Cid = Sub.Cid);

--Finding the Row Group

Update #Temp

Set RowGroup = (GroupId-1)/10

--Refine the Grouped Row Number

Update #Temp

Set GroupId = GroupId - (Select Min(GroupId)-1 From #Temp Sub Where #Temp.Cid = Sub.Cid And #Temp.RowGroup = Sub.RowGroup)

--Display the Conetent

Select

RowGroup+1 RowNumber

,CName

,Cdate

,Isnull(Cast(Max(Case When GroupId=1 Then pamount End) as Varchar),'') Payement1Amount

,Isnull(Cast(Max(Case When GroupId=2 Then pamount End) as Varchar),'') Payement2Amount

,Isnull(Cast(Max(Case When GroupId=3 Then pamount End) as Varchar),'') Payement3Amount

,Isnull(Cast(Max(Case When GroupId=4 Then pamount End) as Varchar),'') Payement4Amount

,Isnull(Cast(Max(Case When GroupId=5 Then pamount End) as Varchar),'') Payement5Amount

,Isnull(Cast(Max(Case When GroupId=6 Then pamount End) as Varchar),'') Payement6Amount

,Isnull(Cast(Max(Case When GroupId=7 Then pamount End) as Varchar),'') Payement7Amount

,Isnull(Cast(Max(Case When GroupId=8 Then pamount End) as Varchar),'') Payement8Amount

,Isnull(Cast(Max(Case When GroupId=9 Then pamount End) as Varchar),'') Payement9Amount

,Isnull(Cast(Max(Case When GroupId=10 Then pamount End) as Varchar),'') Payement10Amount

from

#Temp

Group By

CId

,CName

,Cdate

,RowGroup

Code Snippet

-SQL Server 2005

;With Ordered

as

(

Select C.Cid,Cname,cdate,pamount,Row_Number() OVER (Partition By C.Cid Order By C.Cid) GroupRowId from #charge C Join #payment P on C.Cid=P.Cid

)

, Grouped

as

(

Select Cid,Cname,Cdate,pamount,(GroupRowId-1)/10 RowGroup From Ordered

)

, ReGrouped

as

(

Select Cid,Cname,Cdate,pamount,RowGroup,Row_Number() Over(Partition By RowGroup Order By RowGroup) GroupRowId From Grouped

)

Select

RowGroup + 1 RowNumber

,CName

,CDate

,Isnull(Cast([1] as Varchar),'') Payment1Amount

,Isnull(Cast([2] as Varchar),'') Payment2Amount

,Isnull(Cast([3] as Varchar),'') Payment3Amount

,Isnull(Cast([4] as Varchar),'') Payment4Amount

,Isnull(Cast([5] as Varchar),'') Payment5Amount

,Isnull(Cast([6] as Varchar),'') Payment6Amount

,Isnull(Cast([7] as Varchar),'') Payment7Amount

,Isnull(Cast([8] as Varchar),'') Payment8Amount

,Isnull(Cast([9] as Varchar),'') Payment9Amount

,Isnull(Cast([10] as Varchar),'') Payment10Amount

From

ReGrouped

PIVOT

(

Max(pamount) For GroupRowId In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])

) as Pvt

|||

WOW.

Thanks for the options guys.

I'll be looking into each one of these.

Exporting to CSV

I have a report which is just a simple table with only the detail row (which then as about a dozen columns)

I have uploaded this to report manger. The report generates correctly and I export to CSV.

But when I open the CSV file there are Headings at the top. I want to know is there any way of getting rid of there headers so it is jus the data which shows.

(The headers seem to be the name of the textbox column in reporting services)

See this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1487851&SiteID=1

cheers,

Andrew