none
Show/Hide items in series on chart

    Question

  • I have done this before and can't seem to figure it out now.  I have a bar chart with date on the X axis and a dollar amount for each date as the bar.  I would like to be able to show only the last 30 days programmatically.  I believe this is the Series Properties > Visibility property.  I am using the following formula in the expression for that property:

    =iif(Fields!PayDate.Value>=Parameters!StartDate.Value, true, false)

    It's showing all points regardless of whether the PayDate is greater or less than the date specified by the user in the parameter.  This is coming from a database and the PayDate field is truly a date data type, the X axis is scalar, not category (as I believe it should be).  Any idea how to achieve this?

    In case you're wondering, I don't want to filter using a parameter in the database query because I have a calculated series that is a moving average over 7 days.  If you don't have data from 7 days prior to your first date it won't show the moving average.  Thus, I just hide the time span rather than not returning it in the query.

    Tuesday, July 23, 2013 9:56 PM

All replies

  • Hi Mateoc,

    I have tested it on my local environment (SQL Server Reporting Services 2008 R2 on Windows 7), I got the same results with yours. Base on my test, the expression
    =iif(Fields!PayDate.Value>=Parameters!StartDate.Value, true, false)
    Use the lase value of [PayDate] field to compare to parameter value, so when last value>=parameter's value, all the
    PayDate show. And if the last value <parameter's value, all the PayDate hide.

    One way to work around this issue is that add filter to Category Groups, if this is not you want, I am afriad there is no other approch to work around this issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, July 25, 2013 7:43 AM
    Moderator
  • Try this. Add the following dataset for testing:

    IF OBJECT_ID('tempdb..#sample_data') IS NOT NULL  DROP TABLE #sample_data
    
    create table #sample_data
    (
    PayDate datetime,
    DollarAmt decimal(4,2)
    )
    
    insert #sample_data values('7/3/13 8:50:00',14.25),('7/3/13 9:25',7.25),('7/3/13 13:35',12.5),
    ('7/8/13 16:30',3.3),('7/8/13 20:23',1.65),('7/8/13 9:09',6.6),('7/11/13',11.00),
    ('2/3/13',14.25),('4/3/13',14.25),('5/6/13',14.25),('6/7/13',14.25),
    ('1/1/13',14.25),('9/1/12',14.25),('2/18/13',14.25),('2/19/13',14.25)
    
    select * from #sample_data

    Add this parameter as DateTime: StartDate

    Then click on the Axis properties where you have dates on.  (Although you said you have a bar chart, I think you really have a column chart because this is what I have and I have Date on the X axis and Dollar Amt on Y Axis). 

    After clicking X Axis properties, choose Scalar (Numbers/Dates).  Uncheck "Always include zero" and click on the expression for Minimum.  Select this StartDate parameter.  Press OK twice and then run this report.  It will return only records from above greater than the entered date.


    Ryan D

    Friday, July 26, 2013 1:01 PM
  • This works beautifully!  With one exception.  First, you're right, it's a column chart, not a bar chart.  I actually have two series, so two side by side columns on each date - blue on the left, red on the right.  Each of them displays fine, except the very first one.  For the very first date (on the far left of the chart) only the red bar is displayed, like it's just being visually chopped off.  It's chopped off at the tick mark, which, for all other dates, is directly between the blue and red bars.  Does that make sense?

    Any idea how to "shift" the chart to the right a bit or force the tick mark to the left bar or some other work around?

    Thanks!

    Friday, July 26, 2013 4:49 PM
  • I'm not sure exactly what you are saying.  Would you post a screenshot of how this graph looks currently? 

    Ryan D

    Friday, July 26, 2013 7:03 PM
  • Friday, July 26, 2013 8:08 PM
  • What happens when you enter 6/19/12 for the StartDate?

    Ryan D

    Friday, July 26, 2013 11:10 PM
  • Hmm... works fine.

    Check this out.  All of my dates are the first of the month, so that helps.  Maybe I'll use the query below for my actual calculations, but make my start date (pseudo-code here)

    First of the month of start date parameter chosen - 14 days

    My query:

    SELECT CAST (CAST (MONTH (CAST (ISNULL (dc.NewText, nwds.DataDescription) AS DATE)) AS VARCHAR) + '-01-' + CAST (YEAR (CAST (ISNULL (dc.NewText, nwds.
    DataDescription) AS DATE)) AS VARCHAR) AS DATE) AS                     AvgPayMonth,
    ...
    FROM ...
    WHERE ...
    ORDER BY ...

    How does that sound?  I don't like it, seems like there should be a better solution, but it works!


    • Edited by mateoc15 Monday, July 29, 2013 12:44 PM Clarification
    Monday, July 29, 2013 12:43 PM
  • No, this is far too much code!  Try this:

    declare @startdate varchar(30)
    set @startdate = '6/12/13'
    
    select DATEADD(DD, -14, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate), 0))
    


    Ryan D

    Monday, July 29, 2013 1:10 PM
  • No, for other reasons that query above is the data source for the report, not what's in the RDL.  It's in an existing SP in SQL Server that I can't change.  Ignore that part.  Sorry if that wasn't clear.  Not my code, not my fault, not my problem :)

    Anyway, here's the horizontal axis range minimum:

    =DateAdd(DateInterval.Day, -21, DateAdd(DateInterval.Day, -1*((DAY(Parameters!StartDate.Value)-1)-1),Parameters!StartDate.Value))

    Monday, July 29, 2013 1:23 PM
  • Try changing this minimum to hard-coded value like 0 and see what happens.  Which mode is this in?  Scalar or Category?
    Monday, July 29, 2013 1:51 PM
  • Scalar.  When I change minimum to 0 the whole chart smashes to the right, like 0 = 1/1/1900 or maybe 0 Julian date or something.
    Monday, July 29, 2013 2:14 PM
  • is this category or scalar mode?  Try 2 instead.
    Monday, July 29, 2013 2:37 PM
  • Scalar mode.  Similar result using 2 as when using 0.
    Monday, July 29, 2013 3:44 PM
  • What happened when you entered my expression above?  Would you post your screenshot please of what this looks like?  And do you have a startdate report parameter now?  What are you entering for that?

    Ryan D

    Monday, July 29, 2013 4:22 PM
  • Start Date report parameter has always been there.  I mentioned that I cannot change the data set returned by the stored procedure.  Here's what 0 and 2 looked like, scalar mode (it has always been scalar mode).


    Monday, July 29, 2013 4:31 PM
  • I think this is your problem.  Use Category Mode instead.

    Ryan D

    Monday, July 29, 2013 4:41 PM
  • I think this is your problem.  Use Category Mode instead.

    Ryan D

    Why would I use category for a date field?
    Monday, July 29, 2013 4:42 PM
  • You need to use Category because of layout reasons.  Scalar Mode shows data according to actual distances; Category highlights data by removing the blank space between data points.  For example, this is the same chart from my RDL in the two different modes

    Scalar Mode:

    Category Mode:

    As you can see, Category Mode is asymmetrical so that it can summarize all of the data in one view.  Scalar Mode you can't make out the data because it tries to represent all of the data symmetrically


    Ryan D

    Tuesday, July 30, 2013 3:06 AM
  • My understanding was that scalar was for numerical types of data such as dates and numbers, which inherently have an order, but category was for things like if, for instance, you were summing sales of products by region (North America, South America, Europe, etc) which don't have any kind of numeric value of any kind, or really any way of sorting (other than alphabetically I guess).  Is that not correct?
    Tuesday, July 30, 2013 12:29 PM
  • That is not entirely correct, no.  Try it and post how it looks in Category mode please.

    Ryan D

    Tuesday, July 30, 2013 1:03 PM