nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report Parameters

josh.flaumenhaft

I am having some problems with a report parameters. I am trying to filter results based on user input. The problem I am running into is that the user input does not match a column in the SQL select statement. As an example, the column in the select statement is pulling a full heirarchy (propertybuildingfloorspace), but I am only getting input for the property. I cannot add that to the where statement because there is no property column selected. When I try to bind the dataset parameter to the report parameter, I get "Cannot set the string value (parameter) to parameter1. SQL error #1:The index 1 is out of range." I'm not sure if I am explaining this very well but any help would be most appriciated.

Find more posts tagged with

Comments

GLO_FR

Hi can you upload a design with a flatfile as datasource or an example based on the Classic Model DB

josh.flaumenhaft

Hello GLO_FR, using the classic model db and the customers table, my problem would be if the address1, city, state and zip were combined into a single database column (let's call it mailing address). if you did a select mailing address from customer you would get the full mailing address in a single field. what I am trying to do is configure a report parameter that asks for the zip code, but since there is no seperate column for zip in the select statement, I cannot add a where zip = ? to my SQL query. does that make any more sense?

GLO_FR

Ok, I think I get it.Can you identify and separate the value you want to filter on ? For instance in your first example, can you identify and get the "property" value from the all hierarchie field and put it in a separate column ?Usign SQL, you've got order like left, right, locate... Using BIRT, you've BIRT function or JavaScript... If possible, put this ID or what ever it is in a separate column and use it as filter (for SQL use a parameter in a WHERE clause, for BIRT use a filter and bind it to your report parameter). BTW, using a like in your SQL query is also possible.

josh.flaumenhaft

is there a why to pass the user input parameter into the sql query for my like clause? Example, I have a report parameter called LdCntr. is it possible to pass that parameter into my sql query so I can add a where hierarchy like LdCntr?

GLO_FR

Of course you can.

In your query type
SELECT * FROM MY_TABLE
WHERE MY FIELD LIKE ?

Then instead of linking the query parameter directly to the report parameter, you have to build and expression like this one
'%' + params["Like parameter"].value + '%'

I'll post you an example tomorrow.

GLO_FR

Hi again, and here is a sample report base on the Classic Model db