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; } } }

Sort with parameters

marcSeglar

Hello,

Sorry for my poor english.
I have a trouble when sorting my report. I have a crosstab with many products and divided by month and year.
I want to sort this products by the quantity in the last month and I can't. In the crosstab propertis, in the sort tab I put the key quantity
and in the 'Select row Member value' appears Month and year, I can put 01 and 2011 manually and works,
but I want to put the parameter "fecha_final"(2011-01-01) in this section like BirtStr.left(params["fecha_final"].value,4) for the year
and BirtStr.right(BirtStr.left(params["fecha_final"].value,7),2) for the month to be always the last date. But I can't put parameters in 'Select row Member value'.

I can do this? I dont' Know if it's possible or if it can be done differently.

I don't know if I explain.

Thanks and regards,

Marc Seglar

Find more posts tagged with

Comments

marcSeglar

Ooops...

Nobody??

:_(

mwilliams

So, you've got a date as a parameter and you want to be able to choose to sort the crosstab based on the last column using the date parameter? Is this correct? If so, let me know your BIRT version and I'll give it a try.

marcSeglar

Thanks for your response.

Yes, what you say is correct but the last column of month have 5 columns more that one have the quantity, and the quantity is the sort key.

I have the 2.6.0 BIRT Version

marcSeglar

I attached a picture of the layout to clarify

mwilliams

You're going to have to set the sort condition on your crosstab in the beforeFactory event of your report design since you can't use an expression for the member value settings in the sort editor. I'm going to work on this as well, so if I get something, I'll post it in here. If you figure it out before I post in here, please post your solution for others who come across this thread with the same question. Thanks!

marcSeglar

Ok.

I think I have the solution, I post the solution tomorrow if it is true.

Thanks

marcSeglar

Hello,

My solution is as follows, I put an union in the sql script with the value of the last date and I put the year as 9999 and the month as 99. In birt I put the sort condition by quantity and sorted by the year 9999 and 99, and so its ok, but now in the crosstab appears the year 9999 and month 99 and I want to hide, but i can't, I tried with the visibility when year = 9999, but nothing and i tried with filter condition but nothing too.

You know how to hide the last page of the report or the crosstab with year = 9999?

I think that this is used in the sorting and can not be removed from report, with visibility or filter condition. With any script?

Thanks,

Marc Seglar

mwilliams

So, you essentially have two columns at the end of your crosstab that are the same? One represents the last month and one is your dummy sort column? If this is the case, why not filter out the data from the last actual month and use the dummy column instead. You can just change the header labels to be the last month.

mwilliams

Check out this example. I created a sort on the second crosstab, just using two set values and in the beforeFactory script, changed the values to match those of the EndDate parameter, so the crosstab is always sorted by the last column. Let me know if you have questions.

marcSeglar

Thanks very much for all, for the first.

In the example, the beforeFactory is very comlex...but I try to change the month and year and I put 07-2010, and doesn't sort. In the sort tab, in the select row memeber value you put manually 2004 and 06, allway sort by this value? or do not have in count?

I solved by another form, but this example is interesting.

I explain my form, the code is a bit weird,

Resume: If case(date) = case max(date) put 99 else date, and the report header when is 99 change to the last date using another column that have the correct date. In the select row memeber value I put order by year 9999 and month 99. It's Ok.

The param periodo is for group by month, trimester, semester or year.

I removed the union that have said before to not have two equal months but is the same idea, in the last month put 99 and in the year 9999.
In the main select I put:

MONTH:
CASE when concat(E.ANY,'-',CASE " + params["periodo"].value +" WHEN 4 THEN E.MES WHEN 3 THEN E.TRIMESTRE WHEN 2 THEN E.SEMESTRE WHEN 1 THEN E.ANY END) = (select max(concat(E.ANY,'-',CASE " + params["periodo"].value +" WHEN 4 THEN E.MES WHEN 3 THEN E.TRIMESTRE WHEN 2 THEN E.SEMESTRE WHEN 1 THEN E.ANY END)) AS `PERIODO` FROM x` E WHERE CONCAT(`any`, '-', `mes`) >= LEFT('" + params["fecha_inicial"].value +"', 7) AND CONCAT(`any`, '-', `mes`) <= LEFT('" + params["fecha_final"].value +"', 7)) then '99' else CASE " + params["periodo"].value +" WHEN 4 THEN E.`mes` WHEN 3 THEN E.`trimestre` WHEN 2 THEN E.`semestre` WHEN 1 THEN E.`any` END end as `MES`

YEAR: +/- the same, and put 9999

marcSeglar

Solved.

Thanks for all.

Sure I'll put more question, 'cause in the same report I want to show 50 products for every section but i can't, but thats another topic.

Sorry for my poor Barcelona English and thanks again.

Marc Seglar

mwilliams

In the crosstab sort, I manually put the values, yes, but the script in the beforeFactory replaces those values. The only things you need to worry about changing would be the group name if yours isn't just "group", the parameter name in which I grab the EndDate year and month, and that might be it. Not sure how you put 07-2010 in, so I don't know how to give an explanation to why this didn't work. If you have a solution that works for you, you don't need this script, but it's always there if you run into any issues with your workaround!

probs

Hi ,
 
This example helped me a lot and it works really cool.
But i ran into a problem when there are 3 level of column groups in the crosstab.
How can i set the value for the third column level in the Before Factory script.
 
Can you please help me on this.
 
Thanks & regards,
Prabal

probs

Hi, 
 
Sorry for posting in an old blog but this context seems to be correct to what i am looking for. 
For example how to set the year of week in the attached file in before factory. 
 
can anyone please help me out in this. 
 
Thanks 
Prabal

probs

Got it.
 
Thank u.
we can use
sortKey.getMember().getProperty(
"memberValues").get(0).getProperty("memberValues").get(0).setValue("1"); 
 
Thanks,
Prabal

mwilliams

Sorry. I didn't see your post on this thread. Glad you got it working! Let us know whenever you have questions.