Hi all,
I have a requirement to make a large number of histograms, and I have RS 10.1, no SPCC :'(
Where I'm at: I have all the data within a single data item in a query. I'm not sure how to count up these values into bins to generate the histograms...without typing by hand the range of every single bin and making an unwieldy large number of separate data items (!). There has got to be an easier way
Can one of the gurus please point me in the right direction?
Many thanks!
Todd
As an update, I was able to write some CASE logic to bucket my data items in bins, eg
CASE
WHEN [X] < 0 and [X] =<5 THEN (5)
WHEN [X] >5 and [X] =<10 THEN (10)
.
.
etc
ELSE NULL
END
I can then plot this data item on the horizontal axis against a count of data items in each bin
The problem is that I have holes in my histogram where there are no members of a bin: instead of having a horizonal axis with all possible bins in sequential order, the horizontal axis contains only the populated bins, so the histogram's x-axis looks like:
5, 10, 15, 30, 35, 40
instead of:
5, 10, 15, 20, 25, 30, 35, 40 (note that the 20 & 25 bins are missing above)
Any insights? Bring on the gurus!
Hmmm... The issue with your missing buckets is that you physically have no data for those value ranges. You could perhaps add a second query to your report which retrieves 8 rows from somewhere (doesn't matter what they are), and add a running-count calculation to number them sequentially. You could then multiply your running count by 5 in another calc to generate your bucket values, then join this query to your main query using 0..1 cardinality at the old query end and 1..1 cardinality at the new query end. Use the bucket value item from your resultant query in your chart and see if this gives you the desired result.
Cheers!
MF.
Thank you! This is very helpful and I will give it a shot with adding the "dummy" query
Kind regards. Todd
Hi there --
Can you please help me a little more? I'd like help with creating the "dummy" query for this. I am having trouble understanding how I can create a bunch of rows with the appropriate bin values in one data item
Quote from: Todd129 on 30 Jan 2013 08:19:43 PM
Hi there --
Can you please help me a little more? I'd like help with creating the "dummy" query for this. I am having trouble understanding how I can create a bunch of rows with the appropriate bin values in one data item
Sure.
I created a simple example based on the GO Sales (Query) sample package. My initial query (Query1) brings in the products and quantities, but the quantities are way too big, so I added a calc (Calc Qty) to divide them by 10,000 and dump the decimal places, giving me values from 1 upwards. I then added a calc called "Bucket" which uses a CASE statement like yours to derive bucket values from 5 up to 45. Next I added a second query (Query2) to retrieve the products again, with a calc to return the running count (1,2,3,4,5 etc). I filtered the query to return only rows with a count of 10 or less, meaning I had only 10 rows in the query. Then I added a calc called Bucket to multiply the running count by 5, giving me values of 5, 10, 15 etc all the way up to 50. So my first query has bucket values up to 45 and the second has bucket values up to 50. Still with me, or have I bored you to death yet? :) Next I added a third query (Query3) and dropped a join condition onto it. The source queries to be joined were selected as Query1 and Query2. I set the join to be 0..1 at the Query1 end and 1..1 at the Query2 end. I selected Query3 and dragged in my Bucket item from Query2 and my Calc Qty item from Query1, and I set the Auto Group and Summarize property of Query3 to "No". Finally, I added a List to my page, set it to be based on Query3, and dragged in Bucket and Calc Qty from the query. I grouped on Bucket (to make things easy to see) and the result is saved as a report spec and attached to this post.
If you run the report, you see that buckets from 5 to 50 are displayed, even though in the original query there are only buckets from 5 to 45. I think this is what you need?
Cheers
MF.
Perfect! Thanks--this is something I can really work with. I'll give it a shot.
Many thanks for taking time from your schedule to help me.
All the best, Todd