I need to add a date dimension prompt to a universe

[NPilypaitis]

I thought that it would be written like this;

@Prompt('DatePrompt','D',,Mono,Free,Not_Persistent,GETDATE(),User:0,,)

But I keep getting this error;

The query does not reference any table when attempting to build the WHERE clause. (IES 00022)

See attached screenshot.

I want to work for any query so I don't want to connect it to any table.

[nscheaffer]

Can you provide more details on how you envision using this? Do you just want to prompt for a date value and not actually use it to filter on anything?

[NPilypaitis]

Correct, I just want to prompt for a date value and not used to filter anything. Actually, the date doesn't need to be the current date, something like '1900-01-01' could work too.

[NPilypaitis · Accepted solution]

So far, the only way I can get this to run without any errors is to set it as a string instead and write it as @Prompt('DatePrompt','A',,Mono,Free,Not_Persistent,{'1900-01-01'}) then convert the string back to a date after the query runs. If anyone can get it to cast as a date from the SELECT clause that would be nice. But I suppose if it works then it's not ugly ^^;

EDIT: I can get it to be a date if I add a CAST. So final answer
CAST(@Prompt('DatePrompt','A',,Mono,Free,Not_Persistent,{'1900-01-01'}) AS Date)

[nscheaffer]

I cannot figure out how to get the current date as the default value, but it seems you don't need that.

This is working for me...

@Prompt('DatePrompt','D',,Mono,Free,Not_Persistent,{'1900-01-01'})

If you do not associate a table, you will get the same error you are getting now...

If you ignore it, it should work. It works for me in a test query within my universe...

You will get that same error or something similar if you run the Check Integrity command. But since it works in my query within the universe I am pretty confident it will work once you publish it.

You could get rid of the error by associating the dimension with any table in your universe. Be careful with this approach as you will be returning ALL of the rows from the table you chose unless you have some other filter or join on it.

Personally, I would just live with the error.

Noel

[drs_macgyver]

The error is actually a warning and it is correct, this object does not reference any tables.  However, it does not need to.  As written, it will not validate but it can still be used in a query as long as the query references a table.

You can validate the SQL by selecting any table but you won't want to leave a table selected or you'll have to create joins or it will cartesian.  So pick any table, validate it, then uncheck all before promoting the universe.

I also would recommend creating a naming standard for these type of objects so that when you run Integrity Check and see the warnings, you know you can ignore them.

[drs_macgyver]

Sorry for duplicate answer, I didn't see that my answer yesterday posted.

[NPilypaitis]

Yea, I do get the prompt when I add it to a Webi and try to run it. However, I get the following error no matter what date I enter in the prompt;

The following database error occurred: [Cache ODBC][State : 22007][Native Code 22007] [E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\WIReportServer.exe] ERROR #388: Unknown error, code 22007. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)

[drs_macgyver]

In the Information Design Tool DFX, go to the Parameters and List of Values tab. Add a parameter, give it an object name, type in the prompt text, and set the type to a date. If you happen to have a date table, use that for the list of values. You can either code the default as a constant or a formula such as your getdate or currentdate to get system date. You then just need to expose the parameter in the BLX portion of the universe.

[NPilypaitis]

I think I added a prompt to the dfx correctly;

My apologies, but I'm not sure how to expose it to the blx like you said. Can you provide instructions?

[drs_macgyver]

I misled you.  I am using the parameter as a forced prompt filter on various universes classes so I added a new filter object and set the formula based on the new parameter.

However, the parameter is available as is, in the DFX.  When you create your report query prompt,  click on the gear for Prompt Properties and select parameter from universe.  This will replace the prompt portion of the condition with the parameter's prompt.  If this works for you, I would appreciate your thumbs up and marking this as an answer.

[NPilypaitis]

I would like nothing more than to be able to mark something as the solution and give the kudos to whoever can explain how I can do this. Unfortunately, nothing suggested yet has worked.

[drs_macgyver]

I over complicated it when mentioning the BLX.  Once your parameter is added to your DFX and you export the universe.  You can access it within a report query.

1. Add a condition setting whatever date that you need to restrict = a prompt.
2. Click the gear on Prompt Properties and select parameter from universe
3. Choose your parameter

[NPilypaitis]

I do not want to add the prompt as filter in the WHERE clause. I need it to be in the SELECT.

[drs_macgyver]

I made a bad assumption.  If you just want to prompt for a date and pass it to the report, go back to where I posted to add an object to your business layer.  You can add a Dimension object, click on the SQL Assistant for the SELECT, and choose your Parameter object that you defined.

[NPilypaitis]

I did that several times and no matter what I tried if the new dimension I added had 'D' in the @prompt function if I added it to a query and tried to run it I would an error. Explained and shared several times in this thread but once more now;
The following database error occurred: [Cache ODBC][State : 22007][Native Code 22007] [E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\WIReportServer.exe] ERROR #388: Unknown error, code 22007. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)
I finally got it to work by tying it as CAST(@Prompt('DatePrompt','A',,Mono,Free,Not_Persistent,{'1900-01-01'}) AS Date)

[drs_macgyver]

There isn't a prompt in the dimension. When you use SQL Assistant and select your DatePrompt parameter, it will just add Prompt(DatePrompt). You do not need to code the prompt syntax.

[nachtaktiv]

getdate() is a DB-function, so when not connected to a DB it cannot be resolved.
connect this to any available table, no matter what table it is 

[drs_macgyver]

The DB connection is provided by the other objects in the query. Best to not associate the prompt object with a table or that table will require a join in all queries that it is used.

[drs_macgyver]

This is not really an error, just a warning.  The SQL does not reference a table.  You can test validate the SQL by selecting any table, but uncheck the table before promoting the universe.  Otherwise, it will expect a join to exist.

When you add the prompt to a query that references a table, it won't matter that the prompt doesn't.  I suggest using a naming convention for these type of objects so you aren't chasing these warning messages.

[drs_macgyver]

I also assumed based on the syntax that you are using UDT and not IDT as your designer tool. If still getting an error, try eliminating the default value in the prompt and see if that is the cause for the error.

[NPilypaitis]

I think I am using IDT, that is the program name at least.

And even when I remove the default value. However, I get an error when I try to run anything with it in Webi.