Original Message:
Sent: Thu February 22, 2024 03:33 AM
From: Paul Mendelson
Subject: Data insertion in table
Yeah, that should definitely work. As long as the report is getting a value for the parameter that matches the parameter in the stored procedure it will be inserted.
------------------------------
Paul Mendelson
Original Message:
Sent: Tue February 20, 2024 01:43 PM
From: M Barrett
Subject: Data insertion in table
I don't mind the refresh, would love to just finish it the old way :-). Can I add two more value fields in the SP and table with prompts and then in the HTML of the fields change it to point to value1 or value 2?
'<input
type="text"
name="p_'+number2string([DataEntry].[Day Key])+'Value"
value="'
+case when [Value] is null then '' else number2string( [DataEntry].[Value2]) end
+'"
onchange="updateDateArr('+number2string([DataEntry].[Day Key])+')"
/>'
------------------------------
M Barrett
Original Message:
Sent: Tue February 20, 2024 12:23 PM
From: Paul Mendelson
Subject: Data insertion in table
This would be a combination. Same idea using a stored procedure with parameters, but calling the report with mashup services so it all happens in the background. This makes the experience much better for the end user, as the report doesn't need to be refreshed for the data to be updated.
------------------------------
Paul Mendelson
Original Message:
Sent: Tue February 20, 2024 11:16 AM
From: M Barrett
Subject: Data insertion in table
Thank you Paul for the prompt response!
Is this in addition to what you have in this example or is it a new set up?
https://cognospaul.com/2014/01/02/creating-data-entry-page-cognos/
------------------------------
M Barrett
Original Message:
Sent: Tue February 20, 2024 05:11 AM
From: Paul Mendelson
Subject: Data insertion in table
There are a few ways you can do this. The way I'm picturing it is you have a series of three inputs in a row. You could then write an onblur function to attach to all of the inputs that would do something like this
fnUpdate = async function(){
var elm = this
, row = elm.closest('tr')
, inps = [...elm.querySelectorAll('input')]
, gateway = Application.GlassContext.gateway
, targetReport = `/content/folder[@name='CMS']/report[@name='c']`
, url = gateway + '/v1/disp/rds/reportData/searchPath' + targetReport
;
if(inps.filter(inp=>inp.value).length<inps.length) return false; //count the inputs with values. If not ALL of the inputs are filled stop
var params = inps.map(inp=>{let param = 'p_' +inp.name+'='+inp.value }).join('&')
var data = await fetch(url + '?asyc=off&fmt=datasetJSON&'+params
, {
"headers": {
"accept": "application/json"
, "content-type": "application/json"
}
, "method": "get"
, "mode": "cors"
, "credentials": "include"
}).then(x=>x.json())
return data
}
The assumption here is that each input has the parameter as the input name. The HTML would be like: <input name="pYears"/>
What is happening is that when the user enters a value into any input on the list, the JS goes up to the row and looks for all of the inputs. It then compares the count of all inputs with values against the count of inputs. If they match, it will run the update report, passing the parameters in the URL. The URL will look something like this:
"/bi/v1/disp/rds/reportData/searchPath/content/folder[@name='CMS']/report[@name='Run Update Stored Proc']?asyc=off&fmt=datasetJSON&p_pYear=2022&p_pProductline=660&p_pCountry=America"
This is the basic script to handle it. You can then add things like indicators if they haven't populated all of the inputs (maybe make each unfilled input background or border red), or spinning icons while the row is being updated. You could get even more fancy and only generate the input when the user clicks on the cell - but then you'd have to refactor the input count check.
------------------------------
Paul Mendelson
Original Message:
Sent: Fri February 16, 2024 10:20 AM
From: M Barrett
Subject: Data insertion in table
Hi Paul, love your work BTW. For your example is there any way to do more than one 'value' field? for instance, a value1, value2, value3 etc.. ? so the user updates the first, second and third value item fields then those three get submitted to the database table?
------------------------------
M Barrett
Original Message:
Sent: Sun June 25, 2023 03:49 AM
From: Paul Mendelson
Subject: Data insertion in table
Since I've written that article my JS skills have improved dramatically. One of these days when I have free time (HAH) I need to write an article describing a method to do data entry using AJAX so you don't have to refresh the page.
Basically you can have a separate report that runs the stored procedure. You can then use CMS to call it, passing the parameters. But the CMS call can be handled through an event handler on the report the user is running. An example would be to have an onblur listener on all inputs in your list. When the user removes focus from the input the stored proc report is called in the background.
Depending on interactive mode you have different techniques to get the context data for the cell the user is updating. In interactive mode you can run Application.getParamterValues() to get a list of all active parameters for the report run, so you can get the prompted time/product/region values for example.
------------------------------
Paul Mendelson
Original Message:
Sent: Fri June 23, 2023 10:15 AM
From: brenda grossnickle
Subject: Data insertion in table
I am doing a cognos report that updates a ms sql database table. My report uses many of the techniques that Robert Dostal has posted, except mine is a simple toggle update. There is no data entry. If a relationship code type (single, joint owner) is = 0 in the database table, when clicked it is updated to 1, and visa versa. The report is a drill through that redesplays the same report - report1 is a drill through to report1.
First row is just for my testing. it will be removed. Three prompt text boxes that have -99 will be hidden. Userid is a badly named column that should be called something like primary key. Userid column will be hidden. First prompt box is for the userid and -99 says to show all rows, second prompt box is for Single Owner, third prompt box is for Joint Owner. The relationship table has userid, Relationship Code, Single Owner, and Joint Owner column. Relationship code BEN can be assigned to Single Owner and/or Joint Owner. So my update is just a toggle - on or off. It does not allow for entering text. If in the relationsihp table BEN, Single Owner = 1 then "Assigned" is displayed, else "[+]" is displayed.
An example would be when BEN Single Owner is clicked, it is a drill through, and a stored procedure that is setup in framework manager is called and two parameters are passed - userid = 2 and single owner. Joint Owner parameter is not passed. In the stored procedure userid=2, singleowner is not null and joinowner is null. So the stored procedure knows that BEN (userid=2) Single Owner (parameter for single owner is not null) was clicked. So the stored procedure would set BEN SingleOwner to 1 if it is currently 0 in the table, else it would set it to 0 (toggle). And then the drill through redisplays (select * from relationship) the same report again and BEN Single Owner now displays "Assigned".
Here are some links that i used. Some are only a toggle (like mine), some allow data entry (i did not like because you could only do one row at a time and you have to redisplay the report to see the update or to continue updating), some use HTML and javascript.
https://www.youtube.com/watch?v=00xaDWs5igo -- i used this one the most for my report. he only had a single update column. i modified to update two columns.
https://www.youtube.com/watch?v=fnIwARI5BnU - this one is more of primer of how to use stored procedures in framework manager
https://www.youtube.com/watch?v=9RxDg7LIWQI - data entry (not just a toggle) to update database table
https://cognospaul.com/tag/framework-2/ - pretty advance data entry using html and java script
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
Original Message:
Sent: Wed June 21, 2023 05:33 AM
From: Nidhi Pawaiya
Subject: Data insertion in table
Hello All,
I am generating a list report from cog nos report studio.
I need to insert data from list report to internal database table.
Please suggest how to insert report data into tables.
------------------------------
Nidhi Pawaiya
------------------------------