Topic
2 replies Latest Post - ‏2011-03-30T22:55:32Z by SystemAdmin
kellykerr
kellykerr
5 Posts
ACCEPTED ANSWER

Pinned topic How to pass a value from one query to another?

‏2011-03-30T15:58:47Z |
I have two questions:
1) In one query, I want to have logic that dynamically alters the query, for example, when a parameter, clientid, is passed I want the query to be "WHERE CLIENT_ID = clientid" else if parameter accountid is passed I want the query to be "WHERE ACCOUNT_ID IN (accountid)". This is a native query and will be initiated from SDK. How can I do this in Report Studio?

2) The above query output will list a number of transactions for a number of accounts. I want to extract the ACCOUNT_ID for each account from the above query and use it to get the name and address using a subsequent query. I would like to do this from Report Studio with one call from the SDK.

Please help.. Thanks
Updated on 2011-03-30T22:55:32Z at 2011-03-30T22:55:32Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1959 Posts
    ACCEPTED ANSWER

    Re: How to pass a value from one query to another?

    ‏2011-03-30T22:49:58Z  in response to kellykerr
    Personally, I find custom SQL queries to be a problem scenario. The short-term gains that you might see for some performance issues or tricky design scenarios do not normally outweigh the problem of maintenance. This type of approach advocates a siloed result where single reports are created by a central development team. This means that the strength of BI cannot be delivered to the end consumer via well-structured models which enable ad-hoc reporting.

    That being said, you can manipulate native SQL queries with parameters. What you will need is a set of macro functions to insert the filter statements if a parameter value is supplied. You could use something like:

    
    WHERE #prompt(
    'Account ID', 
    'integer', 
    '1=1', 
    'ACCOUNT_ID = ')# AND #prompt(
    'Client ID', 
    'integer', 
    '1=1', 
    'CLIENT_ID = ')#
    


    This will give you two optional parameters where you can supply values to one or both of the parameters. If you want only one parameter to be applied at a time then you can give one precedence over the other by nesting the macro functions like:

    
    WHERE #prompt(
    'Account ID', 
    'integer', prompt(
    'Client ID', 
    'integer', 
    '1=1', 
    'CLIENT_ID = '), 
    'ACCOUNT_ID = ')#
    


    If both an account ID and a client ID are supplied to the above expression then the account ID will be filtered and the client ID discarded.

    For your second question, I would say that you should just join the query to whatever it is that you are trying to filter. This would mean that you can avoid any complexities with the SDK. However, if you really must use the SDK then you could create a list report using the custom SQL query and run this to CSV or XML to then retrieve and process the result using the SDK. This will give you a list of the account ID values. You can then build this list into a set of parameter options to pass into a request for a report containing the name and address details. This second report would contain a filter with an IN clause which is defined with a parameter. You would pass the processed list of values from the first query as parameter values to the second report. If you post to the Cognos SDK forum there may be someone who could supply a sample of code to pass a list of parameters to a report request.