Altering the layout of a report using a value from prompt

[Riotknight]

Hello,

This is my first post asking about Cognos so bear with me if this doesn't make sense.

I have a report I am working on that requires the user to type in a serial number and the report will display all items available with that number, but the list columns need to be different based on the class of the item with that serial number (for example they type 1234 it has a class of 6 in the database so it displays columns A, B, C, where as if they select 5678 it has class 5 which displays columns A, C, D).  The class is in a table in the database connected to the serial number table.

Doing an if statement in the data item hasn't worked so far since the column headings need to change as well as the actual data.

I'm having an issue now with the fact that I need it to do a database query after they select the serial number to put the class in a parameter so I can use it in a conditional block to sort the columns.

My company is trying not to use javascript as much as possible so a solution not requiring it would be prefered, but if it's not possible without javascript I would be open to it.

Thank You

[MFGF]

It sounds to me like you need to include all the possible columns you could ever need to see in your list, then conditionally render the relevant ones depending on the item class you derive from the serial number. You will probably need a string variable for this, returning different values for each class value (maybe even just picking up the class value itself?), then use this to conditionally render/hide the appropriate columns in the list.

Cheers!

MF.

[pricter]

If I understand well the problem is that you want to sort the list depending on the class of the serial number.

So create a data item with if statement which will declare which column to use depending on the class and add this sort item to sorting property of the list.

[Riotknight]

It sounds to me like you need to include all the possible columns you could ever need to see in your list, then conditionally render the relevant ones depending on the item class you derive from the serial number. You will probably need a string variable for this, returning different values for each class value (maybe even just picking up the class value itself?), then use this to conditionally render/hide the appropriate columns in the list.

Cheers!

MF.

Hi MF,

The problem is I can't seem to derive the Class from the Serial Number and use it as a variable in the condition.  I pull the Class in as a data item but when I try and use that in the condition is says it's invalid.

I'm still fairly new at Cognos so maybe I'm missing something simple on how to do this.

Thanks

[Riotknight]

If I understand well the problem is that you want to sort the list depending on the class of the serial number.

So create a data item with if statement which will declare which column to use depending on the class and add this sort item to sorting property of the list.

Thank you for your answer, but it's not sorting the values in the list, I want to include/exclude list columns based on the class.

[CognosPaul]

This sounds like fun.

I'm a little confused on your goal, so lets see if I understand it.

The user enters a valid serial number. The database then finds the class associated with it. The report then conditionally renders columns based on the class returned.

I can think of a number of ways to do this, none of which require JavaScript.

The first question is how do you determine which columns need to be rendered? Do you have a table that has class, fieldsToRender fields? Or is everything hard coded?

Second, will there always be 3 rendered columns, or will the number of columns change based on the class?

Third, do you have rights to modify the framework?

While I wait for those answers, I'll give you a hint so you can try to figure it out: parameter maps.

[Riotknight]

Hi PaulM,

The user enters a valid serial number. The database then finds the class associated with it. The report then conditionally renders columns based on the class returned. 
Correct

The first question is how do you determine which columns need to be rendered? Do you have a table that has class, fieldsToRender fields? Or is everything hard coded?
It will be hard coded for now, but I will try and get it dynamic later

Second, will there always be 3 rendered columns, or will the number of columns change based on the class?
It can change

Third, do you have rights to modify the framework?
yes

While I wait for those answers, I'll give you a hint so you can try to figure it out: parameter maps.
I haven't messed with parameter maps before, but I'll see if I can do something with that.

Thanks

[MFGF]

I think you just need to bring in your class item as the expression for your string variable, then add the distinct classes as return values.

You should then be able to select each class value from the variable, and set the conditional rendering of your columns to suit.

I am on my iPad right now so I can't check this is correct, but I will take a look in the morning.

Regards,

MF


Sent from my iPad using Tapatalk

[CognosPaul]

My concern is that simply hiding the fields would still force Cognos to include them in the generated SQL, and thus group the rows by them. I could be mistaken, and that's the first thing you should try.

Would there ever be a max number of fields? Could you always say with certainty that there will be no more than 5 or 10 fields? Here's my train of thought. Set up a table that has the class ID and all of the fields delimited with pipes.


Class | Fields
------+------------
  ABC | 01[NS].[NS Table].[FieldA]||02[NS].[NS Table].[Field2B]||03[NS].[NS Table].[FieldC]
  ABD | 01[NS].[NS Table].[FieldA]||02[NS].[NS Table].[Field2B]||03[NS].[NS Table].[FieldD]
EABD | 01[NS].[NS Table].[FieldE]||02[NS].[NS Table].[Field2A]||03[NS].[NS Table].[FieldB]||04[NS].[NS Table].[FieldD]
EFBD | 01[NS].[NS Table].[FieldE]||02[NS].[NS Table].[Field2F]||03[NS].[NS Table].[FieldB]||04[NS].[NS Table].[FieldD]


Put it in a parameter map, either hard coded or as a reference to this table, called fieldLookup (you can call it whatever you want, but I'm using fieldLookup for the rest of this post).

Now, back in the report, create a data container that returns the correct class. Easiest way is to use a Singleton or to associate the query to the page. Whatever you do, remember to put the class inside the properties of the data container.

Place your list inside that data container. Place each field that could possibly be used in the report into that list. On each column, create a render variable along the lines of [Clients].[Fields] in ([NS].[NS Table].[FieldA]).

If that works, great. If, however, the list is still grouping by the hidden fields, then you'll need to use a fairly complex macro.

[absriram]

Hi MF,

The problem is I can't seem to derive the Class from the Serial Number and use it as a variable in the condition.  I pull the Class in as a data item but when I try and use that in the condition is says it's invalid.

I'm still fairly new at Cognos so maybe I'm missing something simple on how to do this.

Thanks

RiotKnight,

If you are using Class in the render variable but not in the list report itself, then you will get an error.  To get around this error, select the list report (select any column and click on the ancestor button in properties pane and select "list"). In the properties pane, edit "Properties" and select the "Class" column in the list.

That would resolve the error.

Thanks,
Sriram.
http://cognosonsteroids.blogspot.com