chart y-axis intervals not using formula?

i put the formula day(Parameters!Parameter1.Value) into my y-axis's major gridlines interval value, and it doesn't obey the value i put in.

is this a bug or another "feature"?

when i just type in "31" is works as expected.

is there a work around for this?

I dont think it will accept an Expression. unless you have symbol like fx beside it,you cant enter any expression

neither it is bug nor feature.

Only Constants are accepted here.

others: please correct me if i am not correct

Thanks

|||for Y-axis , no expresions are available. Hence you can't have dynamic Y-axis|||

no, not all fields with out the expression symbol won't accept expressions. the maximum and minimum fields will accept expressions. i guess that's what lead me to expect major gridlines option to accept it.

seems really stupid to not allow it.

as a side note, the dundas control which DOES show the expression field beside major gridlines also doesn't accept any formula, so it must be some internal failing on the part of RS

|||

Hi Tim

The Major Gridlines Interval will accept expressions. I have spent some time working with this to get my charts to display the Y axis scale in consistent way. The key is to derive the maximum value that your chart is going to display dynamically and divide it by the number of gridlines you want displayed.

For example the expression =Ceiling(Max(Fields!Data.Value))/5 will give you five gridlines whose spacing is dependant on the the data selection chosen when the report is run.

In practice I have found that the interaction between Minimun, Maximum and Major Gridlines interval is complex and setting one can have an adverse effect on the other.

As a result I just set one value out of the three depending on what I am trying to achieve.

You can also use expressions containing code if you want to write you own scaling function, which I have found gives the best results.

Hope this helps

chart y-axis intervals not using formula?

i put the formula day(Parameters!Parameter1.Value) into my y-axis's major gridlines interval value, and it doesn't obey the value i put in.

is this a bug or another "feature"?

when i just type in "31" is works as expected.

is there a work around for this?

I dont think it will accept an Expression. unless you have symbol like fx beside it,you cant enter any expression

neither it is bug nor feature.

Only Constants are accepted here.

others: please correct me if i am not correct

Thanks

|||for Y-axis , no expresions are available. Hence you can't have dynamic Y-axis|||

no, not all fields with out the expression symbol won't accept expressions. the maximum and minimum fields will accept expressions. i guess that's what lead me to expect major gridlines option to accept it.

seems really stupid to not allow it.

as a side note, the dundas control which DOES show the expression field beside major gridlines also doesn't accept any formula, so it must be some internal failing on the part of RS

|||

Hi Tim

The Major Gridlines Interval will accept expressions. I have spent some time working with this to get my charts to display the Y axis scale in consistent way. The key is to derive the maximum value that your chart is going to display dynamically and divide it by the number of gridlines you want displayed.

For example the expression =Ceiling(Max(Fields!Data.Value))/5 will give you five gridlines whose spacing is dependant on the the data selection chosen when the report is run.

In practice I have found that the interaction between Minimun, Maximum and Major Gridlines interval is complex and setting one can have an adverse effect on the other.

As a result I just set one value out of the three depending on what I am trying to achieve.

You can also use expressions containing code if you want to write you own scaling function, which I have found gives the best results.

Hope this helps

Chart Gridline Question

Is there a way to make Y axis gridlines intervals change dynamically?
Basically increase/decrease base on numbers being graph.Not in rs2k don't know about rs2k5. I got round this by overlaying graphs
and hiding the ones i didn't want to see depending on how much data i got
out.
Greg
"Johnny C" <JohnnyC@.discussions.microsoft.com> wrote in message
news:DD600FE2-F167-4B8E-8126-693E57B31D0B@.microsoft.com...
> Is there a way to make Y axis gridlines intervals change dynamically?
> Basically increase/decrease base on numbers being graph.

changing timeframes with stock data

I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).

Here's is some sample data and my final goal:

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3

[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *

*sum

Assuming your intervals will never span midnight,
you can do this by joining to an intervals table. This
has all minutes in one day with any intervals you want
and can be set up like this

CREATE TABLE MinuteIntervals(Mins INT NOT NULL PRIMARY KEY,
HMS CHAR(8) NOT NULL,
Interval10Mins INT NOT NULL,
Interval1Hour INT NOT NULL)

DECLARE @.i INT
SET @.i=0
WHILE @.i < 24*60
BEGIN
INSERT INTO MinuteIntervals(Mins,HMS,Interval10Mins,Interval1Hour)
SELECT @.i, CONVERT(CHAR(8),DATEADD(minute,@.i,'19000101'),108),
@.i/10, @.i/60

SET @.i=@.i+1
END

Now you need to join to this to your stock table
grouping by whatever interval you want.

SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
INNER JOIN MinuteIntervals m ON m.HMS=CONVERT(CHAR(8),t.[DateTime],108)
GROUP BY m.Interval10Mins

|||

Correction, the group by should read

GROUP BY m.Interval10Mins,CONVERT(CHAR(8),t.[DateTime],112)

|||

Of course this will only work for intervals less that one day.
Should have read the question fully!

For daily or longer intervals, don't join
to the MinuteIntervals table, simply group by the relevant amount
e.g. for daily

SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Close]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
GROUP BY CONVERT(CHAR(8),t.[DateTime],112)

For monthly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),6)

For yearly

GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),4)

|||

I very much appreciate your help. If I understand the roles of tables t and t2, it appears the code only works if two different tables are used.

Does that mean I have to first create a copy of the original table in order to run the script correctly?

|||Those are not tables they are just aliases for tables. Since you are joining with the same table you need to use table aliases to make the names unique for referencing columns in the query. See the SELECT statement topic in Books Online for the syntax details.|||

Yes, you are so right . That's what I get for trying to cut back on my caffeine.

It works like a charm . Thanks to all who have helped. I really do appreciate it.