Search code examples
sql-serverssrs-2012ssrs-tablix

Page Break on export excel- ssrs

Is it possible to export the output excel by group wise with page name as group and subgroup wise with page name as subgroup.

Tried with few options and doesn't work well with page break and sheet name.

Page break -->Group--> Between
Page break -->SubGroup --> Between and End.

Expected output with the below dataset: 5 sheets in excel.

  1. US - SubGroup name -Sheet1
  2. CA - SubGroup name -Sheet2
  3. North America - group name with Summation detail -sheet3
  4. FR - SubGroup name -sheet4
  5. Europe -group name with Summation detail - sheet5

Sample Dataset:

Select 'Area-1' as Region, 'North America' as 'Group','US' as 'SubGroup','Northwest' as 'Detail',7887186.7882  'Sales'
union
Select 'Area-1', 'North America','US','Northeast',2402176.8476  
union
Select 'Area-1', 'North America','US','Central',3072175.118  
union
Select 'Area-1', 'North America','CA','Southwest',10510853.8739  
union
Select 'Area-1', 'North America','CA','Southeast',2538667.2515  
union
Select 'Area-1', 'North America','CA','Canada',6771829.1376  
union
Select 'Area-1', 'Europe','FR','Northwest',4772398.3078  
union
Select 'Area-1', 'Europe','FR','Northeast',3805202.3478  
union
Select 'Area-1', 'Europe','FR','Central',5012905.3656

enter image description here

enter image description here

Expected Output: enter image description here

Solution

  • I would try to get the data grouped differently so that the report side of things is easier. I took your original dataset, dumped it into a temp table and grouped it like this..

    SELECT * INTO #t FROM (
Select 'Area-1' as Region, 'North America' as [Group],'US' as [SubGroup],'Northwest' as [Detail], 7887186.7882 as [Sales]
union
Select 'Area-1', 'North America','US','Northeast',2402176.8476  
union
Select 'Area-1', 'North America','US','Central',3072175.118  
union
Select 'Area-1', 'North America','CA','Southwest',10510853.8739  
union
Select 'Area-1', 'North America','CA','Southeast',2538667.2515  
union
Select 'Area-1', 'North America','CA','Canada',6771829.1376  
union
Select 'Area-1', 'Europe','FR','Northwest',4772398.3078  
union
Select 'Area-1', 'Europe','FR','Northeast',3805202.3478  
union
Select 'Area-1', 'Europe','FR','Central',5012905.3656
) q 

SELECT 
    [Region], [Group], [SubGroup], [Detail]
    , SUM(Sales) OVER(PARTITION BY [Group]) as ContinentSales
    , SUM(Sales) OVER(PARTITION BY [Group], [SubGroup]) as CountrySales
    , Sales as DetailSales
into #s
 FROM #t

SELECT DISTINCT Region, SubGroup as ReportGroup, Detail, DetailSales as Sales FROM #s
UNION ALL
SELECT DISTINCT Region, [Group], NULL, ContinentSales FROM #s

    This gives the following output

    enter image description here

    You should then be able to group by the report group and use this as the pagename in the group property too.