Prev: DB2 on SS
Next: SSRS doesnt like Guid param datatypes
From: new DBA in '09 on 15 Jun 2010 16:28
Erland, just wanted to let you know that your first advice--to include
the joins in the pivot query from the start--worked terrifically. The
error of duplicated rows I had was due to a historic column value I
was pulling out of a customer activity table when I should have been
pulling static info from the customer table itself. Once I figured
this out, the results came back beautifully. It's still a dynamic SQL
query, which I still don't know how to avoid (considering the column
names will change weekly), but it works, I'm happy, and so's the boss.

What soap opera is playing out in this newsgroup with CELKO? His
advice has helped me before, but this time I can hardly consider his
response "advice." I've seen numerous threads where his advice is
shot down, refuted, or just picked to pieces.
From: --CELKO-- on 17 Jun 2010 12:40
The right way to do a report is to use a report writer. If you have a
medium to large company, then look at a report server. Two companies
with I have consulted had copies of Report Services as part of their
SQL Server contract, but never used it. The excuse was that nobody had
gotten training. Isn't there a “..for Dummies”, on-line training
courses or a contractor looking for a quick job?

If you have to stay in SQL, then set up a table of reporting periods.
The skeleton looks like this:

CREATE TABLE Report_Periods
(report_period_name VARCHAR (25) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date <= period_end_date),
report_period_type CHAR(5) NOT NULL
CHECK (report_period_type IN (..)),
etc);

The report_period_type tells you if this is fiscal, marketing, sales
or whatever.

Use a predicate like “my_date BETWEEN period_start_date AND
period_end_date” to put each row into the right report period. Then
use “GROUP BY report_period_name” with ROLLUP, CUBE and grouping sets
for the fancier stuff.

You can overlap report periods (the “Bikini Madness 2010” promo
occurred at the end of “2010-Q3” and the start of “2010-Q4”). You can
use a fiscal calendar with irregular units of measure. All kinds of
options with fully Standard, portabel code.

But the most important feature is that the report periods are
documented and share by EVERYONE in the enterprise.
From: new DBA in '09 on 17 Jun 2010 14:08
Thanks, CELKO, you've given me some homework to do. I've never used
ROLLUP or CUBE for grouping. I already have a Calendar table, so I
need to think longer about whether or not I also need a Report_Periods
table. My Calendar table has a row for each date, whereas
Report_Periods appears to get one row per reporting period. (So I
guess I just got my answer: I need a Report_Periods table.)

Your help is appreciated. Also, your advice on Reporting Services is
not lost on me. Exposing reporting services reports in a portal is
part of the end-goal.

-Eric
First  |  Prev  | 
Pages: 1 2 3
Prev: DB2 on SS
Next: SSRS doesnt like Guid param datatypes