zooming in cognos (drilltrue?)

[Devasto]

ive been searching for some time now for a way but so far i havent been succesfull. i know that within cognos you can drill in your data, like going from day to week to month ect.

But is there wat to drill? or more exactly focus more on a specific data?

For example, when you have a report that generates a list of orders you make it contain, the client and the ordernumber and total price. can you then make it possible that if you click on lets say the ordernr, you scope in and get all the details of just that order? is there a way to do this by drilling, or forward the selected ordernr to a new report and juse the selected ordernr as a criteria?

thx for reading and possible youre respons.

 

[Devasto]

and could i use an alias in the Framework manager? so it would know i meen a certain data item with a alias i specify in FM?

 

[blom0344]

Yes, no problem. You can define a database column umpteen times in the FM model with each time a different name..

Ties Blom

 

[Devasto]

using aliasses in FM worked fine,

Now the next problem.
I want to add a textbox prompt to my report, so its possible to search for a keyword. but i will need to give in a package item, wich will result that when you search only the selected columns will be checked for the keyword.

Instead i want my textbox prompt to be able to search the entire record for the enterd keyword is it possible to link my textbox prompt to My Query for example? or tell it to check multiple columns

Greetings D.

 

[blom0344]

Checking multiple columns would be possible by using a special view (or an SQL query subject) in the Framework that constitutes a union over the fields you want to search.
Obviously with this union the datatype should be the same for all columns in the definition:

SELECT DISTINCT T.A FROM TABLE T
UNION
SELECT DISTINCT T.B FROM TABLE T
UNION
SELECT DISTINCT T.C FROM TABLE T
......

Ties Blom

 

[Devasto]

Ties,

Could you specifie the steps i will need to take?

So far i created a basic query subject but it now generates the basic code, but were do i add the union code?

so once i added the basic query subject i republish my package so it can be used. Will i need to add this query subject to my basic report query? and then create the textbox prompt on this subjectquery??

Greetings

 

[blom0344]

1. Create a new Query subject
2. Choose Data source type instead of Model
3. Deselect 'Run database query subject wizard'.
4. You now have an empty SQL based Query subject that allows you to define an SQL expression.
5. As long as you use regular expressions you can build a union here.

Depending on your model you add this one to the FM model.
Good practice is to only define SQL objects in the data-layer and then use a new Model query subject on top of the SQL object.

If you only going to use this union for assigning prompt values to a parameter, then it should not matter how you join the union in the model (tricky bit, cause you only want to base prompts on it and not use it for reporting)

Ties Blom

 

[Devasto]

dont got it to work yet,

I have build the Query Subject and joined it to my Facttable, published it and created a textboxprompt on it, package item = return item of the query
and the target query = my basic query,

wenn i run the report no errors,
wenn i put part of a word in the field i get no errors but it doesnt seem to return anything, wenn i put a compleet word in it what can be found in the record i get an error,

"An error occurred while performing operation 'sqlOpenResult' status='-28'."

suggestions?

 

[blom0344]

The query you associate with the prompt should be based on the union query subject. The parameter of the prompt is then used in the fact query to pass on the selected value (using a filter in the fact query definition) like:

[some name].[fact].[dataitem] = ?parameter?

Ties Blom