mechanism for correlating and reporting data based on unique index
<p>Hi all,</p><p> </p><p>I'm new to Birt and can use it to create simple reports based on simple select queries.</p><p> </p><p>However, I need to do something a little more complicated and am getting completely stuck..</p><p>I'm sure it's something easy, but am running around in circles so decided to ask you guys!</p><p> </p><p>Anyway, here goes;</p><p>This is my dataset sample:</p><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]ID,UNIQUEID,TIMESTAMP,LOG [/font]</span></div><div style="margin-left:40px;">[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18899,uniqueID_98_97_197,10/1/2013 2:07:54.098000 PM,SD[/color][/font]</span></div><div style="margin-left:40px;">[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18904,uniqueID_98_97_197,10/1/2013 2:07:54.098000 PM,RE10[/color][/font]</span></div><div style="margin-left:40px;">[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18901,uniqueID_98_97_197,10/1/2013 2:07:54.098000 PM,SUB9[/color][/font]</span></div><div style="margin-left:40px;">[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18894,uniqueID_98_97_197,10/1/2013 2:07:54.098000 PM,SC4[/color][/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18919,uniqueID_98_97_198,10/1/2013 2:07:56.827000 PM,RE10[/font]</span></div><div style="margin-left:40px;">[font="'courier new', courier, monospace;font-size:12px;"]18917,uniqueID_98_97_198,10/1/2013 2:07:56.827000 PM,SC1[/font]</div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18918,uniqueID_98_97_198,10/1/2013 2:07:56.827000 PM,SUB5[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18917,uniqueID_98_97_198,10/1/2013 2:07:56.827000 PM,SD[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18898,uniqueID_98_97_199,10/1/2013 2:07:56.895000 PM,SC4[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18902,uniqueID_98_97_199,10/1/2013 2:07:56.895000 PM,SA[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18905,uniqueID_98_97_199,10/1/2013 2:07:56.895000 PM,SUB4[/font]</span></div><div style="margin-left:40px;">[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18907,uniqueID_98_97_200,10/1/2013 2:07:57.694000 PM,SC5[/color][/font]</span></div><div style="margin-left:40px;">[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18908,uniqueID_98_97_200,10/1/2013 2:07:57.694000 PM,SA[/color][/font]</span></div><div style="margin-left:40px;">[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]18909,uniqueID_98_97_200,10/1/2013 2:07:57.694000 PM,SUB9[/color][/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18913,uniqueID_98_97_201,10/1/2013 2:07:58.194000 PM,SC[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]18912,uniqueID_98_97_201,10/1/2013 2:07:58.194000 PM,SC6[/font]</span></div><div> </div><div> </div><div>I guess I need to filter/reorganize this and output data based on the uniqueID to create the report as I want (although am not sure if this step is technically necessary)</div><div><div style="margin-left:40px;">[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]uniqueID_98_97_197,[/color][/font][font="'courier new', courier, monospace;font-size:12px;"]SC4,SD, RE10 ,SUB9[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]uniqueID_98_97_198,RE10,SUB5 ,SD[/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]uniqueID_98_97_199,SC4,SA,SUB4[/font]</span></div><div style="margin-left:40px;">[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]uniqueID_98_97_200,SC5,SA,SUB9[/color][/font]</span></div><div style="margin-left:40px;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]uniqueID_98_97_201,SC,SC6[/font]</span></div></div><div> </div><div>The report should eventually look like this showing the number of instances of each element occurance..</div><div> </div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]SC1 1[/font]</span></div><div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SA 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SB 0[/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SC 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SD 1[/font]</span></div></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]SC2 0[/font]</span></div><div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SA 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SB 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SC 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SD 0 [/font]</span></div></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]SC3 0[/font]</span></div><div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SA 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SB 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SC 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SD 0[/font]</span></div></div><div>[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]SC4 2 [/color][/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SA 1 [/font]</span></div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB1[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB2[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB3[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB4 1[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB5[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB6[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB7[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB8[/font][font="'courier new', courier, monospace;font-size:12px;"] [/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB9 1[/font]</div><div> </div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SB 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SC 0 [/font]</span></div><div>[color=#daa520;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"] SD 1 [/color][/font]</span></div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB1[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB2[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB3[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB4[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB5[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB6[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB7[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB8[/font][font="'courier new', courier, monospace;font-size:12px;"] [/font]</div><div>[color=#daa520;][font="'courier new', courier, monospace;font-size:12px;"] SUB9 1[/color][/font]</div><div> </div><div><div>[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"]SC5 1 [/color][/font]</span></div><div>[color=#ff0000;]<span style="font-size:12px;">[font="'courier new', courier, monospace;"] SA 1 [/color][/font]</span></div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB1[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB2[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB3[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB4[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB5[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB6[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB7[/font]</div><div>[font="'courier new', courier, monospace;font-size:12px;"] SUB8[/font][font="'courier new', courier, monospace;font-size:12px;"] [/font]</div><div>[color=#ff0000;][font="'courier new', courier, monospace;font-size:12px;"] SUB9 1[/color][/font]</div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SB 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SC 0 [/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"] SD 0 [/font]</span></div></div><p> </p><p>I'm not sure what birt and or sql mechanism would allow me to organize the data like this..</p><p> </p><p>eventually, I'll have to add a new column based on input date, so any recommendations or pointers would be great!</p>
