Line Chart X axis dependent on parameter?

Question

Line Chart X axis dependent on parameter?

dj1202

SSCommitted

Points: 1829
More actions
July 10, 2012 at 1:15 pm

#263672

Hello everyone,
I am working on a report where the X axis is supposed to be based on what the user selects in a parameter. Specifically, this is a parameter where user should be able to select 'Daily', 'Weekly', 'Monthly', or 'Yearly' groupings of a date range. I have yet to create the parameter and need some assistance in figuring out how to do this. Can anyone offer any suggestions on a starting point?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Sounds like your category group value will be the the result of a calculation.

e.g. IIF (Parameters!Criteria.Value="Daily", Fields!DailyField.Value, (IIF (Parameters!Criteria.Value="Weekly", Fields!WeeklyField.Value, Fields!MonthlyField.Value))

As an alternative, you could include similar logic in the SQL code for that gets the data so that the category group in the report is simply a column from the dataset you are using.

dj1202 SSCommitted Points: 1829 More actions · Answer 2

happycat59 (7/10/2012)
Sounds like your category group value will be the the result of a calculation.
e.g. IIF (Parameters!Criteria.Value="Daily", Fields!DailyField.Value, (IIF (Parameters!Criteria.Value="Weekly", Fields!WeeklyField.Value, Fields!MonthlyField.Value))
As an alternative, you could include similar logic in the SQL code for that gets the data so that the category group in the report is simply a column from the dataset you are using.

Thank you happycat. I seem to be missing a lot here. The range of the X axis is dependent on the date range selected by date parameters. Specifically, I have 3 parameters (Start Date, End Date, Date Sort Interval). The Date Sort Interval has the values Weekly, Monthly, and Yearly. I used "Specify Values" for this parameter and used 3 expressions for its values: =DateInterval.Year, =DateInterval.Month, and =DateInterval.WeekOfYear

Am I on the right path? How do I configure the x axis to use Date1 through Date2 as the date range and the third parameter as the date interval?

Many, many thanks in advance

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

What have you used for the category group expression

You will need something like

=iif(Parameters!DateSortOrder=DateInterval.Year, Datepart(DateInterval.Year, Fields!somedate), iif (Parameters!DateSortOrder=DateInterval.Month, Datepart(DateInterval.Month, Fields!somedate) etc

Or (a shorter form of the above

=Datepart (Parameters!DateSortOrder, Fields!somedate)

dj1202 SSCommitted Points: 1829 More actions · Answer 4

happycat59 (7/15/2012)
What have you used for the category group expression
You will need something like
=iif(Parameters!DateSortOrder=DateInterval.Year, Datepart(DateInterval.Year, Fields!somedate), iif (Parameters!DateSortOrder=DateInterval.Month, Datepart(DateInterval.Month, Fields!somedate) etc
Or (a shorter form of the above
=Datepart (Parameters!DateSortOrder, Fields!somedate)

Thank you happycat! The first example you offered (in both posts actually) is just what I needed.