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

Crosstab modify summary field properties

dajdouj

Hello, 
 
my purpose is to modify the way to aggregate on the crosstab. 
 
My crosstab: 
year1 year2 
month1 month2 month3 ... m1 m2 .... 
Number of tickets count (ticketid) 
 
In fact, I want to realize a count distinct in my crosstab. and I want to do this count regarding some conditions and i want to compare at each line of my query some dates with the begin and the end of month1 , month2 .... 
 
My questions are: where can I find the aggregation operation? Can I just add some condition to count or not to count some entries (tickets)?

Find more posts tagged with

Comments

mwilliams

You could use filters to remove values you don't want to include in the crosstab. As for seeing which operation is used by the aggregation, you can change this when creating the dataCube by choosing "edit" when you've selected your measure in the editor. Hope this helps.

dajdouj

But I am afraid that can not resolve my issue.

In the query I have some dates such as creationdate, actualfinish..

My aggreggation is count distinct ticketid.

the problem is for each month I want to compare creation date < begin month x and resolution date (actual finish) > begin month X , and if it's true I want to include the entry in the count. and month x are the dynamic column of my table

I think that I can not filter on those value, because those value are dynamic. And on the summaru field properties I can just specify the type of the aggreggation and some basic operations.

Hope that you can help me on that!!

mwilliams

Can you show me an example using numbers? Maybe create an example with the sample database or a flat file of data that's like yours and let me see what you're getting and then explain to me what you want the result to be in comparison to the result you get. I kinda understand, but I'm not sure that I'm getting it exactly enough to try it!

dajdouj

I have the following table
ticketid creation_date resolution_date

ds1 12/11/2011 18/11/2011
ds2 15/10/2011 19/11/2011
ds3 15/11/2011 03/12/2011

I want to draw a cross table with the number of the backlog tickets in the begining of each month

the dimension of my table will be resolution_date , the summary count ticketed
and if I put count on the summary i will find this table (this is a basic example of what I have the habit to use)

2011
November December
Resolved tickets 2 (ds1 and ds2) 1 (ds3)

And what I want is this table,

2011
November December
Backlog 1 ( ds2 ) 1 (ds3)

I am searching a way to calculate the backlog count. And for that I have to see in the beginning of each month with tickets are in progress (Not still resolved , ie first of the month < resolution date)

Hope that you see better , and you can help on that?

Many thanks..

dajdouj

this is my naswer in word with more clear tables ..

mwilliams

I used the sample database to make this report. I think it does what you're wanting. I use aggregations to figure out how many orders are ordered before the first required date's month. Then, I use values I have stored in persistent global variables in the dataSet script to keep track of the opened and closed orders each month. Then, in the crosstab, I deleted the original measure and replaced it with a text box with my own computations to figure out how many orders went into the given month having not met their close (required) date. This was created in 3.7.1. Let me know if you have questions.

Another way to do this could be to create a table that is grouped on month to figure your values, hide this table, store its values into arrays and create a scripted dataSet out of it to use for your crosstab, but this way works too.

dajdouj

Many thanks..

I think that I am using version 3.2.17 .. I can not see the crosstab methods.. well in xml..

Can you send me the report with a lowest version?

mwilliams

Can you try changing the xml source version info (the first few lines) in the report attached to match the version info in the xml source in one of your reports? If that doesn't work, let me know.

dajdouj

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="85347" data-time="1321378514" data-date="15 November 2011 - 10:35 AM">
Can you try changing the xml source version info (the first few lines) in the report attached to match the version info in the xml source in one of your reports? If that doesn't work, let me know. </blockquote>
 
I have tried this in your report to see it but It does not work..

mwilliams

Here is the same in 2.3.2.

dajdouj

Thank you for your support, your examlple is very clear..

I have ried to adapt my report..

Well, first I have datetime on my datset, I have used BirtDateTime.year and . month to get months and years.

I have had a problem with with the tostring function , I used concat to specify the name of the persistent global value.

with all of this I have just nan on all the table.. I think thtaon the table, I did not access to the same calculated gloabal value (fetch)

I am sending you the report, ah I am using the srciption source (reports with maximo)

can you just see what I did in my report ?

mwilliams

Without being able to run your report, it's hard to figure out exactly what's going on. What issue did you have with .toString()?

Your issue could be because of the way you create your global variable names. You might do some checking to see what values are stored in your variables at a given time to make sure the variables are named correctly and are storing the correct values. If you can recreate your issue with the sample database or a simple scripted dataSet that I can run, I'll be able to help more.

dajdouj

I fix it.. Thank u for your help.. GREAT!!

Well, now I have a new challenge. In fact, it is to display the cross tab on one chart with month as x-axis and the calculated values as y-axis..

Do you have any idea?

mwilliams

Have you tried naming your crosstab in the property editor and using this as the source for the chart data? I know you can do this with regular tables and I thought you could with crosstabs too, but I'm not totally certain.