nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Cross Tab - don't want any calculations

olivia08

I am trying to create a report that diplays output that is similar to what I have below. All of the data comes from one dataset. The columns would be based on time (date - YYYY-MM). The rows are based on entity types (like tranportation types..airplane, car, bike). I used a crosstab to get the row and columns set, but I do not want a sum in the intercepting cells. I just want to display the actual value in each cell that goes with each row/column intercept. Am I using the wrong type or chart for what I'm trying to do?

My dataset returns the date, type, and total for each record. The dataset from the db looks similar to this:

2004-08 Car 89
2004-08 Bike 17
2005-06 Car 42
2007-02 Air 60

The output I'd like to have in my report is:

2004-08 2005-06 2007-02
car 89 42
bike 17
plane 60

I want to display it all and use a report parameter to choose begin and end months that are tied to the date field so only those will show. I plan to only show about 6 months at a time. Thanks for your help.

Find more posts tagged with

Comments

olivia08

<blockquote class='ipsBlockquote' data-author="'olivia08'" data-cid="97061" data-time="1330620142" data-date="01 March 2012 - 09:42 AM">
I am trying to create a report that diplays output that is similar to what I have below. All of the data comes from one dataset. The columns would be based on time (date - YYYY-MM). The rows are based on entity types (like tranportation types..airplane, car, bike). I used a crosstab to get the row and columns set, but I do not want a sum in the intercepting cells. I just want to display the actual value in each cell that goes with each row/column intercept. Am I using the wrong type or chart for what I'm trying to do? 
 
My dataset returns the date, type, and total for each record. The dataset from the db looks similar to this: 
 
2004-08 Car 89 
2004-08 Bike 17 
2005-06 Car 42 
2007-02 Air 60 
 
I'd like the output to just diplay the info with the date at the top and the types down the left side and each total to be in the applicable cell (no calulations). 
 
I also would like to use a report parameter to choose begin and end months that are tied to the date field so only those will show. I plan to only show about 6 months at a time. Thanks for your help. </blockquote>

mwilliams

If you only have a single row for each date/type combination, there shouldn't be a calculation done. If there is more than one row with the same date and type, the default will be to SUM the measure values. If you only want to show the first, go to your dataCube and double click on your measure field and change the aggregation function from SUM to FIRST. Hope this helps. If not, let me know.

Hans_vd

Hi Olivia,

Why can't you use the SUM function?
If there is only one value for each row/col, the sum of that one value is equal to that one value.

You can also use the FIRST function, if you really don't want to use the SUM function.

Hope this helps
Hans

johnw

Take a look at the following example. The only real way to do this and have the control you are looking for is to embed a Table report item in the column header of the cross-tab. This will let you have the horizontal spanning of the dates going across, but let you control what is going on vertically. This way you wont need to use the aggregation calculation of the cross tab.

In my example the rows are dynamic, so the values do not line up, but from your example they will always be the same, so they should line up just fine.

The way this was done was by using a small piece of script on the Cross Tabs label that sets a global variable of the value to filter by. In the table, I am using a filter (although a binding could just as easily be used for performance reasons) so that only the rows for that current month are displayed.

olivia08

Thanks to everyone. I think this may be working but I'm not quite sure because there is too much data on here for me to actually read it. Is there a way to make my report parmameter work so that only certain months of data is shown? I tried creating a parameter group that includes Begin and End date but when I tried to tie them to the Date field it didn't work. What am I doing wrong?

johnw

Filter it on the data set itself. So, create the begin and end date parameter, then in the original data set either modify the queries WHERE clause: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>where
date between ? and ?</pre>
 
This will create two data set parameters. Then you can set the expression of the data set parameters to the report parameters. 
 
Or you can use the Filters tab in the data set.

johnw

Attached is an updated example. I used Filters in the dataset instead of modifying the query, but the idea is the same. You are limiting what gets returned from the data set before the data cube consumes it.

olivia08

Great...its working. My only other issue is that negative addMonth values don't seem to work. I set my End date to BirtDateTime.addMonth(params["Begin Date"].value,-6) and get an error. When I use 6, it works fine. So it only fails when I use a negative. The goal was for users to select a begin date and the report would display the last 6 months.

olivia08

Never mind...I see the problem and have fixed it. You guys have been very helpful.