Conditional space between rows in same group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Conditional space between rows in same group

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-10-19 : 07:54:16

Hi there,

The report I'm developing displays costs for jobs that have been done by a utilities company. There are several groups and one of those groups has information on Sales invoices, material costs, sub-contractors etc. The Sales invoice is always the first record that will be displayed when the job record is drilled down.

If a Sales invoice exists, then I'd like there to be a space between that row and the other rows within the same group. However, if a Sales invoice does not exist, then I'd like no space to exist between the "fist" row and the others.

For example, the diagram below shows a record with a sales invoice and a space between that row and the others.



	Job Name	Store	Invoice	Cost
Group 1	J123	Glasgow store		
Group2	Sales Invoice		100	
Group2				
Group2	Direct Labour			200
Group2	Material Costs			100
Group2	Sub-Contractor			50

However, the record below shows a record without a sales invoice and there are no spaces between the rows:


	Job Name	Store	Invoice	Cost
Group 1	J321	EK Store		
Group2	Direct Labour			100
Group2	Material Costs			100
Group2	Sub-Contractor			70

Does anyone know if this is possible? I'd imagine I may need VB for this but don't have a lot of experience with it.

Any help would be great, thanks

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-19 : 08:12:28

I am not sure how you could create a new blank row BUT can you have your SQL always insert a blank row after and then only display it on the HIDE value with an expression similar to:

IIF(Fields!YourTriggerField.Value = Previous(Fields!YourTriggerField.Value, false, true)

?

John

cidr
Posting Yak Master

207 Posts

Posted - 2009-10-19 : 08:27:35

Thanks for responding John.

I actually managed to get a way with this by using IIF. For example. I added a blank row within the same group below the row with the fields and used:

=IIF(fields!CategoryName.Value = "SALES INVOICES", FALSE, TRUE)

That seems to have worked!

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-10-19 : 08:59:41

jeez, I totally missed the simpler answer. Need to remember that one...

Cheer

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources