Designing a Dashboard - Level 6 in the Stairway to Reporting Services

Question

Designing a Dashboard - Level 6 in the Stairway to Reporting Services

Jessica M. Moss

SSC Eights!

Points: 986
More actions
August 3, 2011 at 10:55 pm

#390120

Comments posted to this topic are about the item Designing a Dashboard - Level 6 in the Stairway to Reporting Services

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Great information and nice tutorial.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

thepotplants Hall of Fame Points: 3079 More actions · Answer 2

Hi.

I've just done the Microsoft SSRS training course, and now that I'm back an work I'm trying to put a few things into practice.

I've downloaded the Adventureworks2008R2 and AdventureWorkDW2008R2 databases and have been trying to work through your example. (It looks great BTW.)

However, I haven't got very far... Your SQL in Figure 3, refers to a table that doesn't exist in the AdventureworksDW2008R2 database: dbo.DimTime. Exercises in SSRS training course Mod 4 refer to it, but none of the supplied scripts create it.

Is this just a version difference? between 2008 and 2008R2 sample db's. Is there a script I need to run?

Any help or suggestions would be greatly appreciated.

Cheers

Pete

steve roberts SSC Enthusiast Points: 185 More actions · Answer 3

Hi

I had the same issue and replaced the line in the script

inner join dbo.DimTime t on f.DueDateKey = t.TimeKey

with

INNER JOIN dbo.DimDate AS t ON f.DueDateKey = t.DateKey

hope this helps

JediSQL SSCrazy Points: 2118 More actions · Answer 4

Hi,

I got the same thing, but then I read carefully. She says to use "AdventureWorksDW" not "AdventureWorksDW2008R2".

Sincerely,
Daniel

JediSQL SSCrazy Points: 2118 More actions · Answer 5

So far so good, but now I have a problem with the Dashboard. I do not get the expandable groups. They are all expanded without the +/– control to expand/collapse the groups. It is just a static report with grid lines.

Sincerely,
Daniel

ccdeal26 SSC Enthusiast Points: 108 More actions · Answer 6

Wonderful.

Share a website with you ,

( http://www.ccdeal.net/ )

Believe you will love it.

We accept any form of payment.

ccdeal26 SSC Enthusiast Points: 108 More actions · Answer 7

Wonderful.

Share a website with you ,

( http://www.ccdeal.net/ )

Believe you will love it.

We accept any form of payment.

JAMESC2003 SSC Enthusiast Points: 138 More actions · Answer 8

There is a program (adventureworks2008_sr4.exe) that creates the

DW database. I do not remember where I found it, but perhaps

an internet search will help.

John_P SSC Veteran Points: 240 More actions · Answer 9

I Love the SIGN function...

CASE WHEN fsq.SalesAmountQuota > frs.SalesAmount

THEN 1 ELSE CASE WHEN fsq.SalesAmountQuota = frs.SalesAmount

THEN 0 ELSE -1 END

END as Trend

Can be replaced with:

SIGN(fsq.SalesAmountQuota - frs.SalesAmount) as Trend

IMNSOH, I think it is simpler 🙂

adholkawala Grasshopper Points: 17 More actions · Answer 10

You have to change the nest after the time key change too so it should work like this:

SELECT e.FirstName + ' ' + e.LastName AS FullName, fsq.CalendarYear, fsq.CalendarQuarter, fsq.SalesAmountQuota, frs.SalesAmount,

CASE WHEN fsq.SalesAmountQuota > frs.SalesAmount THEN 1 ELSE CASE WHEN fsq.SalesAmountQuota = frs.SalesAmount THEN 0 ELSE - 1 END END AS Trend

FROM dbo.FactSalesQuota AS fsq INNER JOIN

DimEmployee AS e ON fsq.EmployeeKey = e.EmployeeKey LEFT OUTER JOIN

(SELECT f.EmployeeKey, t.CalendarYear, t.CalendarQuarter, SUM(f.SalesAmount) AS SalesAmount

FROM FactResellerSales AS f INNER JOIN

DimDate AS t ON f.DueDateKey = t.DateKey

GROUP BY f.EmployeeKey, t.CalendarYear, t.CalendarQuarter) AS frs ON fsq.EmployeeKey = frs.EmployeeKey