I have a text box prompt in which the user enters 10 digit number, this 10 digit number is similar to 3 different data items like account number,loan number and policy number. If the user enters a 10 digit Account number, report related to Account number and Account details like wise If the user enters a 10 digit Loan number, report related to Loan should get displayed conditionally. If the user enters a 10 digit Policy number, Policy report has to be displayed.
I have tried creating a string variable with the condition
if (?TextPrompt? = '[Query1].[Account Number]')
then ('Account') else if (?TextPrompt? = '[Query1].[Loan Number]')
then ('Account') else ('Policy')
which is not picking up the query item value and executes the else part for all selection. Kindly somebody let me know is there any better solution to achieve this. Cheers
Is there any way to identify what kind of number it is from the number itself? For example, is there a rule that all account numbers start with 525 for instance?
I've done something similar. Try creating a union query in SQL like this:
select accounts.accountID, 'Account' as Type
from accounts
where accounts.accountID = #prompt('number','integer)#
union all
select loans.loanID, 'Loan' as Type
from loans
where loans.loanID = #prompt('number','integer)#
union all
select policies.policyID, 'Policy' as Type
from policies
where policies.policyID = #prompt('number','integer)# Attach this query to the page, putting both data items in the properties. Create the string variable on [Query1].[Type]. Put a conditional block in the page set to that variable.
Hi Paul, thanks for oyur response. Yes! All the Account No. starts with '000' and Loan No. with '012' and Policy No. with '888'. Could you let me know how to achive this using the Union Query to sent, do i need to mention these in #prompt('number','integer)# .
It would be a great help if you gave me a sample report. Thanks a lot!!! Cheers
Well, if you can identify them like that the report becomes much easier.
To start create a string variable.
Let's call is ReportType
Add values "Account", "Loan", "Policy" and "Unknown" (without the quotes)
Use the following expression:
case substring(ParamValue('Number'),1,3)
when '000' then 'Account'
when '012' then 'Loan'
when '888' then 'Policy'
else 'Unknown'
end
Then you just need to use a conditional block with the previous as the block variable. Or, alternatively, you could use it as a render variable on different pages.
Lots of options available.
Thanks Paul! It is a brilliant solution. Let me try and update you, am sure this will work. Cheers. Thanks a Lot!!!
Paull thanks! your solution works :)
Just curious that is there any possible way to achive this if there is no any specific rule like Account number starts with '000', Loan number starts with '012' etc..
Please let me know. Thanks
Kind Regards,
K 2
Quote from: k2 on 16 Jun 2010 09:37:56 AMJust curious that is there any possible way to achive this if there is no any specific rule like Account number starts with '000', Loan number starts with '012' etc..
Hi,
If the data is all in the same table, you will need some kind of logic to be able to determine the different types (Account/Loan/Policy etc). If you cannot do this based on the starting digits as in Paul's example, then how else can they be differentiated? Is it based on length, or based on a certain character being present, or...? Whatever the rules are for determining the type, you will need to identify them and figure out how to implement them in your report. Once you know the rules, if you are struggling to implement them, just post up how the rules work and we will try to suggest solutions.
Best regards,
MF.
If there was absolutely no logic to identify the numbers you would have to use the my first suggestion.
Lets make the following example tables
Account
---------
1
4
7
10
13
Loan
---------
2
5
8
11
14
Policy
---------
3
6
9
12
15
Looking at the numbers alone there is no way to logically see which one is which. Thus the union query.
select accounts.accountID as ID, 'Account' as Type
from accounts
where accounts.accountID = #prompt('number','integer)#
union all
select loans.loanID as ID, 'Loan' as Type
from loans
where loans.loanID = #prompt('number','integer)#
union all
select policies.policyID as ID, 'Policy' as Type
from policies
where policies.policyID = #prompt('number','integer)#
If the user enters 7 into the prompt the SQL will then look like this:
select accounts.accountID, 'Account' as Type
from accounts
where accounts.accountID = 7
union all
select loans.loanID, 'Loan' as Type
from loans
where loans.loanID = 7
union all
select policies.policyID, 'Policy' as Type
from policies
where policies.policyID = 7
Which will then result in
ID | Type
-----+---------
7 | Account
You would set the page query to it and use the following as a string variable:
[Query1].[Type]
Your conditional block would remain the same and everything should work perfectly.
Hi Paul, thank you once agian :) am just looking for exactly the same scenario you explained. Thanks a lot for giving me an idea ,which i thought it cannot be done.
Could you mind sending me a sample report xml or notepad file which implies the same logic,as i am just learing advance report writing and not much cmfortable with SQL queries. It would be a great help. Please let me know. Thanks for your time. Cheers
I am current working in Cognos version 8.4 with SQL Server.
That's a very nice approach, Paul!
K 2, if you are not comfortable with writing an SQL query to do this, set up three separate queries in your report, one based on the Account details, one on Loan details and one on Policy details. Add an extra data item to each, with an expression of 'Account', 'Loan' and 'Policy' (a different value for each query). Add a detail filter to each query to trigger the prompt - use the same variable name in each case, eg. [AccountID] = ?IDNumber? for the Account query, [LoanID] = ?IDNumber? for the Loan query etc.
In the query explorer, add another new query, and feed it from a UNION object from the toolbox - unioning together the queries for Account and Loan details. Add a final new query, and feed it from another UNION object from the toolbox, this time unioning together the previous new query and the Policy query.
This final query can then be used as the basis of your report.
One other point that is important for both Paul's SQL-based approach and this Report Query-based approach is that the number of items returned by each part (and the data types of the items) must be consistent across all three subjects - Account, Loan and Policy, or else the unions will not work. Both approaches also assume that Account, Loan and Policy details are held in separate tables/query subjects too - if they are all in the same table (as I mentioned before), this approach will not work and you will need to identify the rules to be able to distinguish between them.
Regards,
MF.
Thanks a lot! MF and Paul. Its working great and am sure it is an amazing piece of work. Cheers for your efforts and Great help!!!
¬ K 2
Hey Guys,
FYI, You can union more than 2 queries in a single "Union" within Report Studio.
No need to union 2 then use an additional union to union in the third query.
Nuffsaid.
Quote from: Nuffsaid on 17 Jun 2010 12:01:07 PM
Hey Guys,
FYI, You can union more than 2 queries in a single "Union" within Report Studio.
No need to union 2 then use an additional union to union in the third query.
Nuffsaid.
Interesting! A union only appears to have two inputs (for source queries). Can you elaborate?
MF.
by default Union shows only 2 queries. You can drag and drop a new query above or below one of the queries being used in the union, and a 3rd query is added to the union.
As tupac_rd stated, drag a new query and drop it just below one of the existing shortcuts used in the existing union. This will create a 3rd query and also a 3rd shortcut for the union. AFAIK there's no limit to the number of queries you can add.
Nuffsaid.
Nice! Thanks Fellas! :)