Exclude box type none columns from the generated SQL

[Phsy]

Hi!

Here is a short story background about my issue: I have to create a report with AdHoc functionality , so basically the user will select which columns she/he wants to see during the reporting. I made this solution with a checkbox prompt, passing parameters to boolean variables for each possible column, then pair these variables with each column for 'if selected then box type visible, if not selected then box type none.' This solution is working, however there is a very annoying issue. The box type none columns data still fragmenting the overall number of rows, so for example if all columns visible then the total number of rows are 500, and if only 3 columns are visible then the total number of rows still 500. So its not aggregating the identical rows. I attached a sample screenshot to better understand my problematic results.

Any ideas welcomed,
Tx.

[New_Guy]

Hi Phsy,
Box type none helps to hide columns but it will still render it and this will cause the generated sql to pick this columns to. There is render property for list column, try using it and that should resolve the issue. If not try with a case statement with the column pick parameter like case when ?p_column? = 'A' then [X].[Y] else '' end. Not a perfect syntax but you can play around. Let me know if you still need help.
Good luck
New guy

[Phsy]

Hi NewGuy,

Thanks for your reply, I already tried render variable but its still generating the unneccessary SQL. The problem with case when solution, that this must be multi select, so in one scenario the user want to see 5 columns, the other case need to see 10, or 15 or even 40. So its totally up to the user how many columns must be in the result.

Tx

[MFGF]

Hi NewGuy,

Thanks for your reply, I already tried render variable but its still generating the unneccessary SQL. The problem with case when solution, that this must be multi select, so in one scenario the user want to see 5 columns, the other case need to see 10, or 15 or even 40. So its totally up to the user how many columns must be in the result.

Tx

Hi,

It sounds like you're trying to reinvent your own authoring tool? Might be a silly question, but why not just give the users access to Cognos Workspace Advanced and let them bring in whichever columns they need?

MF.

[Phsy]

Yes, good ponit. But my user said it looks awful and they dont want to use it.... so i must stick to RS. But basically yes, i need to create a report with CWA functionalities.

[New_Guy]

Hi Phsy,
If it is a must do in Report studio, the only way out is create a parameter for each column and use case in the data item and render the column based on the same respective parameter. You have to create a prompt with 2 static choices which will be a radio button type. Then you have to size your prompts to 50px width from what I have tried and insert them into a table and make them pretty.

case when p1 = 1 then column 1 else p1 end  --------- for col 1
case when p2 = 2 then column 2 else p2 end  --------- for col 2

Let me know if you need any further help.

Good luck
New Guy

[Phsy]

Hi NewGuy,

Do you think with this solution, the relevant data will be next to each other? I think this will break up the join between relevant rows. And also, will this work with 40 columns? I think the query will be really slow.

[New_Guy]

Hi Pshy,
This wont break the relation as the columns will be in the same list and the query will run a bit slow, it is a give and take solution. The way I mentioned is you are substituting the actual value with the parameter. Dont use this columns straight in the filter, instead use the actual path/definition if this columns are used in the filter. It is time consuming but is the only way out from report studio for your requirement. Let me know how it goes.
Good luck
New Guy

[Phsy]

Hi NewGuy,

By any chance, can you attach me a .txt export of your example? I am not quite sure I get the parameter setup you mentioned there. Please do for more then 2 cols, 5 could be easier to understand.

Thank you!

[New_Guy]

Hi Pshy,
Attached is not a working sample, and just for the concept. Try it out and see if it helps.
Good luck
New Guy

[hespora]

There is another way to d something like this. See http://cognospaul.com/2014/09/14/prompting-fields-list/