I’ve been learning SQL Server and Crystal Reports, and I thought that this board would have some good resources for questions in general. I know we have some DBAs out there.
Right now I don’t have any specific questions, but thought I would share some of the learning resources that I’ve been given or found.
Find out when your local PASS chapter meets. Sign up for the virtual chapters, pop into the monthly webinars.
There’s a SQL Saturday in Silicon Valley in March. Free (except for lunch). If you go, let me know and I’ll give you some tips on whose sessions to sit in on.
Look up “Accidental DBA” for lots of good beginner resources.
I’ve used SQL Report Server a bit before, but the company I’m with now uses Crystal Reports. I don’t mind learning it, though, since that means I’ll just have more skills.
Nabiki, if you can, try to push Report Server. The main selling point is that Crystal Reports is a resource HOG and will chew through network bandwidth like nobody’s business. I had one client where they had to stop everything in the company (they had over 30 locations across the country) if they had to run a report outside my carefully crafted low usage times. They had one VP who thought she was most important than everyone else and was always shutting her location down to run some report she thought she needed. After I was called in to consult and pointed this out, and charged them the appropriate Stupid Tax, my low usage “guidelines” (VPs words) were then engraved in stone and placed on her desk. Report Server is kinder to network resources and leaves a much smaller footprint if you’re running reports during peak usage times.
SSRS is also a lot easier to monitor on the back-end because it’s so tightly integrated into SQL Server. Tools like Idera & SQL Sentry can watch for scheduled runs that don’t happen, long-running jobs, etc. right in the same interface as the rest of your SQL Server environment monitoring.
I was thinking of suggesting the phased approach. I just ran my first Crystal report, and it took five minutes to generate a four page report. One month of data for one customer. That’s pretty awful performance, all right.
I’m learning new stuff every day! Today, I have an interesting problem that I’m not sure how to solve.
My output looks like this:
The screened numbers should be
36 for 1/2
47 for 1/3
The confirmed numbers should be
2 for 1/2
5 for 1/3
There shouldn’t be any numbers in the screened column if they were confirmed, and I think that’s where the problem is coming in. I think it has to do with the grouping at the end. How do I group the screens and confirms separately from the names, but all on the date?
GROUP BY CONVERT (VARCHAR(10),Z.Date, 120), X.Name
A continuation of the question above, is there a way to record ‘Screened’ and ‘Confirmed’ on one line of date only, but still have the names grouped by date?
Got the data right. Now to try to figure out how to do part of the data in a vertical pivot and part of it horizontal. I’ve kind of gotten it in Crystal by overlapping two pivot tables.
So, the table would look kind of like one of these, with the top being the ideal.
Under the Name column, are “Pos” and “Neg” a count of each type of response that you can get, or just a yes/no?
If the former…I’m not sure there’s a non-hideous way of doing that with SQL, but I could easily be proven wrong. I’m thinking some kind of Frankenstein join with a PIVOT involved. But you may be better off doing that part in the reporting tool, not SQL itself.
Unfortunately, the Pos & Neg have to be quantity pos or neg.
Is it normal for a query that takes 11 seconds to run in SQL server to take several minutes in Crystal? I hope I’m not impacting performance right now…
Arrrgggggghhh! Why can’t I just do a pivot table in Crystal reports?! The crosstab does me no good because it wants to modify my data somehow (sum, avg, etc) I just want it to show the data in the pivot table format.
Can TRIM be used to remove specific text rather than just spaces? I haven’t been able to find anything that would give me the syntax for that, though it’s hinted that it’s possible. In addition, can you specify multiple strings to remove at the beginning of a field, such as LTRIM X or Y from (field)?
