Excel with SQL Expressions (Possible BIDS Alternative)
New here so Hi everyone!
Not sure if this is the correct area to post my query as it contains questions in regards to sql as well as office excel.
So here goes... We are a training company which is funded by the government that money is awarded to us for a certain duration of the course in which the trainees must complete the course otherwise we start training them for nothing.
I have been tasked to amend an Excel document that contains a list of employees names which we then input a number into different categories which include; number of students training for that particular employee we have, the number of them that have suspended training (this allows us to stop any funding but prolongs the life in which the student can complete the course and continue the funding at a later date), and the number of students that have gone over their funding date.
All the above information is stored in an SQL database on our server, the information is obtained through a report provided by our database software.
My question and problem is... I know an SQL database can be linked with excel 2013 but can expressions be used to work out certain data in certain cells of an excel document (i know we might be talking of a lot of expressions changed to suit the certain employee).. the data we normally gather from the report we have in our database software is all based on counting the number of rows present under that employee to work out how many students they have at the moment so need some sort of count expressions of somesorts to work out that information, the students that are either suspended or have gone over their period of training are worked out by counting letters on the report from the database software that is printed (The letter "Y" in this case will appear in the field for the employee), the letter will be totalled up to give us the amount of students suspended or reached their funding date etc. So yer at the moment its all done manually by counting figures on the form so we need expressions to do that counting for us. Im not entirely sure what i would use for expressions (perhaps an expression using count function like in report builder) but with that it would be ideal to be able to do it in Excel cells. I would greatly appreciate some advice on the potential expressions i could use to count in a BIDS report if using Excel isnt possible using their own data cells etc. going by that method i have to run a report query used in the database software to create the report from which the row totals, number of "Y"'s that need counting etc.
A long and boring post i know, just trying to give a good idea of the database and reports im working with. Thanks
When you add the link to the SQL database you can input an SQL statement, however I don't "think" it can be multiple ones at the same time. So if you had a connection to the database using say "SELECT * FROM 'STUDENTS' WHERE 'THINGY' = Y" etc, it would only pull the data that the statement relates to, but I'm not sure how that would work with multiple queries, unless you had seperate connections for each one.
(That is if I understood what you want correctly?)
Ha yer kinda tricky to explain the report we use to manually note down the rows and manually count the number of Y's present in a field related to that one employee is all part of one single report that uses SQL (the SQL is generated by the software via changes made in an easy to use user interface..ya know drag and drop change parameters..a bit like the SQL Builder in BIDS where the SQL is built for u).
The Report itself is a report specific to the program we are using ..it is NOT an .RDL therefore i "think" and im not expert quite a newbie tbh but i like to think i learn fast as i only just built my first report in BIDS a few days ago.. I might have to build the report in BIDS so its seen as an .RDL file that i could perhaps link to the excel form (if possible) then use expressions in excel to read from the report ive linked to count the number of rows and count the number of letters etc.
Seems to make sense to me.. but its all a matter of whether that's possible.. to actually link a specific report/rdl to an excel document and then use expressions in excel to count rows and letters generated by the report and place the data gathered by the expressions in individual cells. (Like i said id be using the same expressions for the exact same fields just changing the employee who im looking at in that particular cell.
Im a newbie though so what do i know hey haha
Last edited by Jbone; 19th February 2014 at 10:05 AM.