2 prompts to a macro from DAT file 1

[LRubanowicz]

Hi I am successfully sending out via email reports with one prompt sent to the report using a DAT file but I am having an issue with two.
This is how I send over one
select case Plant
case "101"
strTo="email@email.com"
the "101" coming from a DAT file. but how do I change this to pass in two variables say Plant and OrderType? and email it out.
Thanks in Advance
Lisa

 

[drlex]

Would probably depend on the range of possible values.
To keep your code changes to a minimum, you might consider concatenating the prompts

prompt = Plant + OrderType
select case Prompt
        case  "101Stock"
        strTo="email@email.com"
etc

soi la, soi carré

 

[LRubanowicz]

Of course
Thanks a million !!
All the best
Lisa

 

[LRubanowicz]

Hi
OK I can do this for one prompt for each but now I need to send multiple Plants and multiple product categories.
I can send over a prompt such as '401,402,405@12,13,14' from the macro (as above).
So before the @ symbol are one or many Plants and after the @ symbol are one or many Product Categories (numbers but I can work with that).
If I have up to 28 possible plants and up to 43 Product Categories how can I set this up in the filter. I know the position of the @ symbol position('@', ?Filter?) but how can I create the Plant_code in ('401','402','405') and Sub_cat_code in (12,13,14)out of '401,402,405@12,13,14' if I don't know how many plants or sub_cat's I am likely to have on each run.
I do not want to create a column in the report for the possible 28 Plants and the 43 possible Sub_cat's. I would like to do it on the fly.
Thanks in advance
Lisa

 

[drlex]

This came up recently in thread401-1148617 - there seems to be some issue as to whether a comma can be passed in a single prompt.
When I need to pass multiple items, I have them in a prompt with space delimiters and use a filter with ?prompt? contains <field>. This is due to commas not working for me on 7.3
Try with splitting your prompt at the '@' and ?plantprompt? contains Plant_code and ?subcatprompt? contains sub_cat_code.
Note that this can bring back incomplete matches - ie a prompt with '1112' would return '111' and '112' in the data set. I only use it with fixed-length fields.


soi la, soi carré

 

[LRubanowicz]

OK so say I split the prompt at the @ symbol how can I send over several Plants - I can obviously make the Plant code a string or a number. My plant numbers are all three digits so I shouldn't encounter the issue you mention above.
I want to send over an initial text of 201,202,203 or maybe less or more numbers.
I can't seem to get them over to the IN prompt. without coming out at (''201','202'') which doesnt work of course.
I don't want to have to list everything in the full array of 43 plants and then a column for each to say which position the substring is in.
Any ideas.
Cheers
Lisa

 

[drlex]

My method envisages that you pass two prompts, each prompt containing the required selection of items with a space as a separator and the use of the CONTAINS function
i.e.
Prompt_Plant = "401 402 414"
Prompt_Sub_Cat = "11 12 19"

Pass as Prompt_Plant + "|" + Prompt_Sub_Cat

Filter is ?Prompt_Plant? CONTAINS [Plant] and ?Prompt_Sub_cat? CONTAINS [SubCat]

where [Plant] and [SubCat] are the report items to filter on.

You may need to create a loop in order to compile the prompts from the DAT file.


soi la, soi carré

 

[LRubanowicz]

Hi
I can't get even the simplest of CONTAINS statement to work. I successfully got [Plant Code] contains '401' but when I type in [Plant Code] contains '401 402' nothing comes back and I know both exist.
I am trying to do the basic here with two plants being typed into the CONTAINS within Impromptu - not passing them over yet - but to no avail.
Have you any ideas.
Cheers

 

[drlex]

that's because you have your terms reversed; you need to put '401 402' CONTAINS [Plant Code].
[happy]

soi la, soi carré

 

[LRubanowicz]

You can't do that - the only things available are USER ID, Access Security, STRING, Prompts Manager - you can't select a Column or Database column
Can you please try this with something of your own data. I kow it is a lot to ask but....
Lisa

 

[drlex]

I can do that and indeed have to on occasions.
example report.
Filter:
(?prompt? contains Loc)

SQL (SQL server):
select T1."nl-loc" "c1" , T1."Name" "c2" , T1."Loc" "c3" , T1."Summ-code" "c4"
from "dbo"."location" T1
where '217 215' like {fn CONCAT({fn CONCAT('%',T1."Loc")},'%')}

Perhaps your statement of "you can't do that" relates to your use of a different DB, version of Impromptu or just plain ole misunderstanding.

soi la, soi carré

 

[LRubanowicz]

I am using Cognos Impromptu 7 against Oracle. The issue with your example above is that the Catalog column Loc is not available in the list on the left hand side of the screen.
I got the SQL statement working alright though.
select T1.Plant_code , T1.PLANT_Name
from regions T1
where '217 215 402 403' like CONCAT(CONCAT('%',T1."PLANT_CODE"),'%')
returns the three plants that exist.
But Cognos won't allow me to do the same in the Filter screen.
Sorry to be a pain

 

[drlex]

You won't have Catalog column Loc in your list, because it is a field name in my catalog used to give you an example. Neither would you have nl-loc nor Summ-code.

By your admission of working SQL, it leads me to think that you're only just a whisker away from solving this.

Try the following:
1.Create a new report with two fields (columns) - Plant_code and Plant_Name.
2.Go to Filter
3.Click on Prompt Manager and define a prompt called Plant that is a string
4.After completing Prompt Manager, you should have ?Plant? showing in the filter. Click on 'contains' on the list on the left hand side.
5 Add in Plant_code from the report to complete the filter.

You should now have a report that prompts you for a string and returns plant codes contained in that string. Robert should be your mother's brother...




soi la, soi carré

 

[LRubanowicz]

Hi
I followed your instructions to the letter - I promise - and still when I go to add the Plant Column from my report (number 5 above) it is not available. The only options here are Access Security, string, user ID, Prompt Manager

Maybe you see my issue. could it be something to do with the Cognos settings not allowing the further options?
Lisa

 

[LRubanowicz]

Hi drlex
OK I created a new report using just SQL and then inserted a prompt
select T1.Plant_code , T1.PLANT_Name
from regions T1
where ?Plants? like CONCAT(CONCAT('%',T1."PLANT_CODE"),'%')
and it works fine.
mmm.....

 

[drlex]

If you can't see also see 'functions', 'Catalog columns', 'if', 'lookup' and '(', then I'm at a loss to proceed - it could well be catalog lock down - not something I've experienced. If so, you need to either create your own catalog or seek improved catalog access from whomever is in charge.

(I thought it might be due to the DB storing 'Plant' column as a number and therefore not being an option to compare with a string prompt, but if you can't access functions, then number-to-string won't help get around this).

cya


soi la, soi carré

 

[LRubanowicz]

Thanks a million - I appreciate the time you have put in to this. But as you can see I am stuck. I am the Administrator on the Catalog butI will have a look around to see what comes up at that end
I knew I wssn't going mad!!
Thanks again