Report Design: Request for a logic

[Ravisha]

Hi Cognoise,

We are on 10.1.1. I'm authoring a report for which I've got a requirement. Although it looks easy, it's kind of tricky to deal with. From the list below, if a user passes the parameter (in this case Product Type Code: 951), the list should filter for just 951 and must display 1 row. On the other hand, if the parameter passed is not in the list (Product Type Code) it should bring back with all Product Type's and Product Number falling under a specific ProducLine (in this case its Camping Equipment; Hardcoded).

It is very easy to do in T-SQL but unable to figure out an approach in Report Studio. (Guess I need some vacation  :o)

I'd appreciate if anyone can guide me through this.


Product Line          Product Type Code      Product Number
Camping Equipment            951                        3110
Camping Equipment            953                        20110
Outdoor Protection               966                        92110
Camping Equipment            952                        16110



Thanks in advance  :)

P.S: If I try to "IF" or "CASE" constructs in the detail filter it is throwing with the following error
UDA-SQL-0358 Line 74: Syntax error near "=".

[AnalyticsWithJay]

There are many ways of doing this.

The simplest, is to set the filter to Optional, which won't apply the filter when the value is not supplied.

Regarding the CASE/IF statement, it's perfectly valid to use but it's an issue many people run into. You must have a comparison operator and you must have a value returned on both sides of the comparison operator. If you remember these rules, it's simple as such:

Code Select Expand

CASE WHEN ?X? is NOT NULL
THEN [Product Code] 
ELSE 1
END

=
CASE WHEN ?X? is NOT NULL
THEN ?X?
ELSE 1
END

If it's null, you end up with 1=1.

You could also use a macro prompt and supply default values... Many options available, but the first option is the simplest.

[Ravisha]

Hi,

Thanks a lot for your quick response Sir/mam.

I'm afraid to say that either of the solutions which you've provided won't work.

1) Making the prompt as 'Optional'  (I'm not worried about making the prompt optional or required)

If the user doesn't pass a value. It would work wonderfully. But if the user passes a value which is not in the Product Type code list then the report would bring back nothing. (no data)

For example: From the list below if a user tries to pass a value 8976 as a parameter (for Product Type Code) since the value isn't in the column list (product type code) it would bring back nothing (no data). But my requirement is to ignore the value if it isn't in the product type code list and bring back everything which falls under a specific product line.

Product Line          Product Type Code      Product Number
Camping Equipment            951                        3110
Camping Equipment            953                        20110
Outdoor Protection               966                        92110
Camping Equipment            952                        16110

2) For the "CASE" statement which you've specified, it will be the same case as I've explained above.

Thank you so much for the advice. I appreciate your time  :)

[AnalyticsWithJay]

Oh, I see what you mean. In that case you could simply go to the "No Data Contents" property on the list and select "Content specified in the No data tab". In there, place a new list that uses an unfiltered query. It won't have an impact on performance as the query is only fired off if the list must be rendered.

You could use a similar approach using render variables if you have a slightly different requirement.

[Ravisha]

But that's on the report's layout and actually a very wise approach.  ??? But, I want to handle it in the query itself as I've to pass the value(s) into an another query and filter it.

So for example:


Scenario 1
when a user passes the parameter Product Type 951 --> it filters the Query 1 and since the value exists it will bring back with 1 row ---> filters the Query 2 with product type 951

Scenario 2

when a user passes the parameter Product Type 98547 (random number ) --> it filters the Query 1 and since the param value doesn't exist it in the product type column list it must bring back with all product types and product numbers falling under a specific product line ---> filters the Query 2 with all Product Types and Product Numbers.

So, in short, there has to be a validation checkpoint (logic) in Query 1 which filter the query if the param value exists or simply ignore to filter if the param value doesn't exist in the column which is being checked for.

[AnalyticsWithJay]

This feels like a puzzle where you're given one clue each time  ;D

Why go through the trouble of passing Query 1 value to Query 2 if all you're doing is passing the parameter? In other words, the parameter value is 951, and you want to add additional logic to pass the value 951 from Query 1 instead of the Parameter. Since they are the same value, it does not matter where it's coming from. Using the approach I provided, this is no longer necessary.

Using the render approach, you can apply the same technique to Scenario 1 and Scenario 2. It is best to keep things simple, unless there's an additional reason as to why you're trying to go about it this way? Perhaps you could explain to me the full requirement you're trying to achieve?

On a related note, I don't recommend the prompting approach you're using. The user has to remember free-form values, and if the value provided is not there, the user is given a report with data which they likely don't want (hence why they tried to filter on a specific value). I would recommend you reconsider your design to be more user friendly, whether that's using dropdowns, cascading prompts, or search & select.

[Ravisha]

Alright ! Let me explain the whole thing so you would understand why I'm seeking such kind of an approach.

Unfortunately, our user community is not a great fan of cascading prompts especially when they are on a report page. So there is a huge difference having cascading prompts on Prompt Page Vs on Report Page. They have a valid reason and I agree to it to a certain degree.  The reason is when you have cascading prompts on your report page, for every selection or click in the parent prompt (in this case Prompt 1), both the report and prompt (prompt 2 and prompt 3) values will be rendered. In this case, the report has a huge logic and it takes some time (approx 2-3mins) to run and they don't want to wait until the child prompt values are being fetched along with the report's data.

For this reason, I've removed the cascading effect on the prompts and filtering the queries associated with those prompts using the prompt parameters. With this approach, the prompt 2 and prompt 3 values will be filtered based on the selection made in the prompt 1. The downside is when they want to run for entirely a different selection in the prompt 1 and to execute report successfully, they have to switch the selections from previous run value to the default value ('optional') from both prompt 2 and prompt 3. I don't want them to do this for every run. Rather, consider all the parameter values (in this case prompt 1 value is new and prompt 2 and prompt 3 values are old) for every run and process them in  Query 1 and based upon the existence of the parameter values filter the report's query (Query 2). If the combination of prompt 1, prompt 2 and prompt 3 values exists it will bring back with 1 row and filter the Query 2 or if the combination of prompt 1, prompt 2 and prompt 3 values doesn't exist then filter the Query 2 with all possible values falling under the prompt 1 selection.

I know it is overwhelming  :o and a lot of little details to consider... :-\

[Ravisha]

So, in short, I'm trying to make the report more dynamic regardless of the combination of prompt selections.

If the combination of the value selected from Prompt 1 + Prompt 2 + Prompt 3 makes sense then filter the report's query accordingly.

OR

If the combination of  Prompt 1 + Prompt 2 + Prompt 3 doesn't make sense then filter the report's query with the highest level (in this case prompt 1 selection).

So, the above logical processing should take place in some or other query and based on the result set, filter the report's query...

P.S: Prompt 1, Prompt 2 and Prompt 3 are single-select.

[Ravisha]

Hello Cognoise !

Any suggestions please  :-\

[AnalyticsWithJay]

Thanks for the explanation. How about this?

Filter Validation Query:
Include a filter for Prompt 1, Prompt 2, Prompt 3. Add a data item to this query which you will be checking if it's null. Look for ways to optimize this query to return one row quickly. I cannot advise without knowing your data.

Fully Filtered Query:
Include a filter for Prompt 1, Prompt 2, Prompt 3, along with all your container data items and logic.

Default Filtered Query:
Include a filter for Prompt 1, along with all your container data items and logic.

1. Create a boolean variable which checks if [Filter Validation Query].[Data Item] IS NULL. Let's call it vIsDataNull.

2. Use a Conditional Block which references vIsDataNull. In the 'Yes' block, you will add your list/crosstab, pointing to the Default Filtered Query. In the 'No' block, you will duplicate the list/crosstab, this time pointing to the Fully Filtered Query.

There are many approaches, but IMHO this is the simplest.