(Relational)Inserting a Column with Static Values as a part of a Crosstab

[HelloCognos]

Hey, :)

I have a requirement to enter some constant values as a column into a Crosstab. Please see attached.
So far I tried the Text Item or Creating a Data Item but the values needs to be based on each Row of the
Crosstab. The yellow highlighted values have to be entered manually in a bi-weekly basis and there is no
column associated to the at the database backend level.

Please let me know what you think at your convenient.

Thanks a lot as always!!

[cognostechie]

Create an Excel spreadsheet and insert two columns in it:

1. Car_ID (Find out the values from your existing package)
2. Cars_in_Garage - Insert the values here (what you highlighted in yellow)

Cognos allows you to join the Excel file with your existing package so you will get the desired results.

[HelloCognos]

Well, I was hoping for a total of five rows only, I won't need to use the Manage External Data option since in PROD Server, this EXCEL will have to reside
in a secured server and security guys may have an issue with it.

I was hoping that I could have a Query Build from SQL and have these values generated in the Queries SQL so the Crosstab can recognize it but if this is not
possible, I may have to request for a new Column in this table and add the necessary values without Hard Coding in the report.

The other option could be to ask the Framework Manager guys to generate this values as a part of the Subject Query so each row will include this value; but,
this value may change every month so that brings up another issue.

I have full access to the Report Studio report in PROD so if I can get it done in the report, it is perhaps the most suitable approach.

Let me know what you think?

Thanks for your time, :)

[HelloCognos]

I think I got it!! So simple. Can't believe I didn't thought about this.
Created three Data Items: (Value1, Value2, Value3)
Create the below Calculation
case
when ([CAR_TYPE] in ('BENZ') )then
([VALUE1])
when ([CAR_TYPE] in ('BMW') )then
([VALUE2])
else
([VALUE3])
end

Unfortunately, these values will have to be hard coded to the report until the Front End
can deal with it so it is automatically updated within our DW.

Thanks,

[cognostechie]

Well, I was hoping for a total of five rows only, I won't need to use the Manage External Data option since in PROD Server, this EXCEL will have to reside
in a secured server and security guys may have an issue with it.

I was hoping that I could have a Query Build from SQL and have these values generated in the Queries SQL so the Crosstab can recognize it but if this is not
possible, I may have to request for a new Column in this table and add the necessary values without Hard Coding in the report.

The other option could be to ask the Framework Manager guys to generate this values as a part of the Subject Query so each row will include this value; but,
this value may change every month so that brings up another issue.

I have full access to the Report Studio report in PROD so if I can get it done in the report, it is perhaps the most suitable approach.

Let me know what you think?

Thanks for your time, :)

Sorry, but I have made my policy of not providing workarounds to anyone. The workarounds (also called 'quick and dirty' solution) is an approach used by numerous companies and from what I have seen, this method starts with a very little issue which does not seem to be a significant problem at that time but causes the BI implementation to fail miserably later on. It's not just one workaround but the approach which causes the entire implementation to be full of multiple workarounds causing disintegration, data issues, governance issues, depriving the users of available features etc.

What you are trying to do indicates that your organization does not have any governance on the quality of work and in that situation providing an quick and dirty solution can cause holes into the system.   

[HelloCognos]

I totally understand your concern. Thanks for the honesty and your time!

[cognostechie]

Nice that you understood ! From your posts, I see that you have the ability to think creatively and figure out solutions and that will make you succeed more than the person who is dependent on the book for everything ! All the best !

The correct way for this would be to create a column in the database table and populate/update that from an ETL process.

[HelloCognos]

Thanks for the kind words. Yes, I started a request with our DB Team.  :)

[Lynn]

I think I got it!! So simple. Can't believe I didn't thought about this.
Created three Data Items: (Value1, Value2, Value3)
Create the below Calculation
case
when ([CAR_TYPE] in ('BENZ') )then
([VALUE1])
when ([CAR_TYPE] in ('BMW') )then
([VALUE2])
else
([VALUE3])
end

Unfortunately, these values will have to be hard coded to the report until the Front End
can deal with it so it is automatically updated within our DW.

Thanks,

Having the data stored and governed is the ideal situation, as discussed in this thread.

For your example above, you can prompt for the values rather than hard coding them in the report. The values are displayed directly in the report so there is (at least) transparency for anyone consuming the content. Adding a footnote indicating that the figures were supplied at run time can further help a report recipient understand the source of this information.

[HelloCognos]

As always great words Lynn,

I like both points for the prompt and the footnote. At this point, it looks like it's only myself and one more person will run this report; however, I
actually need to findout why I can't get the values populated in the Crosstab since the values that needs to be populated can be derived from
my calculations.

I open another request if I can't figure it out why the Crosstab is not showing the values.

Thanks a lot!!!! :) :)