CrossTab PivotTable instructions

[Versile]

I wanted to share my experiences with the crosstab and pivottable features of Telerik Reporting as it took me a significant amount of time to develop and I hope this will help someone else reduce their time.

I have some values I want pivoted from a schedule and printed appropriately. Since we work schedule by days instead of hours this is slightly different than what most people would do but I believe you could use this example to duplicate.

I had a table output like this:

installdate (date), locationname, team_neam, techname, technumber, ma, installerStatus (number)

I wanted to pivot on the installdate and create dynamic columns across the page and open a new page only after grouping by week.

So my output would look like:

Team Name (grouped), TechName, Tech #, MA, dynamic columns based on date range grouped by week number, then sub-grouped (though each resulted in a single record) by date and day of week.

So first I created a blank report, with header, footer, and detail section. I set up the header section with a logo, and an htmltextbox, in the codebehind I grab the first row of my output and get the locname from there to fill in the htmlbox, and get the month name from a passing variable to initiate the report. So my top has a logo and "xxx Location  |  August Schedule". My footer has a legend a single textbox with lightgreenbackground and black text that says working, another textbox with red background and black text that says off and a page # cue. I set the pagenumber by dropping a textbox in the bottom right, set the textalign to right, aligned it with the end of my header row and put this value in it: 

='Page ' + PageNumber + ' of ' + PageCount

Then comes the fun/confusing part. I've had some difficulty with the crosstab portion of the designer so after some trial and error I started copying the files (.cs, designer, and resource file) to a safe spot before every change. If the change failed I copied them back.
I created a dataset with the fields I want, used the crosstab wizard to create a basic report with only team name on the row group, installdate on the column group, and sum of installerstatus on the detail group.

I then finished the wizard and added some code to the codebehind to fill the dataset. I also added some user functions under to clean up my data (and get week number of year etc) Put these at the bottom of your class, but inside. You must rebuild your project to get access to them in the designer.

	public static int Week(DateTime date)
	{
	System.Globalization.CultureInfo myCI = new System.Globalization.CultureInfo("en-US");
	return myCI.Calendar.GetWeekOfYear(date, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
	}
	public static string ShortDate(DateTime shortDate)
	{
	return shortDate.ToString("MM-dd");
	}
	public static string ShortDay(DateTime shortDate)
	{
	return shortDate.ToString("ddd");
	}

Then I change back to designer view and right click on the teamname group in the area and added a child group for each column. Very Important: Add a child group to team name, do not add a child group and try to add another child group to that group! Then I added a child group to installdate, and another child group to installdate for my date, and dayofweek column/rows. Then right click on the first column group I created and set the group expression and value expression using the previous examples (see ProductSalesbyYear/QTR) (right click on it and choose properties, set Expression, click expression, choose user functions, select the one you want, then fields, and click the field you want). I set the sort on these to be the installdate field itself (not the user function!). I also changed the groupbyexpression for my prebuilt column of installdate for the Week # using userfunction. I had to merge some cells, and manually type my header names above each one in the corner tab but that was easy enough. I also set the team name field, and it's rowgroup field to vertical 90 degree angle.

Hope this help someone else, if you have any question let me know I would be glad to help.

[Vassil Petev]

Thank you for sharing your experience with Telerik Reporting, Versile! We hope that it will be valuable to other people as well. I have updated your Telerik Points for your community effort!


Best wishes,
Vassil
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.