SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row visibility conundrum


Row visibility conundrum

Author
Message
tdiroff
tdiroff
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 66
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
ndiro
ndiro
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 216
So you have a Table instead a Matrix. It's Right? can you attach a Desgin view of your Report?
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 2354
rather than use the Hidden property, you could pass the fiscal year to the subreport and filter your data there instead.
tdiroff
tdiroff
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 66
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
ndiro
ndiro
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 216
Look at my attachment. It's a zip with a sql script and a Report Project.
It's not a Virus Smile

In Report1 you can find something like what you need.

Let me Know if it works.
Attachments
TrialRunning.zip (9 views, 22.00 KB)
tdiroff
tdiroff
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 66
Thank you VERY VERY much!!! I will take study it this weekend!

I hope that you have a wonderful Christmas!

Tom D
ndiro
ndiro
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 216
Hope that it' works.
Merry Christmas to you :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search