Save other field than Prompted as presentation variable

Matthias_VC

Okay this question will actually be multiple questions in one.
I'm trying to generate a visual in Primavera Analytics (which uses the OBIEE software as a basis).

It's a cloud implementation and I have no way of changing the Datamodel so I'd like to solve it with the Reporting Programs abilities only.

Now for one graph I want the user to input which project he would like to see a graph like below Picture:

The easiest way would be to prompt the user for the Proj_ID (eg. 545). However, the user has absolutely no Idea which values correspond to which project. So giving them a dropdown with [91,246,545] will be absolutely pointless.

What would be better is to give them a list of Proj_Names [Project X-VS0, Project X-VS1, Project X-VS2, Project Zepplin-November,...] and then use the corresponding Proj_ID as a filter However:

- Is it possible to store not the prompted field but a different one (Prompting Proj_Name, but storing Proj_ID)?

- I guess It IS possible to do a two-way prompt (asking user to select Project Name, then in a next page ask for a Proj_ID where it is already filtered and so only 1 value is in the dropdown storing that value once the user selects it).But this is a process I would rather not follow.

- Is it possible to do a "lookup calculation" in the filter of an analysis (e.g. "WHERE Proj_ID=(Select Proj_ID FROM "Table" WHERE Proj_Name = @{varProjectSelected} LIMIT 1)")

Thanks in advance!

Matthias

Jerry Casey

You can use a calculated value in both the query prompt and the filter.  Create a column like:  "Project Info"."Proj_ID"||' - '||Project Info"."Proj_Name".  It can be used in both the prompt and the query filter.

In the prompt dialog, click on this icon to paste-in your calculated column:

Matthias_VC

Okay, that was already really helpfull.

I've created a column prompt instead of a variable prompt and used the Method you said to create a combined field.

CONCAT(CONCAT(TRIM(BOTH FROM CAST("General - (Project)"."Project Object Id" AS CHAR(10))), '|'), "General - (Project)"."Project Name")

However If I now create the same column in my Analysis, it will ONLY filter out the projectname I selected and not everything with ProjectID 1007.

So I tried saving the column-prompt selection as a variable and use that to look for matches before the pipe. However I don't think I can do string manipulations in the filter so I tried to do all the manipulations in a new column Prompt:

I filtered then all data where "Prompted Base" = 'Y'

As long as I don't prompt anything it works (so the default is okay). As soon as I prompt a value it gives me an error:

"Error generating view. Error getting cursor in GenerateHead"

If I look at the SQL issued it says:

SQL Issued: SELECT CASE WHEN TRIM(BOTH FROM CAST("General - (Project)"."Project Object Id" AS CHAR(10))) = LEFT(845|BA..., LOCATE('|', 845|BA...)-1) THEN 'Y' ELSE 'N' END FROM "Primavera - Activity History"

I guess the problem is due to the missing apostrophe's around our variable result? How do I solve this issue?

Even without prompting, the method is rather slow. I guess this is due to the fact that it requires a lot of calculation. Any way to simplify?

M De Fanti

Hi Matthias,

one solution would be that of using the "Description Id Column" in the "logica column" but, as you said, you have not the possibility to change the datamodel.

Another solution, could be that the analsys should have a filter like "Proj_id" and operator="is base no results of another analysis"

And the second analysis should have in the requested columns the "id" and be filtered by description.

Regards

Massimo

Jerry Casey

Hi Matthias,

I was working under the assumption that project ID and project name had a one-to-one relationship.
Instead of the complicated case statement, you can use a filter that just looks at the left 4 characters of your concatenated prompt-variable:

LEFT('@{varProjID_Name}{845_SomeProject}',4)="General - (Project)"."Project Object Id"

Jerry

Matthias_VC

Hi Jerry,

That looks like what I wanted to do. However, where can I define that Filter? I tried doing it in the filter of the analysis or in a separate filter, but neither have the proper effect. Or at least I wasn't able to manage.

Matthias_VC

Thanks for this!

It really did what I needed. at first I didn't realise I had to use an external filter to filter this visual, but once I did that it went smoothly!

So solution =

- Prompt that asks for Project_Name and stores it in a variable : varProject

- Analysis1 that has two fields: Project_Name and ProjectID, filter this analysis so that Project_Name = @{varProject}

- Analysis2 that shows the line graph and has a filter: ProjectID is based on results of analysis1 (ProjectID)

Using this solution I don't need to trim, or look for a pipe for every line in my table or use text functions. I don't even need to display Analysis1 in my dashboard for it to work. I'm not sure if it's the fastest solution, but it works fine enough for now.

Matthias_VC

I checked now to the advance tap to check what SQL is being generated. there it says:

WHERE
(("General - (Activity)"."Activity Status" IN ('In Progress', 'Not Started')) AND ("General - (Project)"."Project Object Id" IN
(
SELECT saw_1 FROM (SELECT
   "General - (Project)"."Project Name" saw_0,
   "General - (Project)"."Project Object Id" saw_1
FROM "Primavera - Activity History"
WHERE
"General - (Project)"."Project Name" IN ('BAM OWV-Integraal Rev 35 - juni 2019')

)

So that is exactly what I wanted in the first place. Thanks again!

Jerry Casey

You can open the new filter dialog on any column, then check the Convert to SQL box:

You will see this:

You can replace the contents with any SQL filter.

Matthias_VC

Okay,

Thank you very much! I'm a bit unsure whos answer I should mark as "the correct one" because now I have two viable ways of making it work!

I was able to do edit my previous filter this way. When in SQL edit mode, I saw that the apostrophes were not in the formula where I placed them in the calculated column. So I could move them and also restructure it so it didn't need the Case statements.

Very helpfull!

Jerry Casey

You could use Occam's Razor to decide.