SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Stepped report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rdprecure
Starting Member

7 Posts

Posted - 10/24/2013 :  11:15:58  Show Profile  Reply with Quote
Hello,

This is my first attempt to build a SQL report. I tried to use the wizard to create a stepped report. The results were close to what I needed, but I need to format the report so the data is spread out among the different steps rather than all in a single line (see image). I've also included a script for the SQL tables.

Any help would be really great.

Thanks...Doug



IF OBJECT_ID('#Continents') IS NOT NULL
    DROP TABLE #Continents

CREATE TABLE #Continents (
	[Continent] [varchar](50) NULL,
	[Area] [bigint] NULL,
	[CountryCount] [int] NULL,
	[LargestCountry] [varchar](50) NULL,
	[SmallestCountry] [varchar](50) NULL)

insert into #Continents values ('Africa',30065000,54,'Algeria','The Seychelles')
insert into #Continents values ('Asia',44579000,44,'Russia','Maldives')
insert into #Continents values ('Europe',9938000,47,'Ukraine','Vatican City')
insert into #Continents values ('North America',24256000,23,'Canada','Saint Kitts')
insert into #Continents values ('Oceana',8112000,14,'Australia','Pitcairn Islands')
insert into #Continents values ('South America',17819000,12,'Brazil','Suriname')

IF OBJECT_ID('#Countries') IS NOT NULL
    DROP TABLE #Countries

CREATE TABLE #Countries (
	[Country] [varchar](50) NULL,
	[Continent] [varchar](50) NULL,
	[Area] [bigint] NULL,
	[Population] [bigint] NULL,
	[Capital] [varchar](50) NULL)

insert into #Countries values ('Japan','Asia',145920,127600000,'Tokyo')
insert into #Countries values ('USA','North America',3717813,313900000,'Washington')
insert into #Countries values ('Brazil','South America',3287612,198700000,'Brasilia')
insert into #Countries values ('Mexico','South America',758449,112300000,'Mexico City')
insert into #Countries values ('Russia','Asia',6601668,143500000,'Moscow')
insert into #Countries values ('France','Europe',247368,65700000,'Paris')
insert into #Countries values ('Canada','North America',3855000,34880000,'Ottawa')

IF OBJECT_ID('#Cities') IS NOT NULL
    DROP TABLE #Cities

CREATE TABLE #Cities (
	[City] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[Population] [bigint] NULL,
	[Airport] [varchar](50) NULL)

insert into #Cities values ('Tokyo','Japan',37126000,'Narita')
insert into #Cities values ('New York','USA',20464000,'JFK')
insert into #Cities values ('Sao Paulo','Brazil',20186000,'Guarulhos')
insert into #Cities values ('Mexico City','Mexico',19463000,'Juarez')
insert into #Cities values ('Moscow','Russia',15512000,'Sheremetyevo')
insert into #Cities values ('Los Angeles','USA',14900000,'LAX')
insert into #Cities values ('Monterrey','Mexico',4180000,'Escobedo')
insert into #Cities values ('Houston','USA',5383000,'Bush')
insert into #Cities values ('Nagoya','Japan',10027000,'Chubu')
insert into #Cities values ('Salvadore','Brazil',4032000,'Comalapa')
insert into #Cities values ('Saint Petersburg','Russia',4879000,'Pulkova')



Edited by - rdprecure on 10/24/2013 11:23:26

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 10/24/2013 :  12:24:54  Show Profile  Reply with Quote
How are you applying the current grouping?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rdprecure
Starting Member

7 Posts

Posted - 10/24/2013 :  17:04:40  Show Profile  Reply with Quote
If I understand what you're asking me for...


select a.continent,a.area,a.countrycount,a.largestcountry,a.smallestcountry,
b.country,b.area,b.population,b.capital,
c.city,c.population,c.airport
from cities c
right join countries b on c.country=b.country
right join continents a on b.continent=a.continent
order by a.continent,b.country,c.city

Edited by - rdprecure on 10/24/2013 17:33:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 10/25/2013 :  03:31:30  Show Profile  Reply with Quote
Nope I meant grouping applied in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rdprecure
Starting Member

7 Posts

Posted - 10/25/2013 :  06:08:36  Show Profile  Reply with Quote
Three groups. One each for continents, countries and cities. There is one detail line immediately following the city group.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 10/25/2013 :  08:46:00  Show Profile  Reply with Quote
why putting them in separate rows. why cant you put them flattened in single row? in that case they'll all come on same line

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rdprecure
Starting Member

7 Posts

Posted - 10/25/2013 :  12:25:37  Show Profile  Reply with Quote
Because my boss wants it that way.

I found some information on nesting. I'll give that a shot and see how it looks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000