18 Replies Latest reply: May 2, 2011 3:08 PM by Toni Kautto

# Set analysis depending of a selected field in a tree view list box

Hi,

on a my report I have created a tree view list box to show year and month dimensions. For it, I have created a specific year_month field by joining year and month. In this way, an user can select year = 2010 + month = 9 and year = 2011 + month = 3 and so on.

Then I have created two pivot chart, the first one with some measures with yeartomonth data for each month, and the second one with monthly measures. In other terms, in the firts pivot chart if I show measures related to year = 2011 and month = 3 it is as I show the measure values between january and march 2001, while measures in the second pivot are really monthly amounts. My source db is made in this manner.

So, when I make one or more selections on the tree view list box in the second pivot chart I must show the measures respect to the selected year and with month minus or equals to the selected month. I need to find the right set analysis expression. It must be similar to this:

sum({\$<Month = {"<=\$(varSelectedMonth)"}>} MeasureAmount)

but I add the part related to the year.

This expression must function when I select year = 2010 + month = 10 and year = 2011 + month = 3. For these selections, I must show 2010-01, 2010-02, 2010-03, 2010-04, 2010-05, 2010-06, 2010-07, 2010-08, 2010-09, 2010-10 and 2011-01, 2011-02, 2011-03.

Moreover, I have the problem to extract years and months selected from the tree view list box and put the right values on a variable to pass to the set analysis expression. I can select year = 2010 + month = 8 and year = 2011 + month = 3 and year = 2011 + month = 5 and so on; in other terms, I can made a multiple year + month selection.

Any suggests to me, please? Thanks

• ###### Set analysis depending of a selected field in a tree view list box

Hi,

can you attach an example?

Andrea

• ###### Set analysis depending of a selected field in a tree view list box

Unfortunately no. The sample should be a project.

I have tried to explain it.

Thanks

• ###### Set analysis depending of a selected field in a tree view list box

I have tried successfully to create the STATIC expression that I want to implement as a dynamic one:

sum({\$<Year = {2010}, Month = {"<=12"}> + \$<Year = {2011}, Month = {"<=3"}>} MeasureAmount)

This expression matchs with the multiple selection Year = 2010 + Month = 12 and Year = 2011 + Month = 3. But I can select more year and month couples in my tree view list box.

Thanks

• ###### Set analysis depending of a selected field in a tree view list box

Any suggests to solve this issue? How can I render this set analysis expression for + symbol?

Is it possible to create an expression inside SUM() and before the MeasureAmount to create the right set analysis expression?

Thanks

• ###### SV:Re: Set analysis depending of a selected field in a tree view list box

It would be really beneficial if you could attach a sample QVW, to clarify the data model and your current objects.

If your app conatins sensitive data, please use the Scrambling function to make data unreadable. Also feel free to remove sheets and objects that are not relevant to this specific scenario.

Settings > Document Settings > Scrambling

• ###### SV:Re: Set analysis depending of a selected field in a tree view list box

Ok, but I've the QV in Italian language. How the Scrambling function is translated?

However, the problem is about how calculating a sum by union of sets. The sets are known only at run-time. So I can talk to dynamic union of sets inside a set analysis expression.

Thanks

• ###### SV:Re: Set analysis depending of a selected field in a tree view list box

Ok. In Italian the Scrambling function is named as "Maschera".

I have attached my sample file. In the first sheet I have the tree view list box with caption "9. Anno - Mese - ...".

In the sheet 2 I have the pivot named "Personnel Effort". I'm trying to render dynamic the expression named "test". For my purposes, "test" expression functions well. In test2 expression I'm trying to solve this issue.

Thanks

• ###### SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

The first thing you need to do is to resolve all the synthetic keys that you currently have in the document, as you can see in the the table viewer (press Ctrl + T in desktop client). By having these synthetic keys you are very likely to end up with incorrct results in your calculations.

Synthetic keys appear when your tables use multiple fields as keys, instead of unique key fields. You can also end up in problems when performing aggregation on key fields, so it can be wise to create unique key fields that you never use in calculation but only for linking tabels together.

Before evaluating calculation/presentation problems further, please work on eliminating the synthetic keys from your data model.

• ###### SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Hi Toni,

I'm agree with you that synthetic keys could cause some performance problems but for me it is important to separate CodiceCommessa (in English, CommitteeCode), Anno (in English, Year), Mese (Month), etc. There aren't any circular references.

Have you read the entire post, please? The issue, synthetic keys or not, is to create a set analysis expression with a dynamic union of sets. Is it possible to do it? If not, solving the synthetic keys is useless for me. I cannot lose too time.

Many thanks!

• ###### SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

I agree that synthetic keys by default would not give you problems, but they are very likely to cause trouble as you will not be able to control what data you are actually working with in your objects and expressions.

There should be no problem to create a set expression to cover a span of months. Generally it is a efficient idea to have a master calender that you link your dates to, this way you keep the date only in your other tables and then have fields in the master calender table to cover the convertion to Year / Month / Week / YearMonth / Day or what ever filtering capabilities you need.

Still, I think it is no time loss for you to eliminate the syn keys and get a proper data modell before you continue working with the docuemtn objects. If you finish the docuemtn and find out the results are incorrect hen you will need to redo everything. By securing your datat model first, you will not have to spend additional time on that again.

• ###### SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Ok, Toni, but the focus of this post it: "Is it possible to create a set analysis expression by unioning dinamically some sets?".

I have written a static sample, that functions:

sum({\$<Year = {2010}, Month = {">=7"}> + \$<Year = {2011}, Month = {"<=3"}>} DAYS)

Is it possible to render dynamic this static expression? Yes or not, please? Each set depends on two dimensions.

I can know how many sets only at run-time.

Thanks

• ###### SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Moreover, I could have two different dimensions instead of Year and Month, fe ProductCode and CustomerCode. This can be a generic question that could be useful for entire community.

Many thanks.

• ###### SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Please see attached example of how you can use a master calendar table, in order to utilize set analysis to make date limited selections.

I hope this can helpful as an example on how calendar limitations can be made when using Set Analysis. My suggestion is that you apply a master calendar table also to your app, to get a unified an easy to apply date measurement.

If there is anything unclear with the sample doc, please let me know and I will try my best to explain. Else if this resolves your problem, please mark this thread as resolved.

• ###### SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Thanks Toni, but I think I explain better the issue.

I don't want the result on the "Year To Month" chart or on the "First to last month" chart.

I have a tree view list box with year and month and with it I can select more one values for these dimensions.

So I could have these two selections, fe, year = 2010 + month = 3 and year = 2011 + month = 2. I want to show the measures for these dimension values:

• 2010 01, 2010 02, 2010 03;
• 2011 01, 2011 02.

Not between 2010 03 and 2011 02.

But I could do also more two selections and so on. Now, is it clear the issue?

Thanks

• ###### SV:Re: SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Let's try again. I have added a pivot table with a calculated dimension, so no usage of set analysis.

• ###### SV:Re: SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

No Toni, the behaviour is like your Year To Month char but with the possibility to select MORE year and months. I must show all months between january and the selected months for each selected year.

So, if I select year = 2011 and month = 3 I need to show the measure values for year = 2011 and month equals to 1, 2, 3.

If I select also year = 2010 and month = 2 I need to show the measure values for year = 2010 and month equals to 1, 2, and so on for additional selections.

Thanks

• ###### SV:Re: SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

Hi,

I'm solving by constructing the set analysis expression as a VBScript macro code.

I hope to don't use VBScript.

Thanks

• ###### SV:Re: SV:Re: SV:Re: SV:Re: SV:Re: Set analysis depending of a selected field in a tree view list box

It is a very tricky scenario you have suggested, to make these multiple selections and get dynamic presentation.

Any way, here is what I think is the best I can come up with based on the requirements you have set up. In the example I have added 4 expressions (making N <= 4), of which each is set statically to look for the N:th selection in the tree view. This means that you should only make one selection per year, and that the columsn will only be show if the N:th selection has been made.