Transposing Date Rows to Columns

<p>I am trying to find any resources I can for turning a single date column into multiple columns based on unique dates. (a parameter will decide how many unique dates show up in the dataset)<br><br>
I have attached an xlsx file (

Find more posts tagged with

Comments

micajblock

<p>No need to transpose the data, you can simply use a crosstab. In order to do that you need to build a data cube and the measure will use a first aggregate. Look at the attached example using a modified version of your spreadsheet (only the data).</p>
<p> </p>
<p>If you really want to transpose data you can use this method - <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/2-transposing-data/'>http://developer.actuate.com/community/forum/index.php?/files/file/2-transposing-data/</a></p>

SoccerMZG

<p>Thanks Mica,<br><br>
Unfortunately the user needs to be able to drag in fields from a data object directly into the report. A crosstab will not allow this to happen. So I am tasked with finding a way to transpose the rows. Thank you for your files. I will let you know how those work out.</p>

SoccerMZG

<p>Hi Mica,<br><br>
At first look of your example provided above, I am having difficulty understanding one section of your report design. In the open of your scripted dataSet you have the following:<br>
</p>
<div>var odaDataSource = new OdaDataSourceDesign( "Test Data Source" );</div>
<div>odaDataSource.setExtensionID( "org.eclipse.datatools.enablement.oda.xml" );</div>
<div>odaDataSource.addPublicProperty( "FILELIST", dsrc.getProperty("FILELIST").toString() );</div>
<div> </div>
<div>var odaDataSet = new OdaDataSetDesign( "Test Data Set" );</div>
<div>odaDataSet.setDataSource( odaDataSource.getName( ) );</div>
<div>odaDataSet.setExtensionID( "org.eclipse.datatools.enablement.oda.xml.dataSet" );</div>
<div>odaDataSet.setQueryText( dset.getQueryText() );<span> </span></div>
<div> </div>
<div>de.defineDataSource( odaDataSource );</div>
<div>de.defineDataSet( odaDataSet );</div>
<div> </div>
<div>queryDefinition = new QueryDefinition( );</div>
<div>queryDefinition.setDataSetName( odaDataSet.getName() );</div>
<div>queryDefinition.setAutoBinding(true);</div>
<div> </div>
<div>var pq = de.prepare( queryDefinition );</div>
<div>qr = pq.execute( null );</div>
<div> </div>
<div>recordsIterator = qr.getResultIterator( );</div>
<div><br><br>
I am not following this portion of your report as I cannot see your folder structure and do not work with xml. Currently I am doing a POC with Excel data. I use an excel data Source called "dbExcel" and an excel dataSet using dsExcel (the excel table you saw previously). Could you please explain to me how this relates to my excel dataset? Thank you</div>

micajblock

<blockquote class="ipsBlockquote" data-author="SoccerMZG" data-cid="147617" data-time="1494633063">
<div>
<p>Thanks Mica,<br><br>
Unfortunately the user needs to be able to drag in fields from a data object directly into the report. A crosstab will not allow this to happen. So I am tasked with finding a way to transpose the rows. Thank you for your files. I will let you know how those work out.</p>
</div>
</blockquote>
<p>Why not? It is actually easier with a data object as you do not need to create a data cube.</p>