en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Wednesday, February 11, 2015 10:03:13 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Customer
Joined: 8/19/2014(UTC)
Posts: 72

I'm building a report for showing orders where order month = invoice month grouped per month dynamically.

Is that possible?

Doing it for one month dynamically is easy, I just create a crosstab showing orders with criteria order date = this month and invoice date = this month. But, when I reach the next month the history for the previous months of the year doesn't show.

If I set criteria order date = year to date yesterday and invoice date = year to date yesterday then for example orders sold in january and invoiced in february are grouped together under order month january.

When I reach year end I want the crosstab in my report to look like this:

January:
Total sales for all orders with order month January and invoice month January

February:
Total sales for all orders with order month February and invoice month February.

March:
Total sales for all orders with order month March and invoice month March.

Edited by user Friday, February 13, 2015 11:37:38 AM(UTC)  | Reason: Not specified

Niels Thomsen  
#2 Posted : Thursday, February 12, 2015 1:00:15 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Customer
Joined: 4/5/2011(UTC)
Posts: 103

Hi!

If I understand you correctly, it is indeed possible, using a comparison to make columns that contain both Order Date and Invoice Date as criteria.

For basic understanding of comparisons you can begin by studying http://www.targit.com/en/video/help/comparisons
In your case the only difference is that you have 2 sets of criteria for each element (column) of the comparison.

First element is filtered with Order Date = This Month and Invoice Date = This month
Second element is Order Date = Month - 1 and Invoice Date = Month - 1

...and so on.

I don't think you should add any Global Criteria but only the criteria that is defined on the Comparison element level.

Edited by user Thursday, February 12, 2015 1:01:57 PM(UTC)  | Reason: Not specified

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


SO  
#3 Posted : Thursday, February 12, 2015 1:29:08 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Customer
Joined: 8/19/2014(UTC)
Posts: 72

Thanks for the reply.

So if I understand then I should make 12 comparison elements? (this month, -1, -2, -3, -4, -5, -6, -7, -8, -9, -10, -11)

And then set local criteria for this crosstab = order date this year and invoice date this year so I only see months from this year?

Is it possible to name the elements with the month names with dynamic captions?

For exampel element names like this: "Order month {month} - Invoice month {month}" ?

If not I guess I have to use names like -1, -2, -3, -4 and so on.
Niels Thomsen  
#4 Posted : Thursday, February 12, 2015 2:38:52 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Customer
Joined: 4/5/2011(UTC)
Posts: 103

Ok that’s a nice problem :-) 

My idea was to make 12 elements yes – each filtered with Invoice Date and Order Date.
The elements would then be filtered excactly as you say:
First element
Order Date = This Month
Invoice Date = This Month


Second element
Order Date = This Month – 1
Invoice Date = This Month – 1


…all the way up to element no 12.

This would work fine with dynamic captions (through properties), which will then replace the names of the elements with the actual names of the months (combined with text as you describe it) - so far so good.

However I realized that there is one problem: You cannot override the comparison with local criteria like you describe.

That means that using this method I will always look 11 months back with the current month to the far left. I can’t figure out how to limit myself to this year.
The result would be (with February 2015 as a starting point):
Feb 2015 Jan 2015 Dec 2014 Nov 2014 Oct 2014 Sep 2014…Mar 2014

If that is not to your satisfaction I have an alternative.

I’m still making a comparison with 12 elements – only this time the elements are pointing to a specific month (within the current year). The full comparison will look something like this (note my test data is a little old - that's why it's saying 2012):

UserPostedImage

The criteria for one of the dates on one of the elements looks like this:

UserPostedImage

In February 10 of the columns will be empty - since we didn't do any business yet, so we have to hide all columns that are empty.
This is done with a visibility agent (properties tab) with the Condition:

UserPostedImage

And the Action taken by the visibility agent should be:

UserPostedImage

This should do the trick - now you will only see the completed months.

Edited by user Thursday, February 12, 2015 2:41:19 PM(UTC)  | Reason: Not specified

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


SO  
#5 Posted : Thursday, February 12, 2015 2:55:03 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Customer
Joined: 8/19/2014(UTC)
Posts: 72

Thanks a lot for the help, I don't think I would have thought of this way to use comparison.

I solved it with twelve elements, -1, -2, -3 and so on. This showed the last twelve months.

Then I tested to add a local criteria "Order date = this year" and "Invoice date = this year", this removed all months not part of this year, resulting in only february and january this year showing in my crosstab.

As for the dynamic captions I dropped it. Instead I used properties to hide the column with the element titles. Then I added "order month" and "invoice month" to my crosstab so I can see that they equals each other, for example

Order month Invoice month
January January

Thanks!
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error