I have a stand alone Query that I use for obtaining key values and then filter other queries that go against different Fact Tables based off of those Parameter Values. Seems feasible and logical... But I am getting a Cross Join Error. Anyone have any insight as to why this would be happening? I don't know exactly how Parameter Values are handled, but assume they are cached on the Report Servers and from there able to be used in any query.
I'm not sure about this
but in query properites put NO for "Auto Group & Summarize"
In the Query Explorer, with all queries displayed, there is a setting for "Cross Product Allowed". Set this to "Allow" for all of your queries.
I did this and the report clocks. But there should NOT be a relationship between the two queries... One Query gets me the parameter values. Then Report Studio should use those parameter values to filter all the other queries. To me, logically thats how it should work. But instead it is actually trying to tie these queries together somehow... When I look at the SQL for that Query that is being used for the Parameter Population, it doesn't have any referencing to the items used in the other queries.
Generate sql for the query and check the join between two tables.
The SQL is fine. I dumbed it down so Q1 is going against only 1 table for the prompts and Q2 so it is only pulling the key that I am wanting to filter on. I have come to the conclusion that the tool starts to behave poorly when you have a auto submit prompt for another cascade while using that same parameter in a filter (Q2 Filter). If I remove that part of the filter for Q2, the report runs fine. Problem is, I need that in the filter. Its very odd... A model that I have against SQL Server cannot replicate this issue, but from my other Model that is against a DB2 Database is where the problem happens.