Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Row visibility conundrum Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:31 AM
Points: 15, Visits: 64
Hi

First, Merry Christmas to everyone who reads this!

I am trying to create a SSRS 2012 Reportbuilder 3.0 sales reports with drill down

My Rows are
Region
Customer
Invoice
PartNum

Columns are
FiscalYear

Aggregates are
SalesDollars

When I first open my report I would like to see just customers and then be able to drill down to invoices and then partnumbers for a selected invoice. This is simple and works fine

The twist is that when I drill down in a customer I only want to show invoices from the latest fiscal year.

I have a HIDDEN visibility expression for the invoices group (sort year is a parameter to pick out the year to show)
=Fields!FiscYr.Value <> Parameters!SortYear1.Value

The problem is that when I first run the report I see the customer row AND the invoices for the selected year and when I click on the customer drill down I then see invoices for all years..

I have experimented with various permutations in the visibility settings but nothing seems to work

Does anyone have any insight into this?

Thank you

Tom D

Post #1523707
Posted Wednesday, December 18, 2013 1:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 7:34 AM
Points: 44, Visits: 211
So you have a Table instead a Matrix. It's Right? can you attach a Desgin view of your Report?
Post #1523984
Posted Wednesday, December 18, 2013 7:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:37 AM
Points: 612, Visits: 2,146
rather than use the Hidden property, you could pass the fiscal year to the subreport and filter your data there instead.
Post #1524126
Posted Wednesday, December 18, 2013 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:31 AM
Points: 15, Visits: 64
Hi

It is a matrix

I guess to re-phrase the issue ... when I click on customer to drill down and see the invoices I would only like to display invoices from a single fiscal year (column) ... and have just blanks in the other 5 or 6 fiscal years (columns) which may be visible



My rationale is that for a sales report the relevant invoices to look at are in the current fiscal year and not the previous years

Does this make sense?

Thanks for looking

Tom
Post #1524261
Posted Thursday, December 19, 2013 2:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 7:34 AM
Points: 44, Visits: 211
Look at my attachment. It's a zip with a sql script and a Report Project.
It's not a Virus :)

In Report1 you can find something like what you need.

Let me Know if it works.


  Post Attachments 
TrialRunning.zip (3 views, 22.28 KB)
Post #1524497
Posted Friday, December 20, 2013 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:31 AM
Points: 15, Visits: 64
Thank you VERY VERY much!!! I will take study it this weekend!

I hope that you have a wonderful Christmas!

Tom D
Post #1525034
Posted Friday, December 20, 2013 9:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 7:34 AM
Points: 44, Visits: 211
Hope that it' works.
Merry Christmas to you
Post #1525059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse