Transitioning from Crystal 11 to SQL Reporting

[dianemarie]

Hello, we have been told we will be switching our reporting from Crystal (BO) to SQL Reporting. I am comfortable in Crystal 11 and Access reporting, and have just started working with SQL Reporting. I'm feeling somewhat uncomfortable and wondered if anyone could suggest a good book to get me jump-started. I'm not a whiz in SQL but I think I know it well enough to get started on some reports. Thank you for any suggestions.

 

[DietSquirt]

600? And only two of you? Yikes...
(I'm sure you intend to do this anyway, but )I would look for similarities within those reports and group those, with the idea of potentially trimming the 600 down to fewer reports with more parameters, or a handful of highly summarized reports with either drill down or drill through capability.

For example I made a report that lists all the sales by customer for a product manager. The user selects the months they want from a parameter list, and their product manager name from a parm list. The detail is hidden and only the totals by customer are initially visible. They can click on a + sign next to the customer name to see a list of all the products that customer purchased. That's drill down.

When they're drilled down into that detail, they can click on one order number to link to a report that shows all the order (linked by a parm in the 2nd report to the order number in the 1st one) and invoicing information for that one customer order: shipping information, sales rep, item, price, cost, etc. That's drill through. I'll use that second report a lot as it will be accessable from other reports too.

I started by building the two most important reports we need, than deleted them and started over based on what I was learning about how SSRS worked.
Sorry to go on so long...I'm probably about the same place on the learning curve and all this is fresh in my mind.

 

[xlbo]

agree with above. We have created a LOT of reports in RS since we went live with it a year or 2 ago. If you know your data well and you can bundle a lot of your calculations into SQL / Stored procs it really doesn't take that long to create a report

Would suggest looking at creating report templates as well so that you don't need to faff around with setting fonts / colours etc (or at least only do so by exception)

You can also create custom assemblies that can handle any "standard" calculations - these can then be ported over to any report that needs them

Remember that you can embed reports into other reports so that you can create a set of standard report items (individual tables or charts such as TY vs LY or last 12 weeks or whatever standards your company uses) as individual reports and then drag them into other reports so that the more complex reports are simply aggregations of pre-defined report elements held as sub reports

Multiple data sets in a single report is also very sueful (although it would be nice if you could link them). As I said at the start of this thread, most of the really tricky stuff in SSRS is writing the SQL. If you can get that sorted, the actual report creation is (IMHO) pretty straightforward

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DietSquirt]

xlbo: My post was not intended to be a spleen vent, and I'm sorry if I offended you. I truly like SSRS but the one thing that we absolutely have to have is nested subtotals and SSRS doesn't seem to do that. I have searched the web and found many people before me are experiencing the same problem. I was especially enlightened by a post on MSDN where a Microsoft representative stated (to a very frustrated person not me)

"Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266724&SiteID=1

We are developing a data warehouse using SQL2005 tools, but we absolutely have to have nested groups with subtotals, something that Crystal and Access do so easily I think many of us took it for granted. I've spent precious time testing workaround solutions for this problem suggested by others including our consultants. I don't understand why Microsoft would release a product that could create multiple nested groups without being able to create the subtotals for those groups, so maybe the answer is still out there somewhere. If it's not, or it's too cumbersome, we will decide to use another tool instead. We're too small a shop to invest in complex workarounds. That's not spleen, it's just a business decision and my post was intended to share that. I'm close to thinking SSRS is the neatest thing since sliced bread except for that issue and would be sorry to see us have to switch.
I've benefited a lot from your knowledge on this thread and others I have read, and hope we can shake hands and be friends.

 

[xlbo]

DietSquirt - please don't take my posts as me being personally offended by anyone or anything. All I am trying to do is to keep this forum dedicated to professional discussions regarding SSRS. I saw 3 posts in a row that were very negative about SSRS so I decided to step in and say something before the thread degenerated into a "I hate SSRS. Crystal was soooo much better" set of posts. I am very happy that the thread has been turned around and we are now discussing differences between the 2 products rather than listing all the things that SSRS doesn't do.

ps - if you think you've got it bad, we are still using SSRS 2000 !

In terms of your issue regarding nested subtotals, I'm not sure I understand fully but if you are describing what I think you are then it can be achieved - both in SQL and in SSRS. It's just a case of setting things up in the right order. Please feel free to either start a thread or point me to one you have started with the pertinent details and I will have a look

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question