Cross Product Allow in query killing performance. Is there a better suggestion?

[patrickthatcher]

Wondering how I can do this properly. The way I'm doing it now is with a cross product query that is killing performance:

On my report I have a dropdown of dates and my report returns as expected. I have a requirement to add an additional parameter that when chosen will ignore the date selected in the date dropdown and will select a date based on flag in the date table = 'Y'.  This flag will change daily.
My current code is shown below. To get it to work properly, I created a new query that filtered the date based on the 'Y' flag so that I get back one date.  I then needed to set my main query to Cross Product Allowed = Allow.  This setting is understandably killing performance. Is there a better way to accomplish this?

Code Select Expand


Case When (#prompt('pSubscriptionType')# = '0')
Then
([Reporting View Daily].[Ending Date Filter] = #prompt('pRptDate','date')#)
When (#prompt('pSubscriptionType')# = 'D')
Then
([Reporting View Daily].[Ending Date Filter] =[p1DlyDateCurrentQuery].[Date])  //p1DlyDateCurrentQuery is filtered query where Flag = 'Y'
else
(1=1)
END



Thank you

[gpollock]

Hi patrick,

1) Why can't you just add the flag filter to your own query, instead of joining to a query that's filtered?  If p1DlyDateCurrentQuery has the filter for dates that hit the flag, why not just add it to your first query?

2) It's not in the scope of your question, but any common flags we have for dates are actual flags in the table and are modeled as such.  Current date, YTD, Prior year date, are all flags that we easily filter on.

[patrickthatcher]

Thank you.
Funny thing is that I just put that exact code in about 1 hour before your response - duh. 

Code Select Expand


Case When (#prompt('pSubscriptionType')# = '0')
Then
([Reporting View Daily].[Ending Date Filter] = #prompt('pRptDate','date')#)
When (#prompt('pSubscriptionType')# = 'D')
Then
([Reporting View Daily].[Cur Flag] ='Y')
else
(1=1)
END



What I failed to add to the issue is that I use my prompt('pRptDate','date') in the title of my report to show the date requested for the report.  By selecting the 'Y', my report title still shows whatever date happens to be in my dropdown. Example: dropdown shows 7/11/2015, but the Day flag is set for 8/14/2015, the pRptDate prompt will show 7/11/2015 even though the report data is for 8/14/2105.
Any suggestions on how to get around this?

thanks again for the help

[gpollock]

For that, you can also use a caste statement in a report expression, so you could use something similar to below.  I would simplify it by just saying [Reporting View Daily].[Ending Date Filter].  It looks like you should only have one date in your query, regardless of what you used.  You'll need to set the page to use that query, which shouldn't be an issue.

[cognostechie]

Thank you.
Funny thing is that I just put that exact code in about 1 hour before your response - duh. 

Code Select Expand


Case When (#prompt('pSubscriptionType')# = '0')
Then
([Reporting View Daily].[Ending Date Filter] = #prompt('pRptDate','date')#)
When (#prompt('pSubscriptionType')# = 'D')
Then
([Reporting View Daily].[Cur Flag] ='Y')
else
(1=1)
END



What I failed to add to the issue is that I use my prompt('pRptDate','date') in the title of my report to show the date requested for the report.  By selecting the 'Y', my report title still shows whatever date happens to be in my dropdown. Example: dropdown shows 7/11/2015, but the Day flag is set for 8/14/2015, the pRptDate prompt will show 7/11/2015 even though the report data is for 8/14/2105.
Any suggestions on how to get around this?

thanks again for the help

What you are displaying in the report title is the value of the Prompt which will always be whatever is in the Drop Down. In the event it is not selected and the pSubscriptiontype = 'D' then the value that should be displayed in the report title should be the value of the date column of the row where Cur Flag = 'Y' !! If you are only displaying prompt('pRptDate','date') then it will always display the Date from the prompt which is what it is doing.

[patrickthatcher]

Thanks cognostechie & gpollock. I replaced the prompt with the date field and was able to display the correct date. 
What I was trying to do if there wasn't any data was to present back the prompt value so they would know what date it was they selected that didn't return data. What happens now is:

with data
Date: 08/13/2015 - Thu, Dept Type: RETAIL,......

without data
Date: , Dept Type: RETAIL,......

But then I thought better of it.  There should always be data unless the data for that date doesn't exist.

thanks again and have a good weekend