June 16, 2008 at 7:24 am
Hi All
I am using Dundas Charts to produce a fairly standard line graph, however, my problem is that I have a series grouped by month which have null values for the first three months.
EG:
Month Value
JAN NULL
FEB NULL
MAR NULL
APR 15
MAY 12
JUN 9
JUL 18
AUG 20
SEP 21
OCT 11
NOV 7
DEC 5
The user would like to see all the months shown on the graph, but to have the data only start where there is a value (i.e. in April). So there should be no line shown for JAN, FEB, MAR and there should be no line shown between MAR & APR (i.e. what would normally be a steep line going from 0 to 15).
I have tried to play around with EmptyPoint values in code but nothing seems to work.
Any help would be appreciated.
Thanks,
Nigel West
UK
June 17, 2008 at 9:10 am
Does this starting month vary based on any parameters selected for the current report? If not and there is some relative date logic associated with the relevant months (e.g. rolling 6 months starting with last month), you might simply manage this in your WHERE clause. You could use a nested SELECT or stored procedure to resolve the issue if it is a "data-driven" range (e.g. start with first month for which there is data).
June 17, 2008 at 1:25 pm
Hi
Thanks for the response but I'm not sure I understand the answer, I have a dataset already that returns Jan, Feb, Mar, Apr, May etc and there is no value associated with Jan, Feb, Mar so I think this is what you are suggesting.
The problem is that there is a line on the chart starting at Mar (0) and moving straight up to 15 for Apr. What I want to see is simply a line starting at Apr, and starting at 15 (i.e. it doesn't move from zero to 15 at all).
Thanks in advance for any help.
Nigel West
UK
June 17, 2008 at 1:48 pm
You can't modify the dataset to only return the values you want to plot on the chart?
June 17, 2008 at 1:53 pm
I could certainly modify the dataset, the problem with that is that the end user still wants to see Jan, Feb, Mar on the chart, even though no value existed. This is an investment portfolio report and the investment was launched in Jan, the only values started appearing in April but they want to see a chart from the start of the investment.
They are a pain in the A$%E but what can you do?
Nigel West
UK
June 17, 2008 at 2:48 pm
I see, so you want to see Jan, Feb and Mar, but without a line running across the bottom representing no data. Is that it?
June 17, 2008 at 2:59 pm
That's it, but the really important part (I think I worked out how to not have the line across the bottom), is that the line should not start at zero on March and rise to 15 for April, it should simply start at April.
Nigel West
UK
June 25, 2008 at 7:53 am
if you use the Charting tool within BIDS and select a Simple Line Version The Line will start at April
June 26, 2008 at 1:58 am
OK, but the standard charting tool in BIDS does not give me the other functionality I need, which is why we use the awful piece of software called Dundas. It's incredibly flexible, but almost impossible to use because there's apparently no good technical reference manual for it.
Anyway, charting tool in BIDS is not an option for me.
Thanks for advice anyway.
Nigel West
UK
October 8, 2008 at 2:40 pm
Below is my response to a similar question on another forum.
.
.
.
.
.
.
I recently had a similar request. To solve this I had to make a control table. The control table had a record for every month for each data element I was interested in capturing. In your case, you'd have a record for every Type for every month. The value for each record is zero. After creating the control table, I changed my query by adding a UNION clause. Basically, I had my initial query (which returned only those data elements where there was activity) and the UNION returned all the data that was missing.
In your case, the Control table would look like this...
Date Type Qty
1/1/08 A 0
1/1/08 B 0
1/1/08 C 0
2/1/08 A 0
2/1/08 B 0
2/1/08 C 0
3/1/08 A 0
3/1/08 B 0
3/1/08 C 0
Then your query would change from this (which only returns data where data exists)....
SELECT Type, Date, sum(Qty)
FROM myTypeTable
GROUP BY Type, Date
To this....
SELECT Type, Date, sum(Qty)
FROM myTypeTable
GROUP BY Type, Date
UNION
SELECT Type, Date, sum(Qty)
FROM myControlTable
GROUP BY Type, Date
October 9, 2008 at 6:33 am
Download a trial version of Dundas. You should be able to find some samples.
This sounds like you'd want to do something like Jan - Mar as series 1 line, then Apr to Dec as series 2. And then then make Series one the same color as the line.
You might also try asking Dundas directly about a technical manual. We used to use the full version and they were always very helpful.
Greg E
October 9, 2008 at 9:02 am
In Dundas you can set the EmptyPointStyle.BorderWidth = 0 and EmptyPoint.Style.Color = . I believe when you insert NULL into a point that Point is considered "empty", so this should make your Null points invisible.
October 9, 2008 at 11:04 am
Our end users require data to be grouped by calendar week resulting in a similar predickerment. Our implemented solution is nearly the same as that provided by michael_darrow's response above. We build the base grid as a CTE (SQL 2005) and then left join the actual data to it for presentation.
October 9, 2008 at 11:09 am
Sorry, I used brackets in my response that got stripped.
I was trying to say to set the EmptyPoint.Style.Color for the axis in question = your background color. This is what makes that point invisible.
January 30, 2011 at 6:12 pm
Can someone please give me a link to a site where i can download the trial version of the dundas chart for reporting services
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply