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

  • Jessica M. Moss

    SSC Eights!

    Points: 976

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

    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

    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

    SSCommitted

    Points: 1917

    Hi,

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

    Sincerely,
    Daniel

  • JediSQL

    SSCommitted

    Points: 1917

    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

    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

    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

    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: 235

    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

    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

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

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