YTD Crosstab report

[arkam]

Hi,

This is my first post on this forum. Very new to SAS VA (started using last) and any help will be highly appreciated. 

I have a requirement to build a YTD report in a crosstab (it can be any other object type as well but I am trying to use crosstab). I have attached a document with Sample data in the first table and the YTD report in the second tab (sheet 1). I am expected to create a report exactly as shown in "sheet 1"

I am able to get the below correctly but as soon as I add the third column "Budget Room Revenue" as a measure it appears as a row but I need it as a column on the right of "Last year". So when I change it to a "category" it goes all messy and I am totally clueless how can I even add 'Budget Variance" and "Last year Variance" columns in the way required. Can anyone please help. I tried using hierarchy but it didn't work for me. May be I need to change the data structure to get it to work?

Items	Actual	Last Year
RR	21,042,942	18,130,146
FB_R	11,660,946	10,308,000
OOD Revenue	2,296,447	2,293,568
Rental & other Income	1,027,019	977,613
Total Revenue	36,027,354	31,709,328

Apologies in advance if anything in this post is not according to the forum rules.

[Sam_SAS]

Hi arkam,

The sample table provided has separate columns for everything:

2016 Rental Revenue	2017 Rental Revenue	2016 Rental Revenue Budget	2017 Rental Revenue Budget	2016 Sports Club Revenue	2017 Sports Club Revenue	2016 Sports Club Revenue Budget	2017 Sports Club Revenue Budget
144	145	114	113	114	113	114	113

It might be better if the data could be restructured more like this:

Year	Market	Revenue	Budget
2016	Rentals	145	114
2016	Sports Club	143	111
2017	Rentals	146	118
2017	Sports Club	142	112

Crosstabs in VA will always have all the measures on the same axis, and the column and row headings are driven by category values (or hierarchy levels, which are also categorical values). Your data basically does not have any categorical data, so some restructuring is needed to create a useful crosstab.

Does this help? I don't think that the exact crosstab in your XLS sheet is possible even with restructuring, but you should be able to create a useful crosstab to display your data.

Thanks

Sam

[arkam]

Thanks for the suggestion. As you mentioned, even after restructuring I am not able to get the report in the way mentioned in that tab. Is there any way of creating such a report? Doesn't have to be crosstab..

[Sam_SAS]

What are the key features of the visualization that you want to make?

In your spreadsheet you have a crosstab of sorts where the horizontal categories have different measures. You can't do this in VA, as far as I know. Each value on the horizontal axis will have all of the measures. I don't think any other report object would do what you want.

Can you share a screenshot of what you are able to create at this point? (You can blank out the cell values in an image editor.) This would make it easier for us to help you.