That is a challenged when you facing the customer that required you to design the report with unknown number of columns that will tied with a category.

Here is the sample that will display the sales of each province that group under the country:



To design the report, you will make use of DENSE_RANK().

1. Copy the following code into the new data source (under BIDS or Report Builder)
   

   IF NOT object_id(‘tempdb..#Country’) IS NULL
   DROP TABLE #Country

   CREATE TABLE #Country
   (
   Code VARCHAR(4) NOT NULL,
   Descs VARCHAR(60) NOT NULL
   )

   INSERT INTO #Country (code, descs) VALUES (‘US’, ‘United States’);
   INSERT INTO #Country (code, descs) VALUES (‘MY’, ‘Malaysia’);
   INSERT INTO #Country (code, descs) VALUES (‘NZ’, ‘New Zealand’);

   IF NOT object_id(‘tempdb..#Province’) IS NULL
   DROP TABLE #Province

   CREATE TABLE #Province
   (
   Code VARCHAR(4) NOT NULL,
   CountryCode VARCHAR(4) NOT NULL,
   Descs VARCHAR(60) NOT NULL
   )

   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘AUK’, ‘NZ’, ‘Auckland’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘OTA’, ‘NZ’, ‘Otago’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘WTC’, ‘NZ’, ‘West Coast’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘CIT’, ‘NZ’, ‘Chatham Islands’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘BOP’, ‘NZ’, ‘Bay of Plenty’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘CA’, ‘US’, ‘California’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘HI’, ‘US’, ‘Hawaii’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘NY’, ‘US’, ‘New York’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘WA’, ‘US’, ‘Washington’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘SL’, ‘MY’, ‘Selangor’);
   INSERT INTO #Province (code, CountryCode, descs) VALUES (‘PG’, ‘MY’, ‘Pulau Pinang’);

   IF NOT object_id(‘tempdb..#Sales’) IS NULL
   DROP TABLE #Sales

   CREATE TABLE #Sales
   (
   CountryCode VARCHAR(4) NOT NULL,
   ProvinceCode VARCHAR(4) NOT NULL,
   Sales Decimal(12,2) NOT NULL
   )

   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘NZ’, ‘AUK’, 1100);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘NZ’, ‘OTA’, 1000);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘NZ’, ‘WTC’, 900);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘NZ’, ‘CIT’, 800);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘NZ’, ‘BOP’, 700);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘US’, ‘CA’, 600);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘US’, ‘HI’, 500);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘US’, ‘NY’, 400);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘US’, ‘WA’, 300);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘MY’, ‘SL’, 200);
   INSERT INTO #Sales (CountryCode, ProvinceCode, Sales) VALUES (‘MY’, ‘PG’, 100);

   SELECT DENSE_RANK() OVER
   ( PARTITION BY a.code
   ORDER BY b.code )
   AS rownumber,
   a.code AS CountryCode,
   a.descs AS CountryDescription,
   b.code AS ProvinceCode,
   b.descs AS ProvinceDescription,
   c.sales
   FROM #Country a, #Province b, #Sales c
   WHERE a.Code = c.CountryCode
   AND b.Code = c.ProvinceCode




2. Select the “countrycode” under row group
   

   




3. Select the “rownumber” under column group
   

   




4. Design the screen shown as following image
   

   
   




5. Please reduce height of the “rownumber” TextBox to “0.0001″ and unchecked the “Allow height to increase”.

Now you can preview your report. Hope you enjoy it.

Download the source file