Rolling 5 Months

[wanzek1]

I am attempting to create a report using a "snapshot" in viewpoint. Basically what you do is each month we run a "snapshot" of certain data and it stores it in a table. I need to take this information and have 5 months of columns with about 40 rows of data and formulas for each Month. What is the best way to do this? I tried using a crosstab but I don't think this is going to work well.

If I could group horizontally instead of vertically this would be no problem what so ever. Any suggestions would be greatly appreciated!

Currently I am using Crystal 2011

Thanks!

 

[Madawc]

I tried using a crosstab but I don't think this is going to work well.

If I could group horizontally instead of vertically this would be no problem what so ever.

Crosstab expert lets you switch the fields used for column and row. Does this help?

If it's something else, please explain.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[wanzek1]

The problem with using a crosstab is I have trouble formatting it how I want. A crosstab limits you on what you can get it to look like. Also, no matter what options I check I still get totals on the left and I don't want any totals.

Are there formulas that I could use that would act like a crosstab but be individual formulas for everything?

Thanks!

 

[RTag]

What is the database type and the structure of the table?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[wanzek1]

SQL
Latin 1 BIN

Is that what you are looking for?

 

[RTag]

If it is sqlserver 2005 or above you can prepare the data using pivot operator. What are the columns in the table ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[wanzek1]

Columns are dates and the rows are integers. It is sqlserver 2005 and above.

How do I use the Pivot Operator?

 

[RTag]

If you have a table structure like this one:
ID int,
ExportDate datetime
ColumnA int

You can use a select like this to select the data

SELECT
ID,
CASE WHEN DateDiff(mm,ExportDate,GetDate())=0 THEN CurrentMonth'
WHEN DateDiff(mm,ExportDate,GetDate())=1 THEN Prior Month'
WHEN DateDiff(mm,ExportDate,GetDate())=2 THEN '2 months ago'
...
WHEN DateDiff(mm,ExportDate,GetDate())=4 THEN '4 months ago'
END as RelativeMonth,
ColumnA
FROM MyTable WHERE ExportDate BETWEEN <date1> and <date2>

the result will be
ID RelativeMonth| ColumnA
1 4 months ago 1
2 4 months ago 3
3 5 months ago 5
4 Prior month 2

to pivot this data use PIVOT operator. You can check thsi link :

http://msdn.microsoft.com/en-US/library/ms177410(v=SQL.90).aspx

I guess yor SQL should look like this one:

SELECT ID
FROM (SELECT
ID,
CASE WHEN DateDiff(mm,ExportDate,GetDate())=0 THEN CurrentMonth'
WHEN DateDiff(mm,ExportDate,GetDate())=1 THEN Prior Month'
WHEN DateDiff(mm,ExportDate,GetDate())=2 THEN '2 months ago'
...
WHEN DateDiff(mm,ExportDate,GetDate())=4 THEN '4 months ago'
END as RelativeMonth,
ColumnA
FROM MyTable WHERE ExportDate BETWEEN <date1> and <date2>) t
PIVOT
(
SUM (ColumnA)
FOR RelativeMonth IN
( [Current month], [Prior Month], ..., [4 months ago] )



The result which will be sent to Crystal will be

ID Current month Prior Month .... 4 months ago
1 SUM
2 SUM
3

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[Madawc]

A crosstab limits you on what you can get it to look like. Also, no matter what options I check I still get totals on the left and I don't want any totals.

In Crystal 2008, at least, you can choose Crosstab Expert > Customise Style and choose 'Suppress Grand Totals' for rows, columns or both.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[wanzek1]

The suppressing of grand totals works when you only have one row but I have about 40 rows of data. I uploaded an example PDF but I am not sure if it will work. So far I only have 2 rows of data and it already is not working how I would like it to.

 

[RTag]

wanzek1, the method I proposed will give you more control because you will deal with a regular table, but you will be not able to use dates for the columns (instead names should be relative : current month, prior month etc.)
What is the purpose of this report? Are you exporting it to excel ? If this is the case you can get some ideas how to process the data without crystal reports from this video:

http://www.r-tag.com/Pages/Preview_Demo.aspx

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.