[sims] SIMS and PowerBi question

[ifillingham]

Hi there.

 

I hope someone can help answer a question I have or suggest some solutions.

 

I am developing a PowerBi dashboard to display whole school data that we have in SIMS. I can produce simple visualisations showing number of students in each year, graphs showing trends in attendance, punctuality conduct etc. I am using a lot of Power Query to help clean up the csv files from SIMS before I upload them to PowerBi and it seems to be working pretty well.

 

I am coming up against a more difficult problem when it comes to assessment data as my current problem is to write a SIMS report that returns the correct assessment data with the student AND their class.

 

I can extract

Name	Subject	Group
Student 1	Maths	Maths group
Student 1	English	English group
Student 1	Science	Science group
Student 2	Maths	Maths group
Student 2	English	English group

 

AND

Name	Subject	Target grade
Student 1	Maths	Maths target grade
Student 1	English	English target grade
Student 1	Science	Science target grade
Student 2	Maths	Maths target grade
Student 2	English	English target grade

 

and then merge these two csv files to produce

 

Name	Subject	Group	Target grade
Student 1	Maths	Maths group	Maths target grade
Student 1	English	English group	English target grade
Student 1	Science	Science group	Science target grade
Student 2	Maths	Maths group	Maths target grade
Student 2	English	English group	English target grade

 

Do you know if it is possible for SIMS to produce this (merged) table directly??

 

Thanks a lot in advance!!

Ian

[sefton]

It is possible, you need to filter on the subject, class name and then aspect and result set (if you use them)

[RootsSchImp]

It is possible, you need to filter on the subject, class name and then aspect and result set (if you use them)

 

Unless I'm missing something this way would require an execution of the report per subject which may not be practical.

 

The way I would achieve it would be to merge the class report and the targets report in Power Query.

 

Select your Class query, click Merge Query then in the dialogue select the target query as the one to merge, and click on student id in both as the first merge field. Then ctrl click the subject fields to match on subject as well. Then click OK.

 

The merged query is shown as a single column - click on the out facing arrows at the top to expand it to the individual columns you want.

 

Good luck

 

Ed

[sefton]

Unless I'm missing something this way would require an execution of the report per subject which may not be practical.

 

The way I would achieve it would be to merge the class report and the targets report in Power Query.

 

Select your Class query, click Merge Query then in the dialogue select the target query as the one to merge, and click on student id in both as the first merge field. Then ctrl click the subject fields to match on subject as well. Then click OK.

 

The merged query is shown as a single column - click on the out facing arrows at the top to expand it to the individual columns you want.

 

Good luck

 

Ed

 

You can do it all on the one report, you can have multiple instances of the filters. Although your solution is a bit more elegant.

[ifillingham]

Thanks guys,

 

I was thinking that I would have to merge the tables in Power Query as I have ended up doing this quite a few times. Running class and target reports then merging them is more future proof as well I think - as long as I know what fields need to be matched.

I seem to be on an iterative process of getting data from SIMS into PowerBi then finding a more efficient way of doing it e,g, using a different report in SIMS. It's fun but frustrating at the same time.

 

Thanks once again for the help,

Ian