Can I read parameter values from database by passing values from report.
9 April, 2013
Hallo
I have got a system parameter table in my database called sysparam.
I created some reports using a VIEW but the different reports needs different records from the sysparam table.
This table does not join to any table in my VIEW.
Example I read the depreciation table to get the current book value and then want to read a parameter called "Residual Markup". I then need the value this returns to multiply the bookvalue with.
Ex bookvalue = 100
Parametervalue = 1.5
Calculated value = 150
The next report might also use depreciation table but needs the parameter "DeprMethod". This can be "straight line" and based on the value needs to do some work.
Hoe can I get the parameter values in my reports based on values passed from the report ?
Thank you
A Venter
I have got a system parameter table in my database called sysparam.
I created some reports using a VIEW but the different reports needs different records from the sysparam table.
This table does not join to any table in my VIEW.
Example I read the depreciation table to get the current book value and then want to read a parameter called "Residual Markup". I then need the value this returns to multiply the bookvalue with.
Ex bookvalue = 100
Parametervalue = 1.5
Calculated value = 150
The next report might also use depreciation table but needs the parameter "DeprMethod". This can be "straight line" and based on the value needs to do some work.
Hoe can I get the parameter values in my reports based on values passed from the report ?
Thank you
A Venter
Hi Abri,
I hope we are understanding you correctly, so please forgive us if we are missing what you are trying to do.
The following questions should help clarify this.
You have a sysparam table, the contains values.
These values you want to use as filters, and also in a calculation.
E.g you will select 'Residual Markup' from the filter, and then run the report.
It will then do 'Residual Markup' x 'Ex bookvalue'
If so I think you can do this using a custom query filter.
This filter allows you to run freehand SQL directly against your database, so it by passes the view.
E.g.
You could then create a calculated field to handle all calculations.
E.g.
CASE When Parameter = Residual Markup
THEN 2.5 x Ex Bookvalue,
WHEN Parameter = CostPrice
THEN 1 x Ex Bookvalue
Else Bookvalue
END
So the calculated field will handle all possible calculations, but the filter will only show the values relevant to the parameter.
Please let me know if this isn't what you were after.
Regards,
David
I hope we are understanding you correctly, so please forgive us if we are missing what you are trying to do.
The following questions should help clarify this.
You have a sysparam table, the contains values.
These values you want to use as filters, and also in a calculation.
E.g you will select 'Residual Markup' from the filter, and then run the report.
It will then do 'Residual Markup' x 'Ex bookvalue'
If so I think you can do this using a custom query filter.
This filter allows you to run freehand SQL directly against your database, so it by passes the view.
E.g.
You could then create a calculated field to handle all calculations.
E.g.
CASE When Parameter = Residual Markup
THEN 2.5 x Ex Bookvalue,
WHEN Parameter = CostPrice
THEN 1 x Ex Bookvalue
Else Bookvalue
END
So the calculated field will handle all possible calculations, but the filter will only show the values relevant to the parameter.
Please let me know if this isn't what you were after.
Regards,
David
Hallo
Not exactly what I need. I do not want to filter. As the report is a drill through "child" it already filters from parent and reads data. So I have a resultset.
Record1 Value
Record2 Value
I want to read a single record from sysparam
Key field 'Residual Markup' Value field '1.5'
then I want to add a column to the results
Record1 Value Value*1.5
Ex
DepreciationMarch 10.00 15.00
Different reports will want to read sysparam with different Key field values, so
in the report I will just "hard code" this value. Some reports might need more than one record from sysparam.
Thank you
A Venter
Not exactly what I need. I do not want to filter. As the report is a drill through "child" it already filters from parent and reads data. So I have a resultset.
Record1 Value
Record2 Value
I want to read a single record from sysparam
Key field 'Residual Markup' Value field '1.5'
then I want to add a column to the results
Record1 Value Value*1.5
Ex
DepreciationMarch 10.00 15.00
Different reports will want to read sysparam with different Key field values, so
in the report I will just "hard code" this value. Some reports might need more than one record from sysparam.
Thank you
A Venter
Hi Abri,
Ok the parent report will bring the filter which is good.
But I still think you could use a calculated field to do what you want using a case statement;
Case
When Keyfield = Residual Markup
then Value x 1.5
When Keyfield = Wholesale value
then value
When Keyfield = Trade-in
then value x .5
You can create this calculated field at the view level, which means you can use it in many different reports.
I hope this is what you were after.
Regards,
David
Ok the parent report will bring the filter which is good.
But I still think you could use a calculated field to do what you want using a case statement;
Case
When Keyfield = Residual Markup
then Value x 1.5
When Keyfield = Wholesale value
then value
When Keyfield = Trade-in
then value x .5
You can create this calculated field at the view level, which means you can use it in many different reports.
I hope this is what you were after.
Regards,
David
Hallo
A calculated field does not let me select a record, it only adds a field to the select that exists.
Example: Select customer-name from customer.
If I add calc field " (customer-balance * 2)
Select customer-name , (customer-balance * 2) from customer.
What I need is more like this.
Select customer-name , customer-balance from customer. (this can return lots of records)
Select parameter-value from sysparam where param-code = "Residual Markup".
Now on report I want to display
Customer-name Customer-balance * parameter-value.
There will be many customer records but only one sysparam record.
Thank you
A Venter
A calculated field does not let me select a record, it only adds a field to the select that exists.
Example: Select customer-name from customer.
If I add calc field " (customer-balance * 2)
Select customer-name , (customer-balance * 2) from customer.
What I need is more like this.
Select customer-name , customer-balance from customer. (this can return lots of records)
Select parameter-value from sysparam where param-code = "Residual Markup".
Now on report I want to display
Customer-name Customer-balance * parameter-value.
There will be many customer records but only one sysparam record.
Thank you
A Venter
Hi Abri,
It looks like you're after some freehand SQL calculations so you can do this at the view level, as you can create sql freehand calculated fields. You should be able to pull in data directly from a table that is not used in the report.
Please note, if doing this, make sure you select the table as 'mandatory' as mentioned here : Yellowfin Optimized Views - explained.
Please let me know how you go.
Regards,
David
It looks like you're after some freehand SQL calculations so you can do this at the view level, as you can create sql freehand calculated fields. You should be able to pull in data directly from a table that is not used in the report.
Please note, if doing this, make sure you select the table as 'mandatory' as mentioned here : Yellowfin Optimized Views - explained.
Please let me know how you go.
Regards,
David
Hallo
I do not think we are on the same page here. Let me try to start over.
I will use a random example not the specific one I am dealing with as I want to
make it less complicated.
I create a view and put a table in the view. I add 3 fields from the table
InvoiceTable
InvDate
InvNumber
InvAmountExcl
I build a report that lists my invoices for January. Lets assume I have 2 invoices.
01/01/2013 Inv001 R1000
15/01/2013 Inv002 R100
Now I want to calculate the VAT on them (currently the % is 14).
My system has a value setup that I need to read in a table called parameters.
This table has 2 fields
Parameters
ParName
ParValue
So I need to read the table where the name of the parameter is VAT
Select ParValue from parameters where ParName = 'VAT'.
I then need to multiply every InvAmountExcl with this to make the report look like this
01/01/2013 Inv001 R1000 R1140
15/01/2013 Inv002 R100 R114
The 'VAT' I use as filter on the parameters table can change depending on report, this report needed to calculate the inclusive amount but another report might need to read parameter to give days before calculating interest.
Select ParValue from parameters where ParName = 'InterestDays'.
This will return for example 45. And would mean that if you have not paid your invoice within 45 days I will charge you interest.
The interest % can also be a parameter so a report might need to read more than one parameter.
Select ParValue from parameters where ParName = 'InterestPerc'.
There is no relation between the InvoiceTable table and Parameters table.
I dont know if this example explains it better ?
Thank you
A Venter
I do not think we are on the same page here. Let me try to start over.
I will use a random example not the specific one I am dealing with as I want to
make it less complicated.
I create a view and put a table in the view. I add 3 fields from the table
InvoiceTable
InvDate
InvNumber
InvAmountExcl
I build a report that lists my invoices for January. Lets assume I have 2 invoices.
01/01/2013 Inv001 R1000
15/01/2013 Inv002 R100
Now I want to calculate the VAT on them (currently the % is 14).
My system has a value setup that I need to read in a table called parameters.
This table has 2 fields
Parameters
ParName
ParValue
So I need to read the table where the name of the parameter is VAT
Select ParValue from parameters where ParName = 'VAT'.
I then need to multiply every InvAmountExcl with this to make the report look like this
01/01/2013 Inv001 R1000 R1140
15/01/2013 Inv002 R100 R114
The 'VAT' I use as filter on the parameters table can change depending on report, this report needed to calculate the inclusive amount but another report might need to read parameter to give days before calculating interest.
Select ParValue from parameters where ParName = 'InterestDays'.
This will return for example 45. And would mean that if you have not paid your invoice within 45 days I will charge you interest.
The interest % can also be a parameter so a report might need to read more than one parameter.
Select ParValue from parameters where ParName = 'InterestPerc'.
There is no relation between the InvoiceTable table and Parameters table.
I dont know if this example explains it better ?
Thank you
A Venter
Hi Abri,
Let me try to solve your problem.
Let's assume that we have two tables - one with items listed and their current values and another is a sysparam table with different types of parameters names and their values.
Create a view and cross join those tables:
Then create a report based on that view as follows:
If you want to multiply your current values by Residual Markup value, then add it as an extra filter to your report:
And perform multiplication of two columns by using analytic function:
Run your report, it will look like one below:
You can hide your parameter if you do not need it, then your report will look like this:
Hope it gives you an idea on how you can tackle your problem.
Please let me know if it is not what you was looking for or you need more help.
With best wishes,
Jenny Kirillova
Yellowfin BI Consultant.
Let me try to solve your problem.
Let's assume that we have two tables - one with items listed and their current values and another is a sysparam table with different types of parameters names and their values.
Create a view and cross join those tables:
Then create a report based on that view as follows:
If you want to multiply your current values by Residual Markup value, then add it as an extra filter to your report:
And perform multiplication of two columns by using analytic function:
Run your report, it will look like one below:
You can hide your parameter if you do not need it, then your report will look like this:
Hope it gives you an idea on how you can tackle your problem.
Please let me know if it is not what you was looking for or you need more help.
With best wishes,
Jenny Kirillova
Yellowfin BI Consultant.
Hallo
Now that is an awesome answer. It works well, up to where I have to do the calculation.
The parameter-value is always a varchar as parameters can be logical, string, decimal etc.
So I need to convert the parameter-value to decimal first, but in the view I cannot change it to metric. The field is then not avail in the multiply columns option.
Thank you
A Venter
Now that is an awesome answer. It works well, up to where I have to do the calculation.
The parameter-value is always a varchar as parameters can be logical, string, decimal etc.
So I need to convert the parameter-value to decimal first, but in the view I cannot change it to metric. The field is then not avail in the multiply columns option.
Thank you
A Venter
Hallo
I found that I can convert and do multiplication using F(x).
thank you very much for the help.
A Venter
I found that I can convert and do multiplication using F(x).
thank you very much for the help.
A Venter
Wow, thanks Jenny!
Glad finally someone is on the page, I guess I have a different book :(
Glad finally someone is on the page, I guess I have a different book :(