SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Charting the Unknown – Step 5 in the Stairway to Reporting Services


Charting the Unknown – Step 5 in the Stairway to Reporting Services

Author
Message
Jessica M. Moss
Jessica M. Moss
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 360
Comments posted to this topic are about the item Charting the Unknown – Step 5 in the Stairway to Reporting Services
indranil.cal
indranil.cal
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 8
Jessica,

Thanks for the nice & lucid article!

We are facing a problem while developing a SSRS 2008 report. We have to generate a scatter chart with linear regression trendline (LRT) to imitate a business critical Excel report.

In Excel, the scatter chart with LRT can be created quickly with a click, but in SSRS 2008 the scatter chart does not seem have the LRT option.

Though I understand it was available in Dundas chart earlier (SSRS 2k5), since Dundas have provided a lot of features to SSRS 2k8, I'm not sure how we can get this chart created in SSRS 2008.

Any tips?
Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11875 Visits: 1407
Nice one .....



Stephen_W_Dodd
Stephen_W_Dodd
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1206 Visits: 553
O.K. Am I missing something? Where is Step 4?

(Oops. Found it!)
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7902 Visits: 2418
FYI, I found out (painfully) yesterday that the case for the embedded mapping values for the US state by county maps is proper case. So the state abbreviations are uppercase (which is common) but the county names are proper case (first letter being capitalized, rest lowercase). This threw a wrench into the works for me as our DW was loaded with upper case county names. This was because the source data was uppercase for all address fields and for reporting, no one really cared. The mapping of the spatial values to the analytical values by county did not return anything due to the case differences.

To fix this, I had two options:

1. Update the county name in each RDL file located in <drive letter>\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery\USA\States_by_County\<State Name>.rdl

2. Add a new county column to my dimension table that was loaded with a proper case function so I could have both versions of the county name stored and available.

I went with option 2. The bad thing is that this is an 18M row dimension so it took awhile to compute initially. I was also not fond of having to store another column. However, I was even less fond of the idea of manually changing thousands of county names in RDL files.

I haven't checked yet but I would assume that country names are stored as case sensitive too. I imagine ESRI files are typically built this way too.

If anyone out there has an easier fix for this problem, I would love to know it. From what I can tell, there is no way to change the case sensitivity in the mapping portion of SSRS.
Jessica M. Moss
Jessica M. Moss
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 360
Hi indranil.cal,

I'm not aware of any way to create an LRT directly in Reporting Services. Your best bet is to create that information in your dataset.

Wish I could help further!
Jessica
Dechrau
Dechrau
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 77
Figure 2 shows every other territory name. How can we display every name?
indranil.cal
indranil.cal
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 8
Hi -

Thanks for your response!

We thought so too, so we looked up some regression formula esp. at http://www.easycalculation.com/statistics/learn-regression.php.

Then we extrapolated which column values are to be generated to get the slope and intercept of the straight line, and an SP was written. Implemented it in SSRS using a straight line type, while rest data were implemented on scatter chart type.

The result looks good - see attachment.

BR, Indra
Attachments
SSRS-Report-scatter-w-LRT.JPG (96 views, 86.00 KB)
Jessica M. Moss
Jessica M. Moss
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 360
Hi Dechrau,

Good catch! To show every label, set the interval on the vertical axis to 1.

Jessica
melissa21_13
melissa21_13
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 208
Indra,
I am trying to do the same thing, would you be able to post your SP or some advice on how to do it? What shape is your dataset in? Any help would be greatly appreciated!

Thanks so much in advance,
Melissa
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search