report question - SQL Server Forums

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

report question

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-28 : 14:52:06

result:

ssrs 2008r2
I need to be able to do this and collapse on beta and production fields.
http://postimage.org/image/7lau6ses7/
------------------------------------------------------------------

following table is the data.

create table temp (names varchar(30), beta int, production int, dates date)

insert temp
select 'google', 1, 2, '20111001' union all
select 'yahoo', 5, 6, '20111001' union all
select 'google', 10, 20, '20111002' union all
select 'yahoo', 200, 300, '20111002'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 01:15:07

add a tablix with beta production etc in columns and add a column group on them containing. then add details inside that to show date fields. then use toggle visibility property as in below link

http://lukehayler.com/2009/07/custom-visibility-toggling-in-ssrs/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 03:17:21

i've posted a sample report i have created for above scenario here. have a look at it to understand how you can do it

http://www.2shared.com/file/R0cKxJI1/Report5.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-30 : 00:59:54

thanks V,
looks like the names are not showing.

http://imageshack.us/f/508/clipboard01fi.jpg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 01:05:09

nope it showing for me. I think you have made some modifications to what i gave

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-30 : 01:29:58

does it matter?

i use ssrs 2008 r2
ms visual stuido 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 01:33:10

quote:
Originally posted by basicconfiguration

does it matter?

i use ssrs 2008 r2
ms visual stuido 2008

nope not at all
but if you've messed with expressions that i've written inside report behaviour will change

FYI i've downloaded it from link and checked. its still working fine for me showing the names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-31 : 15:45:37

thanks V,
Somehow when I run it in the office works and when I'm at home I added a 3rd column and it works as well.

Now I have another q. I will like to get the total when I collapse the fields.

result:
http://postimage.org/image/bwa6496cb/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-01 : 14:46:23

Cool thanks, How can I hide the value when it's collapsed?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:31:41

quote:
Originally posted by basicconfiguration

Cool thanks, How can I hide the value when it's collapsed?

give visibility options as toggle visibility based on report item and select the textbox where you've collapse button. give default visibility option as hide

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-03 : 18:58:09

Thanks V,

I added new data set and total in a column. However when i export it to excel I see the total (extra row).
I like to know how to remove the extra row.

See pic. extra row in yellow.
http://www.2shared.com/photo/pyOENgHP/extra.html?

download rdl here:
http://www.2shared.com/file/09DQ_dK_/Report5.html

create table temp (groups varchar(50), names varchar(30), beta int, production int, dates date)

insert temp
select 'group1', 'google', 1, 2, '20111001' union all
select 'group1', 'yahoo', 5, 6, '20111001' union all
select 'group2', 'apple', 10, 20, '20111002' union all
select 'group2', 'microsoft', 200, 300, '20111002'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:31:00

what have you set for visibility property of total?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-04 : 12:34:45

visibility is set to "Show" but I want it to show when beta and production collapse. I just don't want it to show the total when the group1 AND group2 collapse

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 13:11:20

quote:
Originally posted by basicconfiguration

Thanks V,

I added new data set and total in a column. However when i export it to excel I see the total (extra row).
I like to know how to remove the extra row.

See pic. extra row in yellow.
http://www.2shared.com/photo/pyOENgHP/extra.html?

download rdl here:
http://www.2shared.com/file/09DQ_dK_/Report5.html

create table temp (groups varchar(50), names varchar(30), beta int, production int, dates date)

insert temp
select 'group1', 'google', 1, 2, '20111001' union all
select 'group1', 'yahoo', 5, 6, '20111001' union all
select 'group2', 'apple', 10, 20, '20111002' union all
select 'group2', 'microsoft', 200, 300, '20111002'

i think you've an additional row outside row group which is causing this. can you check if thats the case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-11-04 : 20:16:46

I think I got it thanks...I removed the collapse + sign and its working for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:27:17

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources