I'm building a document with a dashboard-like appearance. I'm using radio buttons for selecting the year. Sadly in one of my datasets the attribute year is another attribute, even if contains exactly the same values. How can I solve the problem of matching attributes basing on their values in documents?
Basically i want to click on the radio button 2013 and select the value 2013 in graph1 containing the attribute named year and in graph2 containing the attribute named year-of-observation.
I did as you told me.
I added an advanced qualification fiolter to the report that contains year-of-observation:
[Year]@ID = [Obs-Year]@ID
Then I added the attribute year to the report.
But when i try to execute the report have the following error:
Report: xxxxxxxxxxx
Job: 8600588
Status: Execution failed
Error: SQL Generation Complete QueryEngine encountered error: The report contains a cross join in pass 1 between tables ANSWERS and YEAR. Execution is aborted per the cartesian join setting.. Error in Process method of Component: QueryEngineServer, Project xxxxxx , Job 8600588, Error Code= -2147212544.
Starting Time: 15:13:50
You can't... or you can try to add the attribute year in the report with year-of-observation and put a filter year = year-of-observation, at this point many things can happen:
If the two attributes have no relationship, the lookup table for year will be added to your report query in cross join, but you don't worry because the filter will limit the results to the right ones.
If one of the attributes is parent of the other you won't have cross join, then remove the filter, but double check the results (just in case)
If Year is already mapped on one of fact tables used by the report you and there is no 1:1 correspondence between year and year-of-observation you could have wrong results.
Also
Assuming Year and year-of-observation have no relationships, if they are mapped on the same lookup column of the same table (no aliases, no role recognition) MicroStrategy will use only one lookup for both attributes so there is no need for the filter.
A word of advise
Anyway it's a good practice to keep different logical entities (like two attributes) separated, but (probably not your case) when they are the same thing just use one.
Advanced level
Another possible solution is to play with relationships: you can try to make the Year parent of year-of-observation, then include the year attribute in the report where is missing, but if this is really feasible depends on your schema, you could end up with messy hierarchies path if you have already children for Year and Year-of-observation.
Cross Join Error
In case you follow solution #1, you could receive an error related to the Cross Join. This happens because the SQL Engine is trying to protect you from Cross Joins (usually a bad thing).
To disable this error you need to change the following VLDB property for the report (or you can change it also at Project level if you have many reports with Cross Join):
VLDB Properties -> Joins -> Cartesian Joins Warning