SSRS Pivot with Dynamic Columns

Question

My Query is like below for Table...,

Create Table t_raw
(
Month nvarchar(255),
Name nvarchar(255),
Clear nvarchar(255),
Contact nvarchar(255),
Viewed nvarchar(255),
Clicks float
)

Insert Into t_raw values('Jan-18','ABC','DTC','dtc@mz.com','GM','2'),
                        ('Feb-18','QSC','FMM','fmm@mp.com','AM','6'),
                        ('Mar-18','ABC','DTC','lta@mz.com','OPS','9'),
                        ('Jan-18','MHY','GNMA','gnma@sr.com','REP','3'),
                        ('Feb-18','VRL','XLR','xlr@vn.com','TUC','5'),
                        ('Mar-18','MHY','GNMA','gnma@sr.com','XEM','7'),
                        ('Jan-18','ABC','DTC','mat@sd.com','SUD','2'),
                        ('Feb-18','MHY','GNMA','mio@fr.com','AFT','4'),
                        ('Mar-18','TOC','ADF','fin@yu.com','BPL','2')

Below is the table:

Input Table

I am expecting the below output like below.

Output Table

Can anybody help me achieve this desired output?

You need to put a lot more effort in here. explain your goal and your logic. Post your sample data as **text**, an image often isn't helpful. What have you tried so far?? Finally what is it you need help with? You need to be more specific. — Thom A, Jul 07 '18 at 10:55
Please put the data in your post, you'll find many will be unhappy to download a file from a stranger on the internet (including myself). Putting a link to a file isn't what I asked (which was to supply it as text). Even better, provide `CREATE` and `INSERT` statements. — Thom A, Jul 07 '18 at 15:29

Wolfgang Kais · Accepted Answer · 2018-07-16T22:04:45.893

0

Create a blank report and create a Data Source and a Dataset to connect to the data. Add a Matrix to the report. Drag the Name fiield from the Dataset in the "Report Data" window to the first "Rows" field of the matrix and select the Month field using the field selector in the Columns header field. Select the Clicks field in the data cell under the Month header, this should automatically result in a Sum. In the properties window of the Month Column Group change the sort order to an expression like

=CDate(Fields!Month.Value)

Change the width of the Name field to be wide enough to contain the four columns Name, Clear, Contact and Viewed (I chose 12 cm).

Now, right-click the Name header cell and insert a table via context menu. Don't worry about the hights and add an additional column in that table.
Do the same in the Name data cell

The design should now look like this:

In the table next to the Month field (I'll refer to that as "header table"), remove the Data row (and associated groups, when asked):

Then in the table next to the Sum(Clicks) field (I'll refer to that as "data table"), remove in turn the header row:

These steps resolve the problem with the hights.

In the "header table", enter the column names and format them as desired. In the "data table", select the corresponding fields to display. In the properties of the Row Group "Details1" of the "data table". add all 4 fields in the correct order for grouping and sorting.

For the fist 3 fields in the "data table", use the Properties sheet to set the HideDuplicates property to the name of your Dataset.

For the Sum(Clicks) TextBox, set TextAlign to "Center" and VerticalAlign to "Middle".

Oh, and add a total for the Name group of the outer matrix, align everything as desired.

Voilà:

Added:

To merge the cells for Name, Clear and Contact do the following:

In the properties of row group "Details1" of the "data table", remove Name, Clear and Contact under Grouping and under Sorting properties, so only Viewed is left. Rename the group to Viewed.
Remove the first three columns of the "data table" (Name, Clear and Contact).
Right-clicking always the topmost row group of the "data table", successivly add parent groups for Contact, Clear and Name.

edited Jul 16 '18 at 22:04

answered Jul 09 '18 at 21:43

Wolfgang Kais

4,010
2
10
17

Thanks for your response but I am confused from “Header table and Data table” ... what to delete and when to delete. If possible could you please provide me the screen print. That helps me lot. – Pandu Jul 11 '18 at 13:43
@Pandu Of course. Just added two screenshots and hope it is clearer now. – Wolfgang Kais Jul 11 '18 at 13:57
That works fine and thank you. Finally I have one doubt... Can we able to merge ABC, now it is showing some empty rows – Pandu Jul 15 '18 at 20:32
@Pandu Of course, but first: what about the other cells (clear and contact)? – Wolfgang Kais Jul 15 '18 at 20:45
Even those two can be – Pandu Jul 16 '18 at 18:36
@Pandu: Just added the details for merging the cells, hope you like it... :-) – Wolfgang Kais Jul 16 '18 at 22:07
I am getting the Few Blank(Empty) rows, how can I eliminate from final report – Pandu Jul 17 '18 at 19:45
The only reason for blank lines that I can think of is blank rows in your dataset, so verify your data and ecxlude records with blank Name, Clear, Contact or Viewed, wherever a row starts to be empty. – Wolfgang Kais Jul 17 '18 at 20:38
From ‘Added’ I am confused with point 2 and 3 ... if possible could you please provide the screen prints – Pandu Jul 18 '18 at 14:01
I'm sorry, I just tried to add 7 more screenshots, but SO moans that my post contains code that isn't formatted properly (although I did not add any "code"). I'm not gonna spend more time on this. Since you didn't mention the blank lines anymore, that problem seems to have vanished, and I'm, sure that you would manage to do the 3 steps, if you only started to try. – Wolfgang Kais Jul 18 '18 at 19:12
Yeah, Blank lines I removed in code... But Merging cells activity I am not able to get it ... missing somewhere – Pandu Jul 22 '18 at 19:09

SSRS Pivot with Dynamic Columns

1 Answers1